LINQ - Modeling multiple columns in the IN clause

Question

In oracle, I can make the following request:

SELECT * FROM Tabl Tabb WHERE (tabb.Col1, tabb.Col2) IN ( (1,2), (3,4)) 

Suppose I have the following entity:

 public class Tabb { public int Col1 {get; set; } public int Col2 {get; set; } // other props } 

and criteria class

 public class Search { public int Col1 {get; set; } public int Col2 {get; set; } } 

I need to write:

 public IEnumerable<Tabb> Select(IEnumerable<Search> s) { var queryable = this.context.Tabbs; return queryable.Where(\* some *\).ToList(); } 

How can I select the objects in this search collection contains a search instance with the same value Col1 and Col2 ?

EDIT

 var result = from x in entity join y in entity2 on new { x.field1, x.field2 } equals new { y.field1, y.field2 } 

This does not work (as I expected) - in the case case, entity2 not an entity table, it is a static collection, so EF throws an exception (sth like: cannot find a display layer for type Search []);

Answer 1

Just understood the problem better. You want all rows where the columns match to help:

 myDBTable.Where(x => myStaticCollection.Any(y => y.Col2 == x.Col2) && myStaticCollection.Any(y => y.Col1 == x.Col1)) .ToList() .Select(x => new Search { Col1 = x.Col1, Col2 = x.Col2 }); 

This means that I need every row where any Col2 in my static collection matches this Col2 database Col2 And where any Col1 matches this Col1 database

 this.context.Searches.Join( this.context.Tabbs, s => s.Col2, t => t.Col2, (search, tab) => new { search, tab }); 

This will return an IEnumerable<'a> containing the search and tab

This guy is doing something like LINK

 var result = from x in entity join y in entity2 on new { x.field1, x.field2 } equals new { y.field1, y.field2 } 

Once you have a result , you want to list this to make sure you get into the database and return all your values. Once they are in memory, you can project them into objects.

 result.ToList().Select(a => new MyEntity { MyProperty = a.Property }); 

Answer 2

There are several ways that everyone has pros and cons, and sometimes a little more complicated ...

Solution 1

First you list the ef part (of course, depending on the size of your data, this can be a very bad idea)

Decision 2

You combine your fields with an element that you are sure (hum) that you will not find in your fields, and use Contains on concatenated EF data.

 var joinedCollection =entity2.Select(m => m.field1 + "~" + m.field2); var result = entity.Where(m => joinedCollection.Contains(m.field1 + "~" + m.field2)); 

of course, that would be a little more complicated if field1 and field2 are not strings, you will have to use something like this

 SqlFunctions.StringConvert((double)m.field1) + "~" + //etc. 

Decision 3

you do this in two steps, assuming that you will have a "not too big result" with a partial match (in only one field)

 var field1Collection = joinedCollection.Select(m => m.field1); var result = entity.Where(m => joinedCollection.Contains(m.field1)).ToList(); 

then you make a "full join" in the two lists listed ...

Decision 4

use stored procedure / generated raw sql ...