Nested Lists in LINQ to SQL

Question

I'm trying to get all items that have a data equal to (1 OR 2) AND a data equal to (6 OR 7)

var oneFromEachList = new[] {
    new[] {1,2}, // 1 OR 2
    new[] {6,7}, // AND 6 OR 7
};

// item.dataList is an IEnumerable<int>
DbSet<Table>.Where(item => oneFromEachList.All(list => item.dataList.Any(list.Contains)))

This query is what I want to do, but it can't be converted to SQL, I get: "The nested query is not supported. Operation1='Case' Operation2='Collect'"

Note that the oneFromEachList object is known before the query is executed (it's not from the database). I guess it's possible to build an Expression by composing .Where statements? This seems very complicated for what it is though...

I'm not clear what you're trying to do, but Dynamic LINQ makes it easy to construct complex Where clauses. dynamiclinq.codeplex.com — Todd Sprang
– Todd Sprang, Commented Oct 23, 2015 at 16:32

StriplingWarrior · Accepted Answer · 2015-10-23 16:40:58Z

1

I think this is what you're going for:

var query = DbSet<Table>()
foreach(var list in oneFromEachList)
   query = query.Where(item => item.dataList.Any(list.Contains));

You could turn this into a one-liner by using Aggregate():

oneFromEachList.Aggregate(DbSet<Table>(), (q, l) => q.Where(item => item.dataList.Any(l.Contains)));

But either way you're not going to be able to accomplish what you're looking for without either composing multiple Where clauses or doing some manual expression building.

edited Oct 23, 2015 at 16:40

answered Oct 23, 2015 at 16:35

StriplingWarrior

158k29 gold badges261 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nested Lists in LINQ to SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related