CODEWITHSUNDEEP
c#.netlinqentity-frameworklinq-to-entities
crichavin
crichavin

Reputation: 4582

How to query child tables values

I have 2 tables: POHeader and PODetail. I want to return all POHeaders that have an associated PODetail.ItemId = intItemId. How can I do this in LINQ?

This is what I've tried.

First I have a method in my Repository that uses the Include parameter to include the PODetails:

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

Then the result of that gets passed to:

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;
}

What is the correct way to do this?

Also, I can foresee needing 2 results of this:

  1. just return a IQueryable
  2. return a joined table result.

Upvotes: 1

Views: 107

Answers (2)

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

Try this; 

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

Upvotes: 3

AaronLS
AaronLS

Reputation: 38392

Assuming your a Header->Detail is a 1-to-many relationship, and Detail has a navigation back to it's header called .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 ;
}

Untested, but I think that will give you what you want.

Upvotes: 1

Related Questions