Query where multiple columns have to match a value set simultaneously in EF Core-CodePudding

The problem

I have a client application which loads a local data file. This data file specifies for each item the Type and Version.

From this file, I compile a list of Type and Version pairs.

var typeVersionSets = datafile.Select(item => new { Type = item.TypeId, Version = item.VersionId }).Distinct();

Note: there are more than these two fields, but for the sake of simplicity I just denote these two.

I also have a SQL Server which runs in the cloud. I need to get all records from a table which meet the value pairs (so the column values must match simultaneously).

I wrote this simple query which cannot be run by EF Core:

List<MyTableRow> MyResult = await dbContext.MyTable
    .Where(dbItem => typeVersionSets.Contains(new { Type = dbItem.TypeId, Version = dbItem.VersionId }))
    .ToListAsync();

I get the following runtime error:

One or more errors occurred. (The LINQ expression 'DbSet().Where(p => __MyTableRowTypeVersions_2.Contains(new { Type = p.TypeId, Version = p.VersionId }))' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.)

TLDR some details

The MyTable is huge and I cannot afford to download it every time and evaluate the LINQ expression on the client.

The number of typeVersionSets is reasonably small (let's say 10 sets).

Of course, I can loop over typeVersionSets like:

List<MyTableRow> MyResult = new List<MyTableRow>();

foreach (var set in typeVersionSets)
{
    MyResult.AddRange(
            await dbContext.MyTable
                .Where(pp => pp.TypeId == set.Type && pp.VersionId == set.Version)
                .ToListAsync()
            );
}

However, this would require 10 database calls.

This code will be executed many times per user and by many users.

Is there a more efficient solution which would result in 1 database call per event without transferring a lot of unnecessary data to the client (or the server).

Some additional notes

I use:

.NET (core) 5.0
Entity Framework Core version 5.0.9.

In case it matters, I cannot migrate to EF Core 6 since this required a migration to .NET (core) 6.0 which raises a lot of issues which are out of my scope.

CodePudding user response：

I'd be inclined to build a dynamic Expression<Func<MyTableRow, bool>> to represent the filter.

var p = Expression.Parameter(typeof(MyTableRow), "dbItem");

var parts = new List<Expression>();
foreach (var set in typeVersionSets)
{
    var typeIdValue = Expression.Property(p, nameof(MyTableRow.TypeId));
    var typeIdTarget = Expression.Constant(set.Type);
    var typeIdTest = Expression.Equal(typeIdValue, typeIdTarget);
    
    var versionIdValue = Expression.Property(p, nameof(MyTableRow.VersionId));
    var versionIdTarget = Expression.Constant(set.Version);
    var versionIdTest = Expression.Equal(versionIdValue, versionIdTarget);
    
    var part = Expression.AndAlso(typeIdTest, versionIdTest);
    parts.Add(part);
}

var body = parts.Aggregate(Expression.OrElse);
var filter = Expression.Lambda<Func<MyTableRow, bool>>(body, p);

List<MyTableRow> MyResult = await dbContext.MyTable
    .Where(filter)
    .ToListAsync()

Expression Trees (C#) | Microsoft Docs

CodePudding user response：

You can use this extension:

dbContext.MyTable
    .FilterByItems(typeVersionSets, (pp, set) => pp.TypeId == set.Type && pp.VersionId == set.Version, true)
    .ToListAsync();