Loading navigation properties using 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 run it with:

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

How to configure EF so that it loads the Server property of my object with the return data from the request?

Based on @octavioccl's answer, I ended up with this:

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

But I'm afraid it makes a lot of db rides?

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 , and they are automatically tracked by the database context unless you turn off tracking.

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

After executing the request, you can get Server through your entity instance through lazy loading:

 var server=entity.Server; 

---

On the other hand, the returned Database.SqlQuery data is not monitored by the database context, even if you use this method to retrieve object types. If you want to track the objects that you receive after executing a query using this method, you can try the following:

 //Attach the entity to the DbContext _context.Entities.Attach(entity); //The Server navigation property will be lazy loaded var server=entity.Server; 

If you turned off lazy loading, you can explicitly load your navigation property using the DbEntityEntry.Reference() method:

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