LINQ - Simulating multiple columns in IN clausule

Question

In oracle I can do the following query:

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

Consider I 've 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 entities, that search collection contain instance of search that has the same value of 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 }

It doesn't work (As I expected) - in may case entity2 is not a entity table, it is static collection, so EF throws exception (sth like: cannot find mapping layer to type Search[]);

Answer 1

Just understood the problem better. You want all rows where the columns match, may be this will 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 is saying, I want each row where any Col2 in my static collection matches this database Col2 AND where any Col1 matches this database Col1

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

This will bring back IEnumerable<'a> containing a search and a tab

This guy is doing something similar 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 your result then you want to enumerate that to make sure you're hitting the database and getting all your values back. Once they're in memory, then you can project them into objects.

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

Answer 2

There's a few ways, which all have pros and cons, and are sometimes a little bit tricky...

Solution 1

You enumerate the ef part first (of course, depending on the size of your data, this might be a very bad idea)

Solution 2

You concatenate your fields with an element you're sure (hum) you won't find in your fields, and use a 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, this would be a little bit more complicated if field1 and field2 are not string, you'll have to use something like that

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

Solution 3

you do this in two step, assuming you will have "not too much result" with a partial match (on only one field)

var field1Collection = joinedCollection.Select(m => m.field1);

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

then you make the "complete join" on the two enumerated lists...

Solution 4

use a stored procedure / generated raw sql...