0

I would like to produce a query that has multiple OR statements from my memory side list. The query is something like:

SELECT * FROM table WHERE (name = 'John' AND location = 'San Francisco') OR (name = 'Mike' AND location = 'Los Angeles') ... so on

The parentheses are the items of the memory side list. Here is a sample of what I tried but there is an error caused by comparing sql-side data against memory-side data. I would like to create the query before executing it since this is a very large table.

List<Tuple<string, string>> nameLocationList = new List<Tuple<string, string>>();
nameLocationList.Add(new Tupple<string, string> ("John", "San Francisco"));
nameLocationList.Add(new Tupple<string, string> ("Mike", "Los Angeles"));

var data = context.table.Where(t => nameLocationList.Contains(new Tuple<string, string> (t.name, t.location))).toList();

But I got an error of:

{"Unable to create a constant value of type 'System.Tuple`2[[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only primitive types or enumeration types are supported in this context."}
Samuel Marvin Aguilos
  • 165
  • 3
  • 11
  • 1
    I don't know *how* you do this is the language you are using, but for such queries you are better off using a table type parameter and `JOIN`ing to it. All those `OR` statements are unlikely to be performant. – Thom A Aug 17 '21 at 13:03
  • I already tried using JOIN but it still had an error on joining sql-side and memory-side data. – Samuel Marvin Aguilos Aug 17 '21 at 13:06
  • Does this answer your question? [Entity Framework Stored Procedure Table Value Parameter](https://stackoverflow.com/questions/8157345/entity-framework-stored-procedure-table-value-parameter) Of the solutions there, passing a `DataTable` is probably the easiest – Charlieface Aug 17 '21 at 13:27

0 Answers0