CODEWITHSUNDEEP
linqentity
Tyddlywink
Tyddlywink

Reputation: 891

Return parent record with only children that meet a certain criteria

I have two tables. One is batch and it has a child table of requests.

I need to return a batch record where one of it's children has a status of 0 and process of false. The lambda expression I have returns all parents that have any children that meet the criteria. The issue is that it's including children that don't meet the criteria.

Here is my expression

        List<Batch> batches = _DC.Batches.Where(
         c => c.Requests.Any(d => d.Processed == false && d.ApprovalStatus.ApprovalStatusId == 0))
              .Select(c => c).ToList();

How do I get this to return only requests that meet the criteria but still return the parent if any meet the criteria?

EDIT: This is what I'm doing now after turning off lazy loading. 

            List<Batch> batches = _DC.Batches.Where(
                c => c.Requests.Any(d => d.Processed == false && d.ApprovalStatus.ApprovalStatusId == 0))
                .Select(c => c).ToList();

        foreach (Batch batch in batches)
        {
            _DC.Entry(batch).Collection(c=>c.Requests).Query().Where(c=>c.ApprovalStatusId== 0 && c.Processed == false).Load();
        }

Upvotes: 0

Views: 436

Answers (1)

jhilden
jhilden

Reputation: 12419

Instead of starting at the Batches table, start at the Request table. Pseudo code:

var requests= _DC.Requests.Where(x => x.Processed == false && x.ApprovalStatusId == 0);

That code will get you all of the requests where Processed == false && ApprovalStatusId == 0. From there, if you want to get information on the batches just do:

foreach(var r in requests) {
   var batch = r.Batch;
   //do something
}

Upvotes: 1

Related Questions