复杂的LINQ'任何'查询 [英] Complex LINQ 'Any' query

问题描述

我有实体框架映射下表：

发票（InvoiceID，InvoiceNumber，IsPaid）

零件（PARTID，部分号码， OrganisationID，IsActive）

Invoices_Parts（InvoiceID，PARTID，数量IsClaimed）

我试图让名单有一些条件从 Invoices_Parts 表InvoiceIDs的。我有一个问题的条件是其中的任何的部分从 Invoices_Parts 是激活

到目前为止的代码：

 的IQueryable< INT> ; partIds = db.Parts.Where（X => 
 x.OrganisationID == loggedInUser.OrganisationID）。选择（Y => y.PartID）; 
 //&放大器;&安培; x.IsActive 
 
名单，LT;字符串> invoiceIds = db.Invoices_Parts.Where（X =>！
 x.IsClaimed&放大器;&安培; x.Invoice.IsPaid和放大器;&安培; partIds.Contains（x.PartID）
 .DistinctBy（Y = > y.InvoiceID）
。选择（Z => z.InvoiceID.ToString（）。）了ToList（）; 
  

我注释掉&放大器;&安培; x.IsActive，因为我想InvoiceIDs的名单是这样的，至少有一部分必须满足IsActive条件 - 我不想过滤掉所有的部件，其中IsActive是假的。我如何在LINQ做到这一点，而无需通过集合手动循环和添加/删除项目？

注意：如果你想知道，我使用下列helper方法 DistinctBy ：

 公共静态的IEnumerable< TSource> DistinctBy< TSource，TKEY的>（这个IEnumerable的< TSource>源，Func键< TSource，TKEY的>的KeySelectors）
 {
的HashSet< TKEY的> seenKeys =新HashSet的< TKEY的>（）; 
的foreach（源TSource元素）
 {
如果（seenKeys.Add（的KeySelectors（元素）））
 {
收益率的回报元件; 
} 
} 
} 
  

修改：我对每个实体以下属性：

发票：

  public虚拟的ICollection< Invoices_Parts> Invoices_Parts {搞定;组; } 
  

部分：

  public虚拟的ICollection< Invoices_Parts> Invoices_Parts {搞定;组; } 
  

Invoices_Parts：

  public虚拟发票发票{搞定;组; } 
公共虚拟部件部件{搞定;组; } 
  

解决方案

人们往往过于复杂的LINQ查询，因为他们是如此用于在SQL表的角度来思考，联接和这样的。通常，如果你把你的实际需求用简单的英语，你可以拿出读取几乎一模一样简单的查询。

我想从支付的发票，其中在该发票的部分是活跃，...等发票ID列表。

这个怎么样

$ b在db.Invoices
$ b

 从哪里发票invoice.IsPaid 
，其中invoice.Invoices_Parts.Any（ IP =>！ip.IsClaimed和放大器;&安培; 
 ip.Part.IsActive&放大器;&安培; 
 ip.OrganisationID == loggedInUser.OrganisationID）
选择invoice.InvoiceId 
  

或者，如果你喜欢的方法语法：

  db.Invoices.Where（I => i.IsPaid）
。凡（I => i.Invoices_Parts.Any（IP = GT;！ip.IsClaimed和放大器;&安培; 
 ip.Part.IsActive&放大器;&安培; 
 ip.OrganisationID == loggedInUser.OrganisationID）
。选择（I => i.InvoiceId）
 .ToList（）; 
  

PS - 你可以做一个的ToString（）如果你想，但我的经验是明智的离开ID的强类型

PPS - 你可以做一个DistinctBy方法，将与实体框架发挥很好（不是强迫过早的评价）像这样：

 公共静态的IQueryable< TSource> DistinctBy< TSource，TKEY的>（这IQueryable的< TSource>源，Excpression< Func键< TSource，TKEY的>>的KeySelectors）{
返回source.GroupBy（的KeySelectors）。选择（I => i.FirstOrDefault（） ）; 
} 
  

I have the following tables mapped with Entity Framework:

Invoices (InvoiceID, InvoiceNumber, IsPaid)
Parts (PartID, PartNumber, OrganisationID, IsActive)
Invoices_Parts (InvoiceID, PartID, Quantity, IsClaimed)

I'm trying to get a List of InvoiceIDs from the Invoices_Parts table with a number of conditions. The condition I'm having a problem with is "where any Part from Invoices_Parts is active".

The code so far:

IQueryable<int> partIds = db.Parts.Where(x => 
    x.OrganisationID == loggedInUser.OrganisationID).Select(y => y.PartID);
    // && x.IsActive

List<string> invoiceIds = db.Invoices_Parts.Where(x => 
    !x.IsClaimed && x.Invoice.IsPaid && partIds.Contains(x.PartID)
    .DistinctBy(y => y.InvoiceID)
    .Select(z => z.InvoiceID.ToString()).ToList();

I've commented out "&& x.IsActive" because I want the list of InvoiceIDs to be such that at least one part must meet the IsActive condition - I don't want to filter out all the parts where IsActive is false. How do I achieve this in LINQ without manually looping through collections and adding/removing items?

Note: In case anyone is wondering, I'm using the following helper method for DistinctBy:

public static IEnumerable<TSource> DistinctBy<TSource, TKey>(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
{
    HashSet<TKey> seenKeys = new HashSet<TKey>();
    foreach (TSource element in source)
    {
        if (seenKeys.Add(keySelector(element)))
        {
            yield return element;
        }
    }
}

Edit: I have the following properties on each entity:

Invoice:

public virtual ICollection<Invoices_Parts> Invoices_Parts { get; set; }

Part:

public virtual ICollection<Invoices_Parts> Invoices_Parts { get; set; }

Invoices_Parts:

public virtual Invoice Invoice { get; set; }
public virtual Part Part { get; set; }

解决方案

People often overcomplicate LINQ queries because they're so used to thinking in terms of SQL tables, joins, and such. Usually if you put your actual requirement in plain english, you can come up with a simpler query that reads almost exactly the same.

I want a list of invoice IDs from the paid invoices where any part on that invoice is active, and ... etc.

How about this:

from invoice in db.Invoices
where invoice.IsPaid
where invoice.Invoices_Parts.Any(ip => !ip.IsClaimed && 
    ip.Part.IsActive && 
    ip.OrganisationID == loggedInUser.OrganisationID)
select invoice.InvoiceId

Or if you prefer method syntax:

db.Invoices.Where(i => i.IsPaid)
.Where(i => i.Invoices_Parts.Any(ip => !ip.IsClaimed && 
    ip.Part.IsActive && 
    ip.OrganisationID == loggedInUser.OrganisationID)
.Select(i => i.InvoiceId)
.ToList();

PS--you can do a .ToString() if you want to, but in my experience it's wiser to leave IDs strongly typed.

PPS--you can make a DistinctBy method that will play nicely with Entity Framework (not forcing premature evaluation) like so:

public static IQueryable<TSource> DistinctBy<TSource, TKey>(this IQueryable<TSource> source, Excpression<Func<TSource, TKey>> keySelector) {
    return source.GroupBy(keySelector).Select(i => i.FirstOrDefault());
}

这篇关于复杂的LINQ'任何'查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！