loading navigation properties with raw sql query

Question

I have this SQL query:

SELECT 
    t.ServerId, t.Id, s.Name
FROM 
    MyTable as t
JOIN 
    Server s ON t.ServerId = S.Id

I'm running it with:

context.Database.SqlQuery<entity>("query_goes_here");

How can I configure EF so that it loads the Server property of my entity with the return data from the query?

Based on the answer by @octavioccl, I ended up doing this:

foreach(var result in results)
{
    context.Attach(result);
    context.Entry(result).Reference(p => p.Server).Load();
}

But I'm afraid this is making a lot of db trips?

Answer 1

Use the DbSet.SqlQuery method for queries that return entity types. The returned objects must be of the type expected by the DbSet object, and they are automatically tracked by the database context unless you turn tracking off.

var enties= _context.Entities.SqlQuery("query_goes_here");

After execute your query, you should be able of get the Server through your entity instance via lazy loading:

var server=entity.Server;

---

On the other hand, the returned data by the Database.SqlQuery isn't tracked by the database context, even if you use this method to retrieve entity types. If you want to track the entities that you get after execute your query using this method, you can try this:

//Attach the entity to the DbContext
_context.Entities.Attach(entity);

//The Server navigation property will be lazy loaded
var server=entity.Server;

If you have disabled lazy loading you can load explicitly your navigation property using the DbEntityEntry.Reference()method:

//Load the Server navigation property explicitly
_context.Entry(entity).Reference(c => c.Server).Load(); 

Answer 2

You can load all the rows for your navigation property first then load your main entity. Apply a filter on it first if you wish.

var servers = context.Servers.Where(m => m.ServerType == "Windows").ToList();
var mytable = context.MyTables.ToList();

or using SqlQuery

var servers = context.Database.SqlQuery<Server>("query_goes_here");

foreach (var serv in servers)
    context.Servers.Attach(serv);

then query MyTable and EF will link the two together for you