EF Core 2.1 check if pair of properties exist in table

Question

Im try to refactor this method to take list of items:

public async Task<bool> IfExist(string id1, string id2)
{
    return await DbSet.AnyAsync(x => x.Id1 == id1 && x.Id2 == id2 &&);
}

That method works perfectly and produce SQL that looks like this:

Command: SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM "SomeTable" AS x
        WHERE ((x."Id1" = @__id1) AND (x."Id2" = @__id2))
    THEN TRUE::bool ELSE FALSE::bool
END
-- Parameters:
@__id1: value
@__id2: value

But now, i want to write IfExist method that takes lists of ids and checking pairs of id1 and id2. I wrote something like this:

public async Task<bool> IfExist(IEnumerable<string> ids1, IEnumerable<string> ids2)
{
    var ids = ids1.Zip(ids2, (id1, id2) => new { id1, id2 });

    return await DbSet.AnyAsync(x => ids.Contains(new { id1 = x.Id1, id2 = x.Id2 }));
}

But its just execute SELECT * FROM ... on my database so basiclly this query is executed in memory, not in database. EF Core 2.1 cant produce SQL code from that LINQ query.

My question is, how to write query that check if any pair of two properties exist in whole table using EF Core 2.1 and LINQ?

Edint when i try to contains existing of Tuple<string, string> its still not work correctly and executed SQL looks like this:

Command: SELECT x."Id1", x."Id2"
FROM "SomeTable" AS x

So it means that my query download whole table from DB and make a linq query in memory

Arman Ebrahimpour · Answer 1 · 2020-05-15T13:16:36.437

1

EF Core doesn't have any option to natively support that behaviour. One solution would be something like this:

return await DbSet.Where(x => ids1.Contains(x.Id1) && ids2.Contains(x.Id2))
                  .AnyAsync(x => ids.Contains(new { id1 = x.Id1, id2 = x.Id2 }));

This way, at least smaller bunch of records will be fetched.

Also you can think to another solution:

public async Task<bool> IfExist(IEnumerable<string> ids1, IEnumerable<string> ids2)
{
    var ids = ids1.Zip(ids2, (id1, id2) => $"{id1}***{id2}");
    return await DbSet.AnyAsync(x => ids.Contains(x.id1 + "***" + x.id2));
}

edited May 15 '20 at 13:16

answered May 15 '20 at 12:28

Arman Ebrahimpour

4,252
1
14
46

1

that string concats looks pretty intresting, i will check it out in the future, thanks! – michasaucer May 15 '20 at 13:17

michasaucer · Accepted Answer · 2020-05-16T20:09:43.340

EDIT:

I needed to build custom expression with this case. I needed to check two ids, then set OR in SQL and then check second pair of ids, then set OR and so on.

After while i developed this solution that looks it works:

// main func...
{
    var ids = ids1.Zip(ids2, (id1, id2) => (Id1: id1, Id2: id2));

    var query = DbSet.AsQueryable();
    var filter = MakeExpression(ids);
    query = query.Where(filter);

    return await query.AnyAsync();

}

private Expression<Func<Data, bool>> MakeExpression(
    IEnumerable<(string id1, string id2)> ids
    )
{
    var parameter = Expression.Parameter(typeof(Data), "x");
    var memberId1 = Expression.Property(parameter, nameof(Data.Id1));
    var memberId2 = Expression.Property(parameter, nameof(Data.Id2));

    var expression = Expression.NotEqual(Expression.Constant(1), Expression.Constant(1));
    foreach (var id in ids)
    {
        var idsAnd = Expression.AndAlso(
            Expression.Equal(memberId1, Expression.Constant(id.id1)),
            Expression.Equal(memberId2, Expression.Constant(id.id2)));

        expression = Expression.Or(expression, idsAnd);
     }

     var lambda = (Expression<Func<Data, bool>>)Expression.Lambda(expression, parameter);

     return lambda;
}

It seems good too, but need to check its performance due to lots of `WHERE` clauses — Arman Ebrahimpour, May 15 '20 at 13:13
I belive that we can write it better using `Expressions`, but im newbie in EF — michasaucer, May 15 '20 at 13:16

EF Core 2.1 check if pair of properties exist in table

2 Answers2