Filter query by multiple parameters from the list of objects

Question

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.

You can use this extension FilterByItems to generate desired filter. — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Mar 24, 2022 at 17:06
If you don't expect a lot of overlap, you could filter separately by CustomerId and PurchaseId then switch to client side (AsEnumerable) and filter again by the pairs. — NetMage
– NetMage, Commented Mar 24, 2022 at 19:38

khaled Dehia · Accepted Answer · 2022-03-25 19:40:51Z

3

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 !!

edited Mar 25, 2022 at 19:40

answered Mar 25, 2022 at 18:04

khaled Dehia

9657 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

NetMage Over a year ago

At least in EF Core, your second method won't work: EF can't translate IEquatable or Filter to SQL. And your first method is incomplete - you will return undesired results.

khaled Dehia Over a year ago

@NetMage I am using EF core in memory, and it works fine , I agree with you the first option might return undesired data but the second option is working for me , Maybe I should try in on SQL and update

NetMage Over a year ago

When I test in SQL I get could not be translated.

Hirasawa Yui Over a year ago

Thanks, man! Sadly, there's no way the 2nd method will be translated to SQL. The 1st one looks fine, but it might result in getting some extra data. It's still better than filtering only by 1 parameter, though. I guess I'll have to get my hands dirty and write the SQL query manually after all.

khaled Dehia Over a year ago

@HirasawaYui thanks for the feedback I will try to find work around for the SQL , good luck

|

Collectives™ on Stack Overflow

Filter query by multiple parameters from the list of objects

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related