在EntityFramework中使用SqlQuery组成一个真实的IQueryable [英] Use SqlQuery in EntityFramework to compose a real IQueryable

问题描述

有没有一种方法来获取SqlQuery或任何其他sql执行方法来组成模型对象并允许诸如Include之类的方法起作用？我正在执行此操作（其中view_products是SQL表函数）：

Is there a way to get SqlQuery or any other sql execution method to compose a model object and allow methods like Include to work? I'm doing this (where view_products is an SQL table function):

var p = context.SqlQuery<Product>("select * from view_products(@param1)", new SqlParameter("param1", "somevalue"));

然后我想这样做：

var list = p.Include(i => i.Inventories).ToList();

这给了我一个DbRawSqlQuery。我无法包含，因为您无法在DbRawSqlQuery上执行此操作。因此，我在末尾添加了一个AsQueryable，例如：

This gets me a DbRawSqlQuery. This I can't "Include", because you can't do that on a DbRawSqlQuery. So, I add an AsQueryable to the end, like:

var p = context.SqlQuery<Product>("select * from view_products(@param1)", new SqlParameter("param1", "somevalue")).AsQueryable<Product>();

但是，我知道这确实给了我IEnumerable，所以.include不会产生任何结果结果。

But, I understand this is really giving me an IEnumerable, so the .Include doesn't yield any results.

我正在寻找另一种方法的建议，该方法是从SQL函数获取我的产品，然后加载其相关的库存对象，以便对象图。谢谢！

I'm looking for suggestions on another way to do this where I get my Products from the SQL function and then load in their related Inventory objects so that I have an object graph. Thanks!

推荐答案

SqlQuery 带来了一些我不是的问题

SqlQuery poses problems some of which I wasn't yet aware of.

您正确地解释了为什么 Include 没有将 IEnumerable 转换为 IQueryable 后的效果。 Include 扩展了一个表达式，而 IEnumerable 没有表达式（转换后无法神奇地得到一个表达式）将其保存到 IQueryable ）。

You correctly explain why Include doesn't have any effect after you convert an IEnumerable to an IQueryable. Include extends an expression, and an IEnumerable doesn't have an expression (and doesn't magically get one after converting it to IQueryable).

所以您最终遇到了产品的列表，该列表未附加到上下文并且无法进行延迟加载。您必须将 Product s明确附加到上下文（例如，通过将其状态设置为 Unchanged ），以使它们变得懒惰

So you end up having a list of Products not attached to the context and not capable of lazy loading. You have to attach the Products to the context explicitly (e.g. by setting their state to Unchanged) to make them lazy loading.

这是我刚刚发现的东西，很奇怪。实体被实体化为代理对象，但是与 DbSet.AsNoTracking（）获取的分离实体相反，它们不显示延迟加载。我认为这是一个错误，我已经报告了。

This is something I just found out and which is odd. The entities are materialized as proxy objects, but, contrary to detached entities that are fetched by DbSet.AsNoTracking(), they don't display lazy loading. I think it's a bug and I reported it.

但是延迟加载会导致n + 1查询反模式：查询会分别获取每个产品的库存对象。因此，这不是获取对象图的最佳方法。

But lazy loading causes the n + 1 query anti-pattern: queries that fetch Inventory objects for each Product separately. So it's not the best way to get your object graph.

所以，您真正想要的是加载属于该对象的所有库存对象一次性完成这些产品的工作，并让它们完成其工作（即EF自动填充所有 Product.Inventories 集合）：

So what you really want is load all Inventory objects that belong to these products in one shot and let relationship fixup do its job (i.e. EF auto-fills all Product.Inventories collections):

context.Inventories.Where(i => productIds.Contains(i.ProductId)).Load();

...其中， productIds 是列表中的产品ID p 。

...where productIds is a list of procuct Ids in p.

但是关系修复仅适用于附加的实体，因此，再次，您必须将其状态更改为未更改。

But relationship fixup only works for attached entities, so again, you'll have to change their state to Unchanged explicitly. If you do that, you're ready to go... except you aren't.

同样，通过 DbSet.AsNoTracking（ ）。后者响应关系修正，而 SqlQuery 中的响应则不响应！

Again there's a difference with detached entities fetched by DbSet.AsNoTracking(). The latter respond to relationship fixup, the ones from SqlQuery don't!

剩下的就是：加载相关的库存对象，如上所示，并填充 Product.Inventories 自己收集：

So what's left is: load the related Inventory objects as show above and fill the Product.Inventories collections yourself:

var query = from product in p
            join inv context.Inventories.Local
                on product.ProductId equals inv.ProductId
                into inventories
            select new { product, inventories }
foreach(var anon in query)
{
    anon.product.Inventories = anon.inventories.ToList();
}

当然，这一切都不尽人意。

Of course this is all quite unsatisfactory.

这篇关于在EntityFramework中使用SqlQuery组成一个真实的IQueryable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！