How to translate SQL "WHERE expr IN (query)" to LINQ?

Question

Basically, I want to make this SQL query using linq:

SELECT * FROM Orders WHERE Identifier IN (SELECT DISTINCT [Order] FROM OrderRows WHERE Quantity = '1') 

Here is what I came up with:

 var q = from o in db.Orders where o.Identifier in (from r in db.OrderRows where r.Quantity == 1 select r.Order).Distinct()); 

But in after o.Identifier is not valid.

What is the correct syntax for the IN keyword?

Answer 1

I'm a little late, but I did a demo!

As other people have stated, I always use Contains:

 using System; using System.Collections.Generic; using System.Linq; namespace ContainsExample { class Program { static void Main(string[] args) { var foos = new List<Foo> { new Foo { ID = 1, FooName = "Light Side" }, new Foo { ID = 2, FooName = "Dark Side" } }; var bars = new List<Bar> { new Bar { ID = 1, BarName = "Luke", FooID = 1 }, new Bar { ID = 2, BarName = "Han", FooID = 1 }, new Bar { ID = 3, BarName = "Obi-Wan", FooID = 1 }, new Bar { ID = 4, BarName = "Vader", FooID = 2 }, new Bar { ID = 5, BarName = "Palpatine", FooID = 2 }, new Bar { ID = 6, BarName = "Fett", FooID = 2 }, new Bar { ID = 7, BarName = "JarJar", FooID = 3 } }; var criteria = from f in foos select f.ID; var query = from b in bars where criteria.Contains(b.FooID) select b; foreach (Bar b in query) { Console.WriteLine(b.BarName); } Console.WriteLine(); Console.WriteLine("There should be no JarJar..."); Console.ReadLine(); } } public class Foo { public int ID { get; set; } public string FooName { get; set; } } public class Bar { public int ID { get; set; } public string BarName { get; set; } public int FooID { get; set; } } } 

Answer 2

  from o in db.Orders where o.Identifier.Any ( from r in db.OrderRows where r.Quantity == 1 select r.Order ).Distinct() select o 

Try it...

Answer 3

It seems that you want to join:

 var q = (from o in db.Orders join r in db.OrderRows on o.Identifier equals r.Order where r.Quantity == 1 select o).Distinct(); 

Answer 4

You tried to use something like this:

 int[] inKeyword = { 5, 7, 9 }; var q = from o in db.Orders.Where(p => inKeyword.Contains(p.Identifier)); 

Hope this helps :)

Answer 5

 var q = from o in db.Orders where (from r in db.OrderRows where r.Quantity == 1 select r.Order).Distinct().Contains(o.Identifier); 

Answer 6

The short answer is that you want to use the Contains method.

 int[] ids = { 2, 5, 6, 1 }; var a = from myRecords in context.db where ids.Contains (myRecords.id) select new {Id = myRecords.id}; 

Filtering by two result sets works the same way, since you can filter any common property shared by two sets:

 string[] cities = { "London", "Paris", "Seattle" }; var query = dataContext.Customers.Where (c => cities.Contains (c.City));