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

asp.net-core ef-core-3.1 entity-framework-core linq

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?

1
2
2/26/2020 3:59:16 PM

Accepted Answer

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://docs.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

1
4/17/2020 3:15:16 PM

Popular Answer

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 


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow