CODEWITHSUNDEEP
entity-frameworklinq-to-entities
Erik Funkenbusch
Erik Funkenbusch

Reputation: 93464

Querying on a set of parameters

I'm stumped on this one. I've tried everything I can think of, and been thwarted at every turn.

string[] s = {"a", "b", "c"};
string[] s2 = {"1", "2", "3"};
var s3 = s.Zip(s2, (first, second) => new string[] {first, second});

var ctx = db.Data.Where(x => x.SubList.All(y => s3
       .Any(z => y.SubListProp.Name == z[0] && y.Value == z[1])));

Assume the following:

Code first, EF6, Data, SubList, and SubListProp contain only the data you see in the this sample.

Obviously, this example doesn't work, as Array Indexers are not allowed in a SQL Expression. The query works correctly if I add .AsEnumerable() to SubList, but that causes the test to occur client side, not on the sql server. How can I generate a Linq to Entities query that will work with a set of parameterized pairs of data?

Is it possible to modify this query to execute entirely on the sql server? And if so, how?

Upvotes: 1

Views: 45

Answers (1)

Travis J
Travis J

Reputation: 82337

Perhaps you could combine the strings up front, and then project a string in order to use for the comparison like this:

string[] s = {"a", "b", "c"};
string[] s2 = {"1", "2", "3"};
IEnumerable<string> s3 = s.Zip(s2, (first, second) => first+second);//a1,b2,c3

var ctx = db.Data.Where(x => x.SubList
    .All(y => s3.Contains(y.SubListProp.Name + y.Value)));

Upvotes: 1

Related Questions