Find all objects in one list that have a property that matches a property in another list of objects

Question

Problem

I am attempting to query a table (based on ModelA) using another table (based on ModelB). These models are simplified for this example. I need to keep the result as an IQueryable, so changing to an Enumerable or List are not options. Still, I tried adding .ToList() but got the same error.

Pulling the MyIds out of one list into a list of strings (in order to use Contains()) isn't an option since there may be too many MyIds (> 40k) that causes an error indicating the operation as run out of resources, which I would guess refers to RAM.

Error

InvalidOperationException: The LINQ expression ... could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

ModelA

public class ModelA
{
    public string MyId { get; set; }
    public string MyName { get; set; }
}

ModelB

public class ModelB
{
    public string MyId { get; set; }
    public string MyName { get; set; }
}

Attempt

var results = context.ModelA
            .Where(a => ModelB.All(b => b.MyId == a.MyId));

What way(s) can this be done successfully?

Answer 1

you could try checking to see if the second list contains 'Any()' of matching ID

var results = context.ModelA
        .Where(a => ModelB.Where(b => b.MyId == a.MyId).Any());

or you might want to try Join

https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/join-clause

var results = context.ModelA
    .Where(a => ModelB.
            Any(b => b!= null  && b.id != null 
                  && a!=null && a.id!==null && 
                  b.MyId == a.MyId));

I had Select instead of Where and I've checked for nulls

Answer 2

You can try this:

var results = ctx
    .ModelAs
    .Where(ma => ctx.ModelBs.Any(mb => mb.MyId == ma.MyId));

I used your models, this is my DbContext:

public class MockContext : DbContext
{
    public MockContext(DbContextOptions<MockContext> options)
        : base(options)
    { }

    public DbSet<ModelA> ModelAs { get; set; }
    public DbSet<ModelB> ModelBs { get; set; }
}

The data I used to test this was:

ModelA:

MyId | MyName

1 Nettie Koch 
2 Karl Kuvalis 
3 Marcus Weissnat 
4 Shannon Hettinger 
5 Wilma Kuvalis 
6 Benny Brown 
7 Amanda Maggio 
8 Claude Kohler 
9 Dawn Ritchie 
10 Alan Ruecker

ModelB:

MyId | MyName

5 Francis Konopelski 
6 Mandy Yost 
7 Marsha Parisian 
8 Crystal Mayer 
9 Sergio Crona 
10 Kenny Rice 
11 Levi Gutkowski 
12 Brandon Haley 
13 Jan Kunze 
14 Rafael Blanda 

Result:

MyId | MyName

5 Wilma Kuvalis 
6 Benny Brown 
7 Amanda Maggio 
8 Claude Kohler 
9 Dawn Ritchie 
10 Alan Ruecker 

Answer 3

There are some ways you can achieve it, here are two ways for your reference:

var result = (from a in context.ModelA
                      join b in context.ModelB
                      on a.MyId equals b.MyId
                      select a);

Or you can try this method:

 var result = context.ModelA.Join(_context.ModelB,
                        x => x.MyId,
                        y => y.MyId,
                       (x, y) => x);

You can refer to this link for more details.

Answer 4

You might be looking Any() like below

var results = context.ModelA.Where(a => ModelB.Any(b => b.MyId == a.MyId));