StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStruggling to convert SQL to LINQ
text
Body
copied!<p>I have a Linq expression that (for northwind) gets the total qty ordered per product for customer id 'ALFKI'</p> <pre><code>from od in db.OrderDetails where od.Order.CustomerID == "ALFKI" group od by od.Product.ProductName into g1 select new { ProductName = g1.Key, Total = g1.Sum(od => od.Quantity) } </code></pre> <p>This is fine but to fully understand Linq, I want to try and formulate the expression in a world where Linq2Sql doesn't nicely build the property bridges through foreign keys.</p> <p>For instance, in the above expression, I'm accessing od.Order.CustomerID. I want to assume that od.OrderID is as far as I can go.</p> <p>Looking at the SQL for the expression, I have:</p> <pre><code>SELECT SUM(CONVERT(Int,[t0].[Quantity])) AS [Total], [t2].[ProductName] FROM [Order Details] AS [t0] INNER JOIN [Orders] AS [t1] ON [t1].[OrderID] = [t0].[OrderID] INNER JOIN [Products] AS [t2] ON [t2].[ProductID] = [t0].[ProductID] WHERE [t1].[CustomerID] = @p0 GROUP BY [t2].[ProductName] </code></pre> <p>This is as far as I've managed to get:</p> <pre><code>from od in db.OrderDetails join o in db.Orders on od.OrderID equals o.OrderID join p in db.Products on od.ProductID equals p.ProductID where o.CustomerID == "ALFKI" group od by od.ProductID into g1 select new { ProductName = g1.Key, Total = g1.Sum(od => od.Quantity) } </code></pre> <p>This is almost there but g1.Key is referring to the ProductID. I can't seem to get at the ProductName and the order quantity at the same time.</p> <p>Thanks</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload