Build Entity Framework query from a list of input value pairs to form OR clauses-CodePudding

I have a list of ListItem objects:

var items = new List<ListItem> 
    { 
        new ListItem{ Code = "X", Type = ItemTypes.Type1},
        new ListItem{ Code = "Y", Type = ItemTypes.Type1},
        new ListItem{ Code = "Z", Type = ItemTypes.Type2},
        new ListItem{ Code = "Z", Type = ItemTypes.Type2},
    };

And I have a table (EF model) of TableItem objects:

public class TableItem
{
    public int Id { get; set; }
    public bool Active { get; set; } = true;
    public ItemTypes Type { get; set; }
    public string Code { get; set; }

    ...
}

My question is how can I query all TableItem records matching the same code/type in the list items using Entity Framework, like

SELECT * 
FROM TableItems 
WHERE 
    (Type = items[0].Type AND Code = items[0].Code AND Active=true) OR
    (Type = items[1].Type AND Code = items[1].Code AND Active=true) OR
...

CodePudding user response：

Use expressions:

Expression<Func<TableItem, bool>> query = de => false;

foreach (var item in items)
{
    Expression<Func<TableItem, bool>> exp = de => de.Code == item.Code && de.Type == item.Type && de.Active == true;
    query = Expression.Lambda<Func<TableItem, bool>>(
        Expression.OrElse(query.Body,
        Expression.Invoke(exp, query.Parameters.Cast<Expression>())), query.Parameters);
}

var res = await applicationDbContext.TableItems.Where(query).ToListAsync();

Predicate Builder. This link has a nice extension that you could use instead of typing the code above.

CodePudding user response：

It should be something like this:

     var results = ctx.TableItems.Where(item => 
                   items.Any(listItem => listItem.Code == item.Code && listItem.Type == item.Type))
                   .ToList();

CodePudding user response：

May be it just help

var listCodes = items.Select(x => x.Code).ToList(); 
var listTypes = items.Select(x => x.Type).ToList();
var result = DbContext.TableItems.Where(x => listCodes.Contains(x.Code) && listType.Contains(x.Type) && x.Active == true);