How can I map a "select all" query with 2 tables using SqlQuery

user_424952 0
Viewed 140

I'm trying to retrieve all fields from two joined tables to any kind of a c# object.

So I'm trying to run this code:

var query = @$"EXEC('select *
   from persons p join students s on p.id=s.id 
   where p.id = 21')";

var result = _context.Database.SqlQuery<?>(query).ToList();

But I don't get what should be instead of the question mark.

I've tried List<object> and Dictionary<string,string> but since I couldn't get exactly how this is being mapped, I don't understand to what it can be mapped.

There is a somewhat similar question here but its solution only addresses two columns, and it apparently doesn't support returning nulls.

entity-framework-6 entity c#
2 Answers

You can try creating a stored procedure or a function in the SQL level. Then, just select then generated table / result. So, you already have an idea of what class it is.

user_70168390

I frequently use the dynamic type like this :

  var lst =   _context.Database.SqlQuery<dynamic>(query).ToList();  
  foreach (var item in lst)
  {
     var myVar = item.myfieldName;
  }

It may be preferable to name each field in your query instead of using select *.

user_85065120
Related