none
How to translate nested query SQL to EF RRS feed

  • Question

  • [Edit - Your forum won't let me include images or links until it can verify my account - but I can't figure out how to do this. For this reason you may prefer to read this question on stackoverflow, which has a picture of the relationships: http://stackoverflow.com/questions/29020733/translate-nested-query-sql-to-ef]

    I have a classic many-to-many table relationship between orders, catalog #'s, and categories. I want to get all the orders for products belonging to the category for a small number of products I provide. It may be easier to show the SQL that does exactly what I want:

    select o.*
    from [Order] o join Product p2 on o.FKCatalogNumber=p2.CatalogNumber
    where p2.FKCategoryId IN
     (select c.Id
    from Category c join Product p1 on p1.FKCategoryId=c.Id
      where p1.CatalogNumber in ('0001', '0002')

    This example gives me all the orders belonging to the categories that catalog #'s 0001 and 0002 are in.

    But I am unable to wrap my head around the equivalent EF syntax for this query. I'm embarrassed to say I spent half the day on this. I bet it's easy for someone out there.

    I came up with this but it's not working (and probably not even close):

    string[] catNumbers = {"0001", "0002"};
    
    var orders = ctx.Categories
      .SelectMany(c => c.Products, (c, p) => new {c, p})
      .Where(@t => catNumbers.Contains(@t.p.CatalogNumber))
      .Select(@t => @t.p.Orders)
      .ToList();
    I posted this question on stackoverflow yesterday but haven't yet received a response so I'm also posting it here to assure a response through my MSDN benefit. Thanks!

    Friday, March 13, 2015 8:07 PM

Answers

  • Someone on stackoverflow had the answer I was looking for. I copied it here for convenience:

    string[] catNumbers = {"0001", "0002"};
    
    var orders = ctx.Orders
     .Where(o => ctx.Products
      .Where(p => catNumbers.Contains(p.CatalogNumber))
      .Select(p => p.CategoryId)
      .Contains(o.Product.CategoryId)
     );

    Wednesday, March 18, 2015 3:04 PM

All replies

  • Hello Rdogmartin,

    >>I came up with this but it's not working (and probably not even close

    From your provided sql statement, you uses IN and JOIN syntax, in LINQ, they are represented as:

    .Contains() method and JOIN in LINQ

    Since it is not clear your detail tables, I created a similar sql query and its correspondent LINQ syntax:

    SQL Statement

    select * from [Order] o 
    
    join [Customer] c on o.OrderID = c.OrderID
    
    where c.OrderID in
    
    (
    
    select od.OrderID from OrderDetail od
    
    where od.OrderDetailID in (1,2,3,4)
    
    )
    

    LINQ query:

    using (DFDBEntities db = new DFDBEntities())
    
                {
    
                    db.Database.Log = Console.Write;
    
                    List<int> ids = new List<int>() { 1, 2, 3, 4 };
    
                    var result = (from o in db.Orders
    
                                  join c in db.Customers on o.OrderID equals c.OrderID
    
                                  where db.OrderDetails.Where(od => ids.Any(id => id == od.OrderID)).Select(od => od.OrderID).Contains(c.OrderID)
    
                                  select o).ToList();
    
                }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 16, 2015 5:55 AM
    Moderator
  • Thanks, but my question refers to a many-to-many relationship, and your example is for a many-to-one relationship. You may want to click the link at the beginning of my post that points to the same question on stackoverflow - it has a diagram of the table relationships.
    Monday, March 16, 2015 2:09 PM
  • Hello,

    >> but my question refers to a many-to-many relationship, and your example is for a many-to-one relationship

    The example is used to show the usage of syntax as “IN”, “JOIN” and the sub query in LINQ, it is not specific for the one to many relationships, for whatever relationships, you could use these sql stynaxs in LINQ as my way. You may also check solutions in your original post on the stackoverflow site.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, March 17, 2015 6:59 AM
    Moderator
  • Someone on stackoverflow had the answer I was looking for. I copied it here for convenience:

    string[] catNumbers = {"0001", "0002"};
    
    var orders = ctx.Orders
     .Where(o => ctx.Products
      .Where(p => catNumbers.Contains(p.CatalogNumber))
      .Select(p => p.CategoryId)
      .Contains(o.Product.CategoryId)
     );

    Wednesday, March 18, 2015 3:04 PM