LINQ to SQL query against a list of entities

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.

From stackoverflow

• 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.

  roosteronacid : Selecting all entries in a table sounds like a pretty bad choice, performance-wise. Also; I'm aiming at using LINQ to SQL for this. Not dynamically generated SQL queries :)

  Adam Robinson : @rooster, like I said in my answer, LINQ 2 SQL is a dynamically-generated SQL query.

• 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.

  roosteronacid : Well, if I'm understanding this right: this is actually an optimization. I could split this up in two parts: query ingredients against my collections names, then check if the amount of the ingredients is what I want it to be (in memory). Or am I way off? :)

  Adam Robinson : You could do that, yes. I'm not entirely certain of what you're aiming at with this query, but from a technical perspective you could do that. In fact, if validation is what you're after then that would likely be more effective than the query you descrive in the question.

  Adam Robinson : That was supposed to be "describe" for the English speakers in our audience.

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

  roosteronacid : 20 minutes spent looking at your answer--trying to decipher your code. Still non-the-wiser. Could I talk you into trying to be a bit more explicit? Adding a few comments, explaining what your code does, perhaps?

  Marc Gravell : Will do - sorry, was in a hurry...

  roosteronacid : Marc, you are making my head hurt!.. Not that I don't appreciate your efforts in educating me.. But damn!.. Could you spell it out for me - so to say - using my exact code combined with your WhereTrueForAny() method? :)

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

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