LINQ to SQL: Advanced queries against lists, arrays and lists of objects
Solution 1
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
Solution 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)
)
Solution 3
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 forIList<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)
)
Solution 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.
cllpse
Designing and developing websites and app's for close to two decades.
Updated on June 05, 2022Comments
-
cllpse almost 2 years
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?
ArraysConsider 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 objectsConsider 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 ofIdentities
objects?
This is branch off of LINQ to SQL query against a list of entities