Many-to-many sorting in Entity Framework

Question

My application has an Entity Framework model containing a many-to-many relationship like the following:

ProductGroup: 
    Scalar: Id, Name
    Navigation: ProductGroupProduct
Product: 
    Scalar: Id, Sku, Description, etc.
    Navigation: ProductGroupProduct
ProductGroupProduct: 
    Scalar: ProductGroupId, ProductId, Position
    Navigation: Product, ProductGroup

Note how the intermediate table has a scalar property called Position that specifies the order in which a product should be displayed within a product group.

How would you write a LINQ query that returns a list of products in a given product group sorted by the Position property? If I was writing good ol' SQL I'd write something like this:

SELECT p.Id, p.Sku, p.Description
FROM Product p
INNER JOIN ProductGroupProduct pgp ON p.Id = pgp.ProductId 
WHERE pgp.ProductGroupId = @MyProductGroupId 
ORDER BY pgp.Position

But I can't figure the LINQ out.

Answer 1

Um, your SQL won't work, because there is no ProductGroup.Position

But I think you want:

var q = from pgp in Context.ProductGroupProducts
        where pgp.ProductGroup.Id == id
        orderby pgp.Position
        select pgp.Product;