如何查询子表值

Question

我有2个表:POHeader和PODetail。我想返回所有有关联PODetail的poheader。ItemId = intItemId。我如何在LINQ中做到这一点?

这是我试过的。

首先，我在仓库中有一个方法，它使用Include参数来包含PODetails:

public IQueryable<POHeader> SearchForWithDetails(int intFacilityId)
{
    return DbSet.Include("PODetails").Where(x => x.FacilityId == intFacilityId);
}

然后结果传递给:

public IQueryable<POHeader> SearchForPODetailsByItemId(IQueryable<POHeader> poHeaders, int intItemId)
{
    //This returns a type of PODetail not POHeader
    var q = poHeaders.SelectMany(c => c.PODetails).Where(c => c.ItemId == intItemId);
    //In this case, I can't figure out the syntax :(
    var p = from poHeader in poHeaders
            let filteredPOs = from poDetail in poHeader.PODetails
            where poDetail.ItemId == intItemId
            select ????
    return p;
}

正确的做法是什么?

同样，我可以预见需要两个结果:

1. 返回一个IQueryable
2. 返回连接表的结果。

Answer 1

试试这个;

var result = poHeaders.Where(e => e.PODetails.Any(a => a.ItemId == intItemId)); 

Answer 2

假设你的a Header->Detail是一对多的关系，Detail有一个返回到它的头部的导航名为。Header:

public IQueryable<POHeader> SearchForPODetailsByItemId(IQueryable<POHeader> poHeaders, int intItemId)
{
    var headersForThisItem = poHeaders.SelectMany(pod => pod.PODetails).Where(pod => pod.ItemId == intItemId)
        .Select(pod=> pod.Header).Distinct();//.Distinct to eliminate duplicates when 2 Details have the same header.  Not necessary if ItemId filter naturally provides distinct results.
    return headersForThisItem ;
}

未经测试，但我想这会给你你想要的。