0

I have the following SQL query. How can I create the same query in LINQ?

select * from table1 t1
join table2 t2
on t1.col1 = t2.col1
where t2.col2 in ('A','B','C' )
Matt
  • 4,656
  • 1
  • 22
  • 32
Payal
  • 73
  • 2
  • 13

2 Answers2

1

You can use Contains :

string[] stringCollection = { "A", "B", "C" };
var query = from t1 in db.Table1
            join t2 in db.Table2
            on t1.col1 equals t2.col1
            where stringCollection.Contains(t2.col2)
            select new { t1, t2 };
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • I tried this, but get this error: Invalid 'where' condition. An entity member is invoking an invalid property or method. – Payal Nov 20 '14 at 22:20
  • @Payal: maybe [this](http://stackoverflow.com/questions/12200034/trying-to-execute-a-where-in-invalid-where-condition-an-entity-member-is-inv) or [this](http://stackoverflow.com/questions/8881302/equivalent-to-sql-in-clause) helps. – Tim Schmelter Nov 20 '14 at 22:25
  • This is how i have my current LINQ query: (from t1 in table1 join t2 in table2 on t1.col1 equals t2.col1 where t1.col2 == someStatusfield select new { A = t1, B = t2.col2 }).Where(r => r.B.Trim() == variable).ToList(); instead of .Where(r => r.B.Trim() == variable), I need to use SQL IN keyword with a list of string. – Payal Nov 20 '14 at 22:28
  • Thank you, so Contains will not work. How will it work with the above linq query that I added? – Payal Nov 20 '14 at 22:35
  • @Payal: sorry, i'm not familiar with [MS Dynamics CRM](http://msdn.microsoft.com/en-us/library/ff681573.aspx). Maybe the links i have provided are helpful. All providers i've used so far support `Contains`. – Tim Schmelter Nov 20 '14 at 22:42
0

You can't query in LinQ - Crm with 'IN'.

What you can do is retrieve the records, after you have a List<Entityt> you use LinQ or a foreach to filter the records you need.

Sxntk
  • 845
  • 14
  • 29