Linq query order by giving me issues

I have a stored procedure that I wrote in SSMS using joins, show below

select distinct z.size, z.SortID 
from [ProductAttributes] x 
join [Product] y on x.ProductID = y.ProductID 
join [ProductSort] z on x.Size = z.Size 
where x.ProductID = @productID 
order by z.SortID

When I execute it returns the values I want and orders them by sortID, which I later supply to a dropdown list in ASP.NET MVC.

I have since then wrote the stored procedure query using linq, shown below

var sizes = (from x in db.ProductAttributes
             join y in db.Products on x.ProductID equals y.ProductID
             join z in db.ProductSorts on x.Size equals z.Size
             where x.ProductID == productID
             orderby z.SortID 
             select x.Size).Distinct();

The problem is using this linq statement, it is not putting the data in the correct order, I am trying to order the dropdown list by sizes M, L, XL, XXL, XXXL, but it is returning L, M, XL, XXL, XXXL, so I am assuming it is ordering the data alphabetically.

If you look at the regular SQL statement the beginning of the query has 2 items after the select clause, can I not do this same thing in linq.

Any help would be appreciated

Answer

I this case I would force the ordering after the query.

Here are a couple of ways to do it.

var allPossibleSizesInCorrectOrder = new []
{
    "XXXS", "XXS", "XS", "S", "M", "L", "XL", "XXL", "XXXL",
};

var ordering =
    allPossibleSizesInCorrectOrder
        .Select((x, n) => new { x, n })
        .ToDictionary(xn => xn.x, xn => xn.n);

var sizes =
(
    from x in db.ProductAttributes
    join y in db.Products on x.ProductID equals y.ProductID
    join z in db.ProductSorts on x.Size equals z.Size
    where x.ProductID == productID
    select x.Size
)
    .Distinct()
    .ToArray()
    .OrderBy(x => ordering[x])
    .ToArray();

This ignores sorting on the database and does an in memory sort based on the order of the allPossibleSizesInCorrectOrder array.

You could also do it this way:

var sizes =
(
    from x in db.ProductAttributes
    join y in db.Products on x.ProductID equals y.ProductID
    join z in db.ProductSorts on x.Size equals z.Size
    where x.ProductID == productID
    select new { x.Size, z.SortID }
)
    .ToArray()
    .OrderBy(x => x.SortID)
    .GroupBy(x => x.Size)
    .SelectMany(x => x.Take(1))
    .Select(x => x.Size)
    .ToArray();