I am using EF Core and stuck in a scenario where I need to fetch all the Parent table records that has child records matching the given child records. Example:

Parent Table

Child Table

I need Linq to get all parents which matches below search parameters.

All prents with Child records same as: Child: [ {C1,20}, {C2,25}]

So, it should return the Parent P1 and P2 as result. I am trying EqualityComparer but getting not translated error from EF. Any help is appreciated.

CodePudding user response：

I don't think EF supports this. I didn't find a direct solution when I was in the same situation (multi-column primary key on the child table). My workaround was to add artificial (computed stored) string column to the child table that combined all the PK parts, after that you should be able to do:

var childrenKeys = new List<string>();
/* .... fill it with children keys you are interested in ... */
var parents = dbContext.Parents
    .Where(x => x.Children.Any(xx => childrenKeys.Contains(xx.NameAndAge))
    .ToList();

Only other option I know of is to switch to client side evaluation and filter it yourself.

CodePudding user response：

I believe you might be searching for this:

context.Parents
    .Include(parent => parent.Childs)
    .Where(parent => parent.Childs
        .Where(child => 
            (child.Name == 'C1' && child.Age == 20)
            || (child.Name == 'C2' && child.Age == 25)).Count==2);

Alternatively, I believe the following would also work:

context.Childs
    .Include(child => child.Parent)
    .GroupBy(child => child.Parent.Id)
    .Select(childsWithSameParent => childsWithSameParent.Where(
       (child.Name == 'C1' && child.Age == 20)
       || (child.Name == 'C2' && child.Age == 25)).Count()==2)
    .Select(childsWithSameParent => childsWithSameParent.First().Parent)