实体框架包含3个表的位置 [英] Entity Framework Include With Where With 3 Tables

问题描述

我正在尝试从一个基表中包含两个表，并在第二个表上提供一个where语句，但是我得到一个非常混乱的错误（下面）。有关问题/解决方案的任何想法？

  ObjectQuery< STATE> productQuery = 
 LeadsContext.STATE.Include（REGION）
 .Where（it.REGION.BRAND.BRAND_ID = @brand，新的ObjectParameter（品牌，品牌））
 .OrderBy（ it.STATE_ABBV）; 
  

基本表格布局：STATE ------ REGION ------ BRAND

BRAND_ID在BRAND中

'BRAND'不是瞬态的成员。集合[Citizens.Leads.Data.REGION（可空=真，默认值=）]。
要从集合中提取属性，您必须使用一个子查询
到
遍历集合，在multipart标识符附近，第8行，列
1。

解决方案

听起来好像 State.REGION 实际上是 Region 实体的集合。

在这种情况下，您不能直接访问BRAND导航因为您的语句试图访问集合的BRAND属性，而不是集合中元素的BRAND属性。

如果使用LINQ编写此查询到实体，而不是查询构建器方法，你可以这样做：

  var productQuery =从LeadsContext.State 
从r在s.REGION 
其中r.Brand.Brand_ID ==品牌
 orderby s.STATE_ABBR 
选择s; 
  

当然，这不会急于加载REGION，所以你可能会认为你可以写这个：

  var productQuery = from s in LeadsContext.State.Include（REGION）
 from r in s.REGION 
其中r.Brand.Brand_ID ==品牌
 orderby s.STATE_ABBR 
选择s; 
  

但是，这不会工作，因为当您执行选择许多时，INCLUDE丢失来自y中的y的y从y中的y $ / code>）。

所以你必须这样做的包括：

  var productQuery =（from s in LeadsContext.State 
 from r in s.REGION 
其中r.Brand.Brand_ID ==品牌
 orderby s.STATE_ABBR 
 select s）作为ObjectQuery< State>）。包含（REGION）; 
  

请参阅提示22 了解此解决方法的更多信息。

我不是100％肯定我们的查询构建器方法，即Where（字符串），支持子选择哪个是必需的。

所以我不知道那里有什么语法。

无论如何，我希望这有助于

Alex

I'm trying to include two tables off of one base table, and provide a "where" statement on the second table, but I'm getting a very confusing error (below). Any thoughts on the issue/solution?

ObjectQuery<STATE> productQuery = 
    LeadsContext.STATE.Include("REGION")
      .Where("it.REGION.BRAND.BRAND_ID = @brand", new ObjectParameter("brand", brand))
      .OrderBy("it.STATE_ABBV");

Basic table layout: STATE ------ REGION ------ BRAND

BRAND_ID is in BRAND

'BRAND' is not a member of 'Transient.collection[Citizens.Leads.Data.REGION(Nullable=True,DefaultValue=)]'. To extract properties out of collections, you must use a sub-query to iterate over the collection., near multipart identifier, line 8, column 1.

解决方案

It sounds as if State.REGION is actually a collection of Region entities.

In which case you can't just access the BRAND navigation directly like that, because your statement tries to access the BRAND property of a Collection, rather than the BRAND property of an element in the collection.

If you were writing this query using LINQ to Entities rather than query builder methods you could do it like this:

var productQuery = from s in LeadsContext.State
                   from r in s.REGION
                   where r.Brand.Brand_ID == brand
                   orderby s.STATE_ABBR
                   select s;

Of course that wouldn't eagerly load REGION(s) so you might think you could write this:

var productQuery = from s in LeadsContext.State.Include("REGION")
                   from r in s.REGION
                   where r.Brand.Brand_ID == brand
                   orderby s.STATE_ABBR
                   select s;

But that won't work because your INCLUDE is lost when you do a Select Many (i.e. from y in z from x in y).

So you have to do the Include at the end like this:

var productQuery = (from s in LeadsContext.State
                   from r in s.REGION
                   where r.Brand.Brand_ID == brand
                   orderby s.STATE_ABBR
                   select s) as ObjectQuery<State>).Include("REGION");

See tip 22 for more on this workaround.

I'm not 100% sure that our query builder methods, i.e. Where(string), support sub-selects which is what is required.

So I'm not sure what the syntax would be in there.

Anyway I hope this helps

Alex

这篇关于实体框架包含3个表的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！