How to get records in EF that match a list of combinations (key/values)?

Question

I have a database table with records for each user/year combination.

How can I get data from the database using EF and a list of userId/year combinations? Sample combinations:

UserId      Year
1           2015
1           2016 
1           2018
12          2016
12          2019
3           2015
91          1999

I only need the records defined in above combinations. Can't wrap my head around how to write this using EF/Linq?

 List<UserYearCombination> userYears =  GetApprovedYears();

 var records = dbcontext.YearResults.Where(?????);

Classes

public class YearResult
{
    public int UserId;
    public int Year;
    public DateTime CreatedOn;
    public int StatusId;
    public double Production;
    public double Area;
    public double Fte;
    public double Revenue;
    public double Diesel;
    public double EmissionsCo2;
    public double EmissionInTonsN;
    public double EmissionInTonsP;
    public double EmissionInTonsA;
        ....
}

public class UserYearCombination
{
    public int UserId;
    public int Year;
}

Can you post `UserYearCombination` and `YeaResults` classes ? — Shahid Manzoor Bhat, Jan 03 '20 at 15:38
Does this answer your question? [Match 2 property of any object in local list using Linq](https://stackoverflow.com/questions/59548495/match-2-property-of-any-object-in-local-list-using-linq) — David Edel, Jan 03 '20 at 16:04

Gert Arnold · Accepted Answer · 2020-01-04T15:46:19.380

This is a notorious problem that I discussed before here. Krishna Muppalla's solution is among the solutions I came up with there. Its disadvantage is that it's not sargable, i.e. it can't benefit from any indexes on the involved database fields.

In the meantime I coined another solution that may be helpful in some circumstances. Basically it groups the input data by one of the fields and then finds and unions database data by grouping key and a Contains query of group elements:

IQueryable<YearResult> items = null;

foreach (var yearUserIds in userYears.GroupBy(t => t.Year, t => t.UserId))
{
    var userIds = yearUserIds.ToList();
    var grp = dbcontext.YearResults
        .Where(x => x.Year == yearUserIds.Key 
                 && userIds.Contains(x.UserId));
    items = items == null ? grp : items.Concat(grp);
}

I use Concat here because Union will waste time making results distinct and in EF6 Concat will generate SQL with chained UNION statements while Union generates nested UNION statements and the maximum nesting level may be hit.

This query may perform well enough when indexes are in place. In theory, the maximum number of UNIONs in a SQL statement is unlimited, but the number of items in an IN clause (that Contains translates to) should not exceed a couple of thousands. That means that the content of your data will determine which grouping field performs better, Year or UserId. The challenge is to minimize the number of UNIONs while keeping the number of items in all IN clauses below approx. 5000.

Krishna Varma · Answer 2 · 2020-01-03T16:25:06.533

you can try this

//add the possible filters to LIST
var searchIds = new List<string> { "1-2015", "1-2016", "2-2018" };

//use the list to check in Where clause
var result = (from x in YearResults
        where searchIds.Contains(x.UserId.ToString()+'-'+x.Year.ToString())
        select new UserYearCombination
        {
            UserId = x.UserId,
            Year = x.Year
        }).ToList();

Method 2

var d = YearResults
           .Where(x=>searchIds.Contains(x.UserId.ToString() + '-' + x.Year.ToString()))
           .Select(x => new UserYearCombination
                 {
                      UserId = x.UserId,
                      Year = x.Year
                 }).ToList();

@KrishnaMuppalla Can you confirm that your approaches do not trigger the client-side evaluation? — ilkerkaran, Jan 03 '20 at 16:32

score 0 · Answer 3 · answered May 03 '23 at 18:21

I have been working on a solution. please notice that this is based on my limited knowledge of C# Expressions, if you have any suggestions improvements i will appreciate it.

public static class EfExtensions
{
    public static IQueryable<T> WhereCompoundIn<T, TKey>(this IQueryable<T> source, IEnumerable<TKey> keys,
        Expression<Func<T, TKey>> keySelectorExpression)
    {
        var keyExpressions = GetPropertyExpressions(keySelectorExpression)
            .ToDictionary(x => x.Member.Name);

        // get the properties and build a selector expression for each property
        var propertyKeySelectors = typeof(TKey)
            .GetProperties()
            .Select(propertyInfo =>
            {
                var parameter = Expression.Parameter(typeof(TKey));
                var property = Expression.Property(parameter, propertyInfo);
                var conversion = Expression.Convert(property, typeof(object));
                return new
                {
                    PropertyName = propertyInfo.Name,
                    ValueSelector = Expression.Lambda<Func<TKey, object>>(conversion, parameter).Compile()
                };
            });

        var predicate = keys
            .Select(compoundKey =>
                {
                    var andExpressions = propertyKeySelectors
                        .Select(key =>
                        {
                            var keyValue = key.ValueSelector(compoundKey);
                            var propertySelectorExpression = keyExpressions[key.PropertyName];
                            // T.Property == keyValue
                            return Expression.Equal(propertySelectorExpression, Expression.Constant(keyValue));
                        })
                        // T.Property1 == keyValue1 && T.Property2 == keyValue2 && ...
                        .Aggregate(Expression.AndAlso);
                    return andExpressions;
                }
            )
            // T.Property1 == keyValue1 && T.Property2 == keyValue2 && ... || T.Property1 == keyValue1 && T.Property2 == keyValue2 && ...
            .Aggregate(Expression.OrElse);
        return source.Where(Expression.Lambda<Func<T, bool>>(predicate, keySelectorExpression.Parameters));
    }

    private static IEnumerable<MemberExpression> GetPropertyExpressions<T, TResult>(
        this Expression<Func<T, TResult>> expression)
    {
        if (expression.Body is not NewExpression newExpression)
            throw new ArgumentException("Expression must be a NewExpression", nameof(expression));

        foreach (var argumentExpression in newExpression.Arguments)
        {
            if (argumentExpression is not MemberExpression { Expression: not null } memberExpression) continue;
            var memberName = memberExpression.Member.Name;
            yield return Expression.Property(memberExpression.Expression, memberName);
        }
    }
}

Which can be used as follows:

var compoundKeys =
    "2480209000000469302,2480209000000469347,2480209000000469374,2480209000000470068"
        .Split(',')
        .Select(productId => new { ProductId = productId, StoreId = "MGA_SUR" })
        .ToArray();

var productStocks = context.ProductStocks
    .Where(x => x.BusinessId == "ZUPER")
    .WhereCompoundIn(compoundKeys, x => new { x.ProductId, x.StoreId })
    .ToArray();

The query above generates the following SQL code:

SELECT `p`.`business_id`,
       `p`.`store_id`,
       `p`.`product_id`,
       `p`.`created_by`,
       `p`.`created_on`,
       `p`.`is_active`,
       `p`.`last_updated_by`,
       `p`.`last_updated_on`,
       `p`.`min_stock`,
       `p`.`purchase_price`,
       `p`.`sales_category`,
       `p`.`sales_price`,
       `p`.`stock`
FROM `product_stocks` AS `p`
WHERE (`p`.`business_id` = 'ZUPER')
  AND (((((`p`.`product_id` = '2480209000000469302') AND (`p`.`store_id` = 'MGA_SUR')) OR
         ((`p`.`product_id` = '2480209000000469347') AND (`p`.`store_id` = 'MGA_SUR'))) OR
        ((`p`.`product_id` = '2480209000000469374') AND (`p`.`store_id` = 'MGA_SUR'))) OR
       ((`p`.`product_id` = '2480209000000470068') AND (`p`.`store_id` = 'MGA_SUR')))

How to get records in EF that match a list of combinations (key/values)?

3 Answers3

Linked