Linq - Contains in a select statement RSS

• • Reply
  

  milasch

  Member

  3 Points

  28 Posts

  Linq - Contains in a select statement

  Jul 21, 2010 05:43 PM|LINK

  Hello,

  I've noticed that a statement like:

  var products = (from p in context.Products
                        where productIds.Contains(p.ProductId)
                        select p).ToList();

  
  where productIds is a List<int> and has a number of elements generates a SQL statement similar to:

  select * from Products p
  
  where ProductID IN (a, b, c, d, e) 
  

  
  where a, b, c, d and e are all the integer elements of productIds list.

  However I want to retrieve the Product objects from the database in the same order as the elements were in the productIds.

  I've chosen to do a very inelegant loop:

  for (int i = 0; i < products.Count; i++)
  {
      var product = products.FirstOrDefault(p => p.ProductId == productIds[i]);
      products.Remove(product);
      products.Insert(i, product);
  }

  
  It works but I'm not happy with it.

  
  

  I'm using Entity Framework and .net 4.0.

  How would you do it?

  Entity Framework ASP.NET 2010 Framework 4.0 Linq sorting

• • Reply
  

  sansan

  All-Star

  53942 Points

  8147 Posts

  Re: Linq - Contains in a select statement

  Jul 22, 2010 12:51 AM|LINK

  A quick solution will be ordering both the lists by same field.

  Order the ProductsIds list by Product ID and

  order your filtered list also by the ProductID

  var products = (from p in context.Products
                        where productIds.Contains(p.ProductId)
                        select p).ToList().OrderBy(a=>a.ProductId).ToList()

  
  and order the productIds also as

  var productIds = new[] { new { ProductId = 18 }, 
                                       new { ProductId = 4 }, 
                                       new { ProductId = 7 }, 
                                       new { ProductId = 12 } }.ToList().OrderBy(a=>a.ProductId);

  
  does that make sense??

  Santhosh
• • Reply
  

  milasch

  Member

  3 Points

  28 Posts

  Re: Linq - Contains in a select statement

  Jul 22, 2010 01:25 PM|LINK

  sansan

  A quick solution will be ordering both the lists by same field.

  Order the ProductsIds list by Product ID and

  order your filtered list also by the ProductID

  
  

  <div class="dp-highlighter"><div class="bar"><div class="tools">view plaincopy to clipboardprint?</div></div>

  1. var products = (from p in context.Products  
  2.                       where productIds.Contains(p.ProductId)  
  3.                       select p).ToList().OrderBy(a=>a.ProductId).ToList()  

  </div>

  var products = (from p in context.Products
                        where productIds.Contains(p.ProductId)
                        select p).ToList().OrderBy(a=>a.ProductId).ToList()

  
  

  
  and order the productIds also as

  
  

  <div class="dp-highlighter"><div class="bar"><div class="tools">view plaincopy to clipboardprint?</div></div>

  1. var productIds = new[] { new { ProductId = 18 },   
  2.                                      new { ProductId = 4 },   
  3.                                      new { ProductId = 7 },   
  4.                                      new { ProductId = 12 } }.ToList().OrderBy(a=>a.ProductId);  

  </div>

  var productIds = new[] { new { ProductId = 18 }, 
                                       new { ProductId = 4 }, 
                                       new { ProductId = 7 }, 
                                       new { ProductId = 12 } }.ToList().OrderBy(a=>a.ProductId);

  
  does that make sense??

  
  

  
  

  Well, basically I want to keep the order I originally had. So if I had a list:

  var products = from p in context.Products
                       where productIds.Contains(p.ProductId)
                       select p;

  
  products would be returned in the order which they have been inserted in the database, and if ProductId is an auto inc primary key I assume var products would have elements in the order { productId == 4, productId == 7, productId == 12, productId == 18 }, and not the original order in productIds list.

  So, anyone know how to do this instead of manually ordering them in a loop as in my first post (which i think is inelegant)?

  
  

• • Reply
  

  PeteNet

  All-Star

  81342 Points

  11398 Posts

  Re: Linq - Contains in a select statement

  Jul 22, 2010 02:06 PM|LINK

  I think what you have is good enough.

  Ideally, you could 'introduce' the order you want and then sort them with the custom order (meaning have a property in products that can be set to a specific order) ..but otherwise I don't think there's anything inbuilt to custom order.

  Or fetch them, in order, with predicates (reduces to fetch for one item in the list at a time) and that might not be as good as re-ordering an already fetched sequence.

  Regards,
  Peter
  • Marked as answer by Hong-Gang Chen - MSFT on Jul 28, 2010 11:21 AM
• • Reply
  

  DonMaxim

  Member

  2 Points

  1 Post

  Re: Linq - Contains in a select statement

  Feb 05, 2013 01:25 PM|LINK

  I'm understand that my answer is a little late, but still... that's works for me:

  var orderedList = productIds.Join(context.Products,
  					id => id,
  					prod => prod.ProductId,
  					(id, prod) => prod);