How to transfor query from SQL to LINQ [closed]

问题:

I have many to many relationship, table Product, table Category and join table ProductCategory.

I need to convert this SQL query to LINQ C#:

    Select TOP 3 Categories.id, Categories.Description, avg(Products.Price) as MeanPrice, count(Products.IsAvailable) as NumberOfAvailableProducts
FROM Categories
INNER JOIN ProductCategories on  Categories.Id = 
           ProductCategories.CategoryId
INNER JOIN Products on Products.Id = ProductCategories.CategoryId
WHERE Products.IsAvailable='true'
GROUP BY Categories.Id, Categories.Description
ORDER BY MeanPrice DESC

I would appreciate help, thanks.

回答1:

There are so many examples in the Internet for you to start learning how to write LINQ effectively (including the post from NetMage above).

But I would give you the examples anyway, based on your case above, with an assumption that this T-SQL of yours :

INNER JOIN Products on Products.Id = ProductCategories.CategoryId

might've been a typo, because what you really meant was maybe like this :

INNER JOIN Products on Products.Id = ProductCategories.ProductId

So, this is the snippet when you ask me how could I do that in LINQ :

var query = (from finalq in
                (
                    from cat in categories
                    join procat in
                        // Join [ProductCategories] with [Products] first
                        (
                            from pc in productcategories
                            join p in products on pc.ProductId equals p.Id
                            where p.IsAvailable == "True"
                            select new
                            {
                                CategoryId = pc.CategoryId,
                                ProductId = p.Id,
                                ProductPrice = p.Price
                            }
                        )
                    // And then join the result with [Categories]
                    on cat.Id equals procat.CategoryId
                    select new
                    {
                        CategoryId = cat.Id,
                        CategoryDescription = cat.Description,
                        ProductPrice = procat.ProductPrice
                    }
                )
            // Then aggregate it to get the final result
            group finalq by new { finalq.CategoryId, finalq.CategoryDescription } into finalGroup
            orderby finalGroup.Average(c => c.ProductPrice) descending
            select new
            {
                CategoryId = finalGroup.Key.CategoryId,
                CategoryDescription = finalGroup.Key.CategoryDescription,
                ProductPrice = finalGroup.Average(c => c.ProductPrice),
                NumberOfAvailableProducts = finalGroup.Count()
            }
        )
        // And take the TOP 3 rows (I know it's ugly but I don't have a choice)
        .Take(3);

So, that's it. I hope you can get something from my post here, and later may create your own, a better version of this quick example.

Good luck!

回答2:

For translating SQL to LINQ query comprehension:

1. Translate FROM subselects as separately declared variables.
2. Translate each clause in LINQ clause order, translating monadic operators (DISTINCT, TOP, etc) into functions applied to the whole LINQ query.
3. Use table aliases as range variables. Use column aliases as anonymous type field names.
4. Use anonymous types (new { ... }) for multiple columns.
5. Left Join is simulated by using a into join_variable and doing another from from the join variable followed by .DefaultIfEmpty().
6. Replace COALESCE with the conditional operator and a null test.
7. Translate IN to .Contains() and NOT IN to !...Contains()
8. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
9. SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
10. Proper FULL OUTER JOIN must be handled with an extension method.