Convert Linq to SQL

Question

I have researched on the net and most result are converting from sql to linq and seldom have linq to sql.

this is the code which I want to convert to SQL :

 using (CommerceEntities db = new CommerceEntities())
    {
    try
      {
      var query = (from ProductOrders in db.OrderDetails
                        join SelectedProducts in db.Products on ProductOrders.ProductID  
                        equals SelectedProducts.ProductID
                        group ProductOrders by new
                            {
                            ProductId = SelectedProducts.ProductID,
                            ModelName = SelectedProducts.ModelName
                            } into grp
                        select new
                            {
                            ModelName = grp.Key.ModelName,
                            ProductId = grp.Key.ProductId,
                            Quantity = grp.Sum(o => o.Quantity)
                            } into orderdgrp where orderdgrp.Quantity > 0 
                        orderby orderdgrp.Quantity descending select orderdgrp).Take(5);

                    RepeaterItemsList.DataSource = query;
                    RepeaterItemsList.DataBind(); 
      }
    catch (Exception exp)
      {
      throw new Exception("ERROR: Unable to Load Popular Items - " + 
                           exp.Message.ToString(), exp);
      }
    }

You can always use the profiler to look at the actual query produced, or plug in some form of logging. — David Hedlund, Jul 30 '12 at 06:53
Why don't you try Linqpad, it will help you to understand things and you can see the sql generated as well. — Nilesh Thakkar, Jul 30 '12 at 06:58

score 5 · Accepted Answer · edited May 23 '17 at 12:06

5

You can attempt to run the LINQ statement in LinqPad. For examples on how to use LinqPad, check the answer here.

It has a tab to show the generated SQL statement.

edited May 23 '17 at 12:06

Community

1
1

answered Jul 30 '12 at 06:59

alextansc

4,626
6
29
45

I had download the LinqPad, but how to use it? – user1529419 Jul 30 '12 at 09:07

score 2 · Answer 2 · answered Jul 30 '12 at 07:03

Here's an article on logging in LINQ to SQL. It lets you specify a TextWriter to which to send the query.

Basically, you can write something like this:

db.Log = new System.IO.StreamWriter("linq-to-sql.log") { AutoFlush = true };

... where db is your data context.

In SQL you'd write something like this (although the produced code will look a lot different, since it is auto-generated):

SELECT TOP 5 Products.ModelName, Products.ProductID, SUM(OrderDetails.Quantity) qty
FROM OrderDetails
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.ProductID, Products.ModelName
HAVING qty > 0
ORDER BY qty DESC

Convert Linq to SQL

2 Answers2

Linked