LINQ to SQL query against a list of entities

Question

Ingredient class:

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

List of Ingredients:

var ingredientsList = new List<Ingredient>();

Database layout of my "Ingredients" table:

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

Am I able to query my ingredientsList against my "Ingredients" table, doing a where-clause which goes something like this (pseudo code alert!):

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

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

Answer 1

I think you'll either have to use multiple queries, or copy your ingredients list into a temporary table and do the database query in that way.

I mean, you could have a SQL statement of:

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

but it get ugly pretty quickly.

Personally I suspect that the temporary table solution is going to be the neatest - but I don't know whether LINQ to SQL has much support for them.

Answer 2

The only extent to which you can use a local collection in a LINQ 2 SQL query is with the Contains() function, which is basically a translation to the SQL in clause. For example...

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);

This would generate SQL equivalent to "...where ingredients.Name in (...)"

Unfortunately I don't think that's going to work for you, as you'd have to join each column atomically.

And just as an aside, using LINQ 2 SQL is a dynamically generated SQL query.

You could, of course, do the joining on the client side, but that would require bringing back the entire Ingredients table, which could be performance-prohibitive, and is definitely bad practice.

Answer 3

LINQ is composable, but to do this without using UNION you'd have to roll your own Expression. Basically, we (presumably) want to create TSQL of the form:

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

where the name/amount pairs are determined at runtime. There is easy way of phrasing that in LINQ; if it was "AND" each time, we could use .Where(...) repeatedly. Union is a candidate, but I've seen repeated people have problems with that. What we want to do is emulate us writing a LINQ query like:

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

This is done by crafting an Expression, using Expression.OrElse to combine each - so we will need to iterate over our name/amount pairs, making a richer Expression.

Writing Expression code by hand is a bit of a black art, but I have a very similar example up my sleeve (from a presentation I give); it uses some custom extension methods; usage via:

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

where args is your array of test ingredients. What this does is: for each localIngredient in args (our local array of test ingredients), it asks us to provide an Expression (for that localIngredient) that is the test to apply at the database. It then combines these (in turn) with 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 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

I was messing around with this solution in LINQPad, if you have it, you can see the dump outputs. Not sure if it is what you need, but from what I understand it is. I used it against my Users table, but you could replaced that for Ingredients and "UserList" for "IngredientList" and "Username" for "Ingredient Name". You can add further "OR" filtering expressions inside the if statement. It is important you set an ID though.

So final note the "Dump()" method is specific to LINQPad and is not required.

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();