How To Transfor Query From Sql To Linq

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, Catego

Solution 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 :

INNERJOIN Products on Products.Id = ProductCategories.CategoryId

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

INNERJOIN 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"selectnew
                            {
                                CategoryId = pc.CategoryId,
                                ProductId = p.Id,
                                ProductPrice = p.Price
                            }
                        )
                    // And then join the result with [Categories]on cat.Id equals procat.CategoryId
                    selectnew
                    {
                        CategoryId = cat.Id,
                        CategoryDescription = cat.Description,
                        ProductPrice = procat.ProductPrice
                    }
                )
            // Then aggregate it to get the final resultgroup finalq bynew { finalq.CategoryId, finalq.CategoryDescription } into finalGroup
            orderby finalGroup.Average(c => c.ProductPrice) descendingselectnew
            {
                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!

Solution 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.