LINQ to SQL: Advanced queries against lists, arrays and lists of objects

Question

Single and multiple lists

Consider the following lists:

List<Int32> appleIdentities = new List<int>(new[] { 1, 2, 3 });
List<Int32> chocolateIdentities = new List<int>(new[] { 2, 3, 4 });
List<Int32> icecreamIdentities = new List<int>(new[] { 11, 14, 15, 16 });

Using LINQ to SQL; is it possible to wite a statement which translates into:

SELECT
    DesertsID,
    DesertsName
FROM
    Deserts
WHERE
    Deserts.AppleIdentity IN (1, 2, 3) AND
    Deserts.ChocolateIdentity IN (2, 3, 4) AND
    Deserts.IcecreamIdentity IN (11, 14, 15m 16)

If yes; how would the code look if I wanted to query my database of deserts against just the appleIdentities list?



Arrays

Consider the following arrays:

Int32[] appleIdentities = new[] {1, 2, 3, 4};
String[] chocolateNames = new[] {"Light", "Dark"};

Using LINQ to SQL; is it possible to wite a statement which translates into:

SELECT
    DesertsID,
    DesertsName
FROM
    Deserts
WHERE
    Deserts.AppleIdentity IN (1, 2, 3) AND
    Deserts.ChocolateName IN ('Light', 'Dark')

If yes; how would the code look if I wanted to query my database of deserts against just the appleIdentities array?



List of objects

Consider the following:

public class Identities
{
    public Int32 appleIdentity { get; set; }
    public String chokolateName { get; set; }
}

List<Identities> identities = new List<Identities>(new[] {
    new Identities { appleIdentity = 1, chokolateName = "Light" },
    new Identities { appleIdentity = 2, chokolateName = "Dark" },
});

Using LINQ to SQL; is it possible to wite a statement which translates into:

SELECT
    DesertsID,
    DesertsName
FROM
    Deserts
WHERE
    Deserts.AppleIdentity IN (1, 2) AND
    Deserts.ChocolateName IN ('Light', 'Dark')

If yes; how would the code look if I wanted to query my database of deserts against just the appleIdentity-property on my list of Identities objects?


This is branch off of LINQ to SQL query against a list of entities

Answer 1

As others have said, LinqToSql will translate Contains to IN.

There's some caveats:

• this translation works for List<T>.Contains(), but doesn't work for IList<T>.Contains(). Does it work for arrays? I don't know.
• This translation will happily translate as many elements as you like - each element becomes a sql parameter. SQL Server 2008 has an approx 2000 parameter limit and will throw sql exceptions at you if you try this with a collection that is too big.
• This translation, when applied to a collection of strings, will produce nvarchar parameters. This could be a serious problem if the target column is varchar and you want to use the index on this column. Sql Server will convert the index, instead of the parameters... which involves reading and converting every string in the whole index.

Here's some code for your List of Objects question:

List<int> someIDs = identities
   .Select(x => x.appleIdentity).ToList();
List<string> someStrings = identities
   .Select(x => x.chokolateName).ToList();

var query = db.Desserts.Where(d =>
  someIDs.Contains(d.AppleIdentity) &&
  someStrings.Contains(d.ChocolateName)
  )

Answer 2

how would the code look if I wanted to query my database of deserts against just the appleIdentities list?

You can compose a linq query in multiple statements, like so, and select at runtime which filters your want to use in your where clause.

var query = db.Desserts;
if (filterbyAppleIdentity)
    query = query.Where( q => appleIdentities.Contains(q.DesertsID));
if (filterbyChocolateIdentities)
    query = query.Where( q => chocolateIdentities.Contains(q.DesertsID));
if (filterbicecreamIdentities)
    query = query.Where( q => icecreamIdentities.Contains(q.DesertsID));

var deserts = query.ToList();

you can also write an extension method to do this without if statements: (Edit fixed typo, return type should be IQueriable

public static class LinqExtensions {
  public IQueriable<T> CondWhere<T>(this IQueriable<T> query, bool condition, Expression<Func<T,bool>> predicate) {
     if (condition)
        return query.Where(predicate);
     else 
        return query;
  }
 }

and write your linq query like this:

  var deserts = db.Desserts;
      .CondWhere(filterbyAppleIdentity, q => appleIdentities.Contains(q.DesertsID));
      .CondWhere(filterbyChocolateIdentities, q => chocolateIdentities.Contains(q.DesertsID));
      .CondWhere(filterbicecreamIdentities, q => icecreamIdentities.Contains(q.DesertsID)).ToList();

Another way to do it is to union the id lists:

var deserts = db.Deserts
        .Where( d => appleIdentities.Union(chocolateIdentities).Union(icecreamIdentities).Contains(d.DesertsID);

For a list of objects you can use .Select extension method to project your list into a int or string IEnumerable and you can use contains in the query in the same way:

var deserts = db.Deserts
    .Where(d => 
        identities.Select(i => i.appleIdentity).Contains(d => d.DesertID) &&
        identities.Select(i => i.chokolateName).Contains(d => d.DesertsName)
     )

Answer 3

Sure - just use Contains - using Northwind as an example:

var qry = from cust in ctx.Customers
          where custIds.Contains(cust.CustomerID)
             && regions.Contains(cust.Region)
          select cust; // or your custom projection

Answer 4

Well, you can try:

var query = from dessert in db.Desserts
            where appleIdentities.Contains(dessert.AppleIdentity)
               && chocolateIdentities.Contains(dessert.ChocolateIdentity)
               && iceCreamIdentities.Contains(dessert.IceCreamIdentity)
            select new { dessert.Id, dessert.Name };

I believe that's okay, although it'll fail when the lists get big enough IIRC. That should be okay for lists and arrays.

I'm not sure about your third query though - I think you'd need a list for each of the separate Contains calls.