Convert EF6 to EF core with the ObjectResult

Admin 1
Viewed 790

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

ef-core-2.0 asp.net-core .net-core entity-framework c#
2 Answers

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 {0}", iN_MEM_IDParameter)
      .Select(x => x.Bar)
      .ToList();

   return offLine;
}
user_42753420

Your Context needs a virtual DbSet<string> ResultStrings { get; set; } that you can call upon and put the result in. (This does not work, see this post or Roman Marusyk comment below)

EDIT: Your Context needs a virtual DbSet<ResultEntity> ResultEntities { get; set; } that you can call upon and put the result in.

Then you can do return clinical.ResultEntities.FromSql("EXECUTE CT.GetTransData {0}", iN_MEM_IDParameter").toList() to fill the set.

Considering the ResultEntity has an Id and a value property, you can do ResultEntities.Select(e => e.value).toList() to extract a list of strings from the set.

user_67482010
Related