none
dynamic multiple contains-query OR converting a collection of linq queries into a single query RRS feed

  • Question

  • Hi all,

    Let's assume i have the following table:

    CustomerID - ProductID
    1 - 10
    1 - 11
    1 - 12
    2- 10
    3- 11

    Now I want exactly the customer who ordered the Product 10 AND 11 AND 12, this would be 1 in this case.

    Unfortunately the amount of parameters is not fixed!

    I guess one solution would be if the customer exists in the intersected (but already executed) queries, but hope there is a nicer solution.

    thx!
    Friday, March 27, 2009 2:04 AM

Answers

  • You can use the same idea that Matt Warren laid out here.

    List<IQueryable<Temp>> temps = new List<IQueryable<Temp>>();
    
    temps.Add(db.Temps.Where(t => t.ProductId == 10));
    temps.Add(db.Temps.Where(t => t.ProductId == 11));
    temps.Add(db.Temps.Where(t => t.ProductId == 12));
    
    var query = temps.Aggregate((t1, t2) => t1.Intersect(t2));
    
    Only one query will be sent to the server here, when you iterate the results in the "query" variable.
    • Marked as answer by wuz Wednesday, April 1, 2009 12:43 AM
    Sunday, March 29, 2009 4:13 PM
    Answerer

All replies

  • You can use the same idea that Matt Warren laid out here.

    List<IQueryable<Temp>> temps = new List<IQueryable<Temp>>();
    
    temps.Add(db.Temps.Where(t => t.ProductId == 10));
    temps.Add(db.Temps.Where(t => t.ProductId == 11));
    temps.Add(db.Temps.Where(t => t.ProductId == 12));
    
    var query = temps.Aggregate((t1, t2) => t1.Intersect(t2));
    
    Only one query will be sent to the server here, when you iterate the results in the "query" variable.
    • Marked as answer by wuz Wednesday, April 1, 2009 12:43 AM
    Sunday, March 29, 2009 4:13 PM
    Answerer
  • Thank's that is definitely a good approach. But could be tricky if t1 and t2 do nor use the same object for the select cause. Referring to the example the condition is still Product10, 11 and 12 but i want to get the manufactures which i can only get with a join.
    Wednesday, April 1, 2009 12:55 AM