使用原始 sql 查询加载导航属性 [英] loading navigation properties with raw sql query

问题描述

我有这个 SQL 查询:

I have this SQL query:

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

我正在运行它:

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

如何配置 EF 以便它使用查询的返回数据加载我的实体的 Server 属性?

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

根据@octavioccl 的回答，我最终这样做了:

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?

推荐答案

使用 DbSet.SqlQuery 方法用于返回实体类型的查询.返回的对象必须是 DbSet 对象所期望的类型，并且它们会被数据库上下文自动跟踪，除非您关闭跟踪.

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");

执行查询后，您应该能够通过延迟加载通过您的 entity 实例获取 Server:

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

var server=entity.Server;

<小时>

另一方面，Database.SqlQuery 不会被数据库上下文跟踪，即使您使用此方法来检索实体类型.如果您想跟踪使用此方法执行查询后获得的实体，可以尝试以下操作:

---

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;

如果您禁用了延迟加载，您可以使用 DbEntityEntry.Reference() 方法显式加载您的导航属性:

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(); 

这篇关于使用原始 sql 查询加载导航属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！