Obtaining entities from DbSet from a list of matching objects

Question

I'm using Entity Framework Core 6 and I want to find a series of entities in a DbSet. The entities I want to obtain are the ones match some properties in a list of input objects.

I've tried something like this:

public IEnumerable<MyEntity> FindEntities(IEnumerable<MyEntityDtos> entries)
{
    return dbContext.MyDbSet.Where(r => entries.Any(e => e.Prop1 == r.Prop1 && e.Prop2 == r.Prop2));            
}

But I get the classic EF Core exception saying that my LINQ cannot be translated to a database query (the problem in particular is the entries.Any(...) instruction)

I know I can just loop over the list of entries and obtain the entities one by one from the DbSet, but that is very slow, I was wondering if there was a more efficient way to do this in EF Core that I don't know about.

Use this extension [FilterByItems](https://stackoverflow.com/a/67666993/10646316). And use in the following way: `dbContext.MyDbSet.FilterByItems(entries, (r, e) => e.Prop1 == r.Prop1 && e.Prop2 == r.Prop2, true);` — Svyatoslav Danyliv, Sep 21 '22 at 13:19
That's weird, I've tried it but as soon as I invoke it the program crashes without even raising an exception, error log tells me: `program exited with code 3221225477 (0xc0000005) 'Access violation'.` — Master_T, Sep 21 '22 at 13:37
A lot (tens of thousands). Maybe the expression builder is causing an overflow... — Master_T, Sep 21 '22 at 13:42
Well, then I can only suggest third party extension, `FilterByItems` works with small amount of records. What you are trying to do in overall? Upsert? — Svyatoslav Danyliv, Sep 21 '22 at 13:44
Yes, "upsert" is exactly what I'm trying to do, I didn't know this term existed — Master_T, Sep 21 '22 at 14:04
It should be some generic method? How to match records? Via PK? Post sample entity which you are trying to Upsert. — Svyatoslav Danyliv, Sep 21 '22 at 14:41
@Master_T If you want to InsertOrUpdate entities you can this awesome nuget package: https://github.com/borisdj/EFCore.BulkExtensions — sa-es-ir, Sep 21 '22 at 14:49

score 0 · Answer 1 · answered Sep 21 '22 at 13:05

0

I think this should work:

public IEnumerable<MyEntity> FindEntities(IEnumerable<MyEntityDtos> entries)
{
    var props1=entries.Select(x=>x.Prop1).ToArray();
    var props2=entries.Select(x=>x.Prop2).ToArray();

    return dbContext.MyDbSet.Where(r => props1.Contains(r.Prop1) && props2.Contains(r.Prop2));            
}

answered Sep 21 '22 at 13:05

sa-es-ir

3,722
2
13
31

Mmmm, I don't think it does the same thing that I want to do, it would be the same if my function inside `Any` used `||` instead of `&&` – Master_T Sep 21 '22 at 13:10
Yes it would be the same, with EF you can pass only list of values and it will convert it to ``IN`` or ``NOT IN``, – sa-es-ir Sep 21 '22 at 13:13
No it wouldn't. Let's assume the database contains an entity where `Prop1=="X"` and `Prop2=="Y"`. Your expression just checks if one of the `entries` has `Prop1=="X"` and if one has `Prop2=="Y"`, but there are no guarantees at all that they would be the SAME entity. – Master_T Sep 21 '22 at 13:21
@Master_T In case of ``&&`` it works but in ``||`` yes it may lead to unpredictable results – sa-es-ir Sep 21 '22 at 14:01
1

@SaeedEsmaeelinejad This is a pretty hard problem, you will not find any straight forward optimized answer. You can look at this question and answer. https://stackoverflow.com/questions/26198860/entityframework-contains-query-of-composite-key – Moshi Sep 21 '22 at 16:58

score 0 · Accepted Answer · answered Oct 25 '22 at 10:12

In the end, I've done this:

public static IEnumerable<MyEntity> GetRangeByKey(this DbSet<MyEntity> dbSet, IEnumerable<MyEntity> toFind)
{
    var keys = new HashSet<string>(toFind.Select(e => e.Id));
    IEnumerable<MyEntity> result = null;

    for (int i = 0; i < keys.Length; i += 1000)
    {
        var keyChunk = keys[i..(Math.Min(i + 1000, keys.Length))];
        var res = dbSet.Where(x => keyChunk.Any(k => x.ResourceArn == k));
        if (result == null)
        {
            result = res;
        }
        else
        {
            result = result.Concat(res);
        }
    }

    return result;
}

Basically I get the keys to find in a HashSet and use it to perform a Where query, which will be translated to a SQL IN clause which is quite fast. I do it in chunks because there's a maximum number of values you can put in a IN clause before the DB engine refuses it.

Obtaining entities from DbSet from a list of matching objects

2 Answers2