Convert EF6 to EF core with the ObjectResult

Question

I have some code I am trying to convert. I don't have these ObjectResult and ObjectContext anymore

This is what I did have:

public virtual ObjectResult<string> GetTransData(string iN_MEM_ID)
{
    var iN_MEM_IDParameter = iN_MEM_ID != null ?
        new ObjectParameter("IN_MEM_ID", iN_MEM_ID) :
        new ObjectParameter("IN_MEM_ID", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<string>("GetTransData", iN_MEM_IDParameter);
}

Since I need a List to be returned from a caller ( it is sent back as json data )

This is what I am trying to build

public virtual List<string> GetTransData(string iN_MEM_ID)
    {
        var iN_MEM_IDParameter = iN_MEM_ID != null ?
               new SqlParameter("IN_MEM_ID", iN_MEM_ID) :
               new SqlParameter("IN_MEM_ID", typeof(string));

       Clinical_CaseTrakker_Context clinical = new Clinical_CaseTrakker_Context();


        List<string> offLine = clinical.string.FromSql("EXECUTE CT.GetTransData {0}", iN_MEM_IDParameter);

        return offLine;
    }

Notice that I am stuck with clinical.string i can't do that , but I am not sure how to take dbcontext instance and run FromSql to execute sql and return to List

Answer 1

In EF Core, it is not possible to use the FromSql method to return a subset of properties (a projection) directly from the database. You are required to define a some model and a DbSet for that class

public class Foo
{
   public string Bar { get; set; }
}

then declare in your context

public DbSet<Foo> Foos { get; set; }

and use it like:

using (var context = new Clinical_CaseTrakker_Context())
{
   var offLine = context.Foos
      .FromSql($"EXECUTE CT.GetTransData {iN_MEM_IDParameter}")
      .Select(x => x.Bar)
      .ToList();

   return offLine;
}