LINQ to SQL query against list of objects

Question

Ingredient Class:

class Ingredient
{
    public String Name { get; set; }
    public Double Amount { get; set; }
}

List of ingredients:

var ingredientsList = new List<Ingredient>();

The database structure of my table "Ingredients":

[Ingredients] (
    [IngredientsID] [int] IDENTITY(1,1) NOT NULL,
    [RecipeID] [int] NOT NULL,
    [IngredientsName] [nvarchar](512) NOT NULL,
    [IngredientsAmount] [float] NOT NULL
)

Can I query mine ingredientsListfor my Ingredients table by making a where clause that looks something like this (pseudo-code warning!):

SELECT * FROM Ingredients WHERE
IngredientsName = ["Name" property on entities in my ingredientsList] AND
IngredientsAmount <= ["Amount" property on entities in my ingredientsList]

Of course, I want this to be done with LINQ, and not using dynamically generated SQL queries.

Answer 1

LINQ can be built, but for this, without using UNION, you have to flip your own Expression. Basically, we (presumably) want to create TSQL forms:

SELECT *
FROM   [table]
WHERE  (Name = @name1 AND Amount <= @amount1)
OR     (Name = @name2 AND Amount <= @amount2)
OR     (Name = @name3 AND Amount <= @amount3)
...

/ . LINQ ; "" , .Where(...) . Union , , . , LINQ, :

var qry = from i in db.Ingredients
          where (  (i.Name == name1 && i.Amount <= amount1)
                || (i.Name == name2 && i.Amount <= amount2)
                ... )
          select i;

Expression, Expression.OrElse - /, Expression.

Expression - , ( , ); ; :

    IQueryable query = db.Ingredients.WhereTrueForAny(
        localIngredient => dbIngredient =>
                   dbIngredient.Name == localIngredient.Name
                && dbIngredient.Amount <= localIngredient.Amount
            , args);

args - . : localIngredient args ( ) Expression ( localIngredient), , . ( ) Expression.OrElse:

---

public static IQueryable<TSource> WhereTrueForAny<TSource, TValue>(
    this IQueryable<TSource> source,
    Func<TValue, Expression<Func<TSource, bool>>> selector,
    params TValue[] values)
{
    return source.Where(BuildTrueForAny(selector, values));
}
public static Expression<Func<TSource, bool>> BuildTrueForAny<TSource, TValue>(
    Func<TValue, Expression<Func<TSource, bool>>> selector,
    params TValue[] values)
{
    if (selector == null) throw new ArgumentNullException("selector");
    if (values == null) throw new ArgumentNullException("values");
    // if there are no filters, return nothing
    if (values.Length == 0) return x => false;
    // if there is 1 filter, use it directly
    if (values.Length == 1) return selector(values[0]);

    var param = Expression.Parameter(typeof(TSource), "x");
    // start with the first filter
    Expression body = Expression.Invoke(selector(values[0]), param);
    for (int i = 1; i < values.Length; i++)
    { // for 2nd, 3rd, etc - use OrElse for that filter
        body = Expression.OrElse(body,
            Expression.Invoke(selector(values[i]), param));
    }
    return Expression.Lambda<Func<TSource, bool>>(body, param);
}

Answer 2

, SQL LINQ 2, - Contains(), SQL in. ...

var ingredientsList = new List<Ingredient>();

... add your ingredients

var myQuery = (from ingredient in context.Ingredients where ingredientsList.Select(i => i.Name).Contains(ingredient.Name) select ingredient);

SQL- "...where ingredients.Name in (...)"

, , , .

, , LINQ 2 SQL - SQL-.

, , , Ingredients, , .

Answer 3

, , .

, SQL:

SELECT * FROM Ingredients WHERE
(IngredientsName = 'Flour' AND IngredientsAmount < 10) OR   
(IngredientsName = 'Water' AND IngredientsAmount <= 5) OR
(IngredientsName = 'Eggs' AND IngredientsAmount <= 20)

.

, , , LINQ to SQL .

Answer 4

List<string> ingredientNames = ingredientsList
  .Select( i => i.Name).ToList();
Dictionary<string, Double> ingredientValues = ingredientsList
  .ToDictionary(i => i.Name, i => i.Amount);
//database hit
List<Ingredient> queryResults = db.Ingredients
  .Where(i => ingredientNames.Contains(i.Name))
  .ToList();
//continue filtering locally - TODO: handle case-sensitivity
List<Ingredient> filteredResults = queryResults
  .Where(i => i.Amount <= ingredientValues[i.Name])
  .ToList();

Answer 5

LINQPad, , . , , , , . Users, "" "UserList" "IngredientList" "Username" "Ingredient Name". "" if. , .

, : "Dump()" LINQPad .

var userList = new List<User>();
userList.Add(new User() { ID = 1, Username = "goneale" });
userList.Add(new User() { ID = 2, Username = "Test" });

List<int> IDs = new List<int>();
//                       vv ingredients from db context
IQueryable<User> users = Users;
foreach(var user in userList)
{
    if (users.Any(x => x.Username == user.Username))
        IDs.Add(user.ID);
}
IDs.Dump();
userList.Dump();
users.Dump();
users = users.Where(x => IDs.Contains(x.ID));
users.Dump();

Answer 6

Union :

 public static IQueryable<TSource> WhereTrueForAny<TSource, TValue>(this IQueryable<TSource> source, Func<TValue, Expression<Func<TSource, bool>>> selector, params TValue[] values)
    {
        // code is based on Marc Gravells answer
        if (selector == null) throw new ArgumentNullException("selector");
        if (values == null) throw new ArgumentNullException("values");

        // if there are no filters, return nothing
        if (values.Length == 0) return source.Where(x => false);
        // if there is 1 filter, use it directly
        if (values.Length == 1) return source.Where(selector(values[0]));

        var lockingUpArray = values;

        var p = lockingUpArray.First();

        IQueryable<TSource> query = source.Where(selector(p));

        foreach (var param in lockingUpArray.Skip(1))
        {
            query = query.Union(source.Where(selector(param)));
        }

        return query;
    }