There's a class that looks like this:

class CustomerPurchase {
    int CustomerId;
    int PurchaseId;
    int Cost;
}

I've used Code First approach and Entity Framework Core migrations to create the DB.

There's also a filter that looks like this:

class Filter {
    int CustomerId;
    int PurchaseId;
}

Now, I'm trying to filter the data by pairs of ids.

Here's my code:

var filters = new List<Filter>();

ctx.CustomerPurchases
    .Where(p => filters.Any(f => 
        f.CustomerId == p.CustomerId &&
        f.PurchaseId == p.PurchaseId))
    .ToList();

Obviously, there is no way this will work. filters is the list of objects and it will not be translated into the SQL query. I'm getting an exception: The LINQ expression blah could not be translated.

So, how do I make it work? This particular table contains a few millions of records, so there's no way I can filter it on the client side.

I'm not tied to entity framework: feel free to suggest a different approach, in case there is one. For obvious reasons, I'm not going to generate a raw query, like WHERE (CustomerId = {1} AND PurchaseId = {2}) OR .... Anything other than that is welcome.

CodePudding user response：

I had this issue earlier and here how I fixed it You have two options in this case

1- Filter based on the ids

var res1 = dbcontext.CustomerPurchases
    .Where(p => filters.Select(c=>c.PurchaseId).Contains(p.PurchaseId))
    .Where(p => filters.Select(c => c.CustomerId).Contains(p.CustomerId));

2- Use contains

var resq = await dbcontext.CustomerPurchases
    .Where(p=> filters.Contains(new Filter { CustomerId = p.CustomerId,PurchaseId = p.PurchaseId }))
    .ToListAsync();

but if you run this one you won't get any result unless you implement IEquatable

So your Filter class will look like this

public class Filter : IEquatable<Filter>
{
    public int CustomerId;
    public int PurchaseId;

    public bool Equals(Filter? other)
    {
        return this.PurchaseId == other.PurchaseId &&
            this.CustomerId == other.CustomerId;
    }
}

Here is the full code of both ways

var options = new DbContextOptionsBuilder<ApplicationDBContext>()
                  .UseInMemoryDatabase("test")
                  .Options;
var dbcontext = new ApplicationDBContext(options);
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1,PurchaseId = 1,Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1, PurchaseId = 2, Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1, PurchaseId = 3, Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 2, PurchaseId = 2, Cost = 10 });
await dbcontext.SaveChangesAsync();

var filters = new List<Filter>();
filters.Add(new Filter { CustomerId = 1, PurchaseId = 2 });
filters.Add(new Filter { CustomerId = 2, PurchaseId = 2 });

var resq = await dbcontext.CustomerPurchases
    .Where(p=> filters.Contains(new Filter { CustomerId = p.CustomerId,PurchaseId = p.PurchaseId }))
    .ToListAsync();
foreach (var item in resq)
{
    Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}

var res1 = dbcontext.CustomerPurchases
    .Where(p => filters.Select(c=>c.PurchaseId).Contains(p.PurchaseId))
    .Where(p => filters.Select(c => c.CustomerId).Contains(p.CustomerId));

var res = await res1.ToListAsync();
Console.WriteLine("===========================================================");
foreach (var item in res)
{
    Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}

and the running code

Update After changed to SQL server I still got the error so option 2 is not an option when running on SQL server

But I found another solution where I am able to build the where clause based on the list of filters that I have I found this PredicateBuilder and here is the code using predicate builder

var whereclause = PredicateBuilder.False<CustomerPurchase>();
foreach (var filterrow in filters)
{

    whereclause = whereclause.Or(c => c.CustomerId == filterrow.CustomerId && c.PurchaseId == filterrow.PurchaseId);
    
}
var resqq = dbcontext.CustomerPurchases.Where(whereclause);

var resq = await resqq.ToListAsync();
foreach (var item in resq)
{
    Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}

That will build the query which will be translated by sql to the following statement

DECLARE @__filterrow_CustomerId_0 int = 1;
DECLARE @__filterrow_PurchaseId_1 int = 2;
DECLARE @__filterrow_CustomerId_2 int = 2;
DECLARE @__filterrow_PurchaseId_3 int = 2;

SELECT [c].[PurchaseId], [c].[CustomerId]
FROM [dbo].[CustomerPurchase] AS [c]
WHERE (([c].[CustomerId] = @__filterrow_CustomerId_0) AND ([c].[PurchaseId] = @__filterrow_PurchaseId_1)) 
    OR 
    (([c].[CustomerId] = @__filterrow_CustomerId_2) AND ([c].[PurchaseId] = @__filterrow_PurchaseId_3))

Here is the full class of PredicateBuyilder

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>() { return f => true; }
    public static Expression<Func<T, bool>> False<T>() { return f => false; }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
                                                        Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
                                                         Expression<Func<T, bool>> expr2)
    {
        var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
    }
}

I hope that answer your question !!