Many-to-many sorting in Entity Framework

Question

My application has an Entity Framework model containing many-to-many relationships, such as:

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

Notice how the staging table has a scalar property called Position, which determines the display order of the product within the product group.

How do you write a LINQ query that returns a list of products in a given product group, sorted by the Position property? If I wrote good SQL, I would 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 not understand the LINQ output.

Answer 1

Um, your SQL will not work because 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;