将简单的SQL分组依据转换为LINQ to SQL [英] Convert simple SQL group-by into LINQ to SQL

问题描述

我遇到了麻烦.我无法理解Stack Overflow上对此问题的现有答案，而且对于LINQ to SQL来说太新了，无法自己解决.

I'm having trouble. I can't understand existing answers to this on Stack Overflow and am too new to LINQ to SQL to be able to nut it out myself.

查看此SQL:

select p.Name as ProductName, SUM(o.NumberOf) as TotalOrdered from [Order] o
  join [Product] p on o.ProductId = p.Id
  group by p.Name

返回一个漂亮的2列表，其左侧为产品名称，而在右列中为已订购(所有订单)的产品总数.如何在LINQ to SQL中复制它?

Returns a nice 2-column table with product names on the left and the total number that product which have been ordered (across all orders) in the right column. How can I duplicate this in LINQ to SQL?

这是到目前为止我得到的:

Here is what I've got so far:

var ctx = new DataClasses1DataContext();
var totalProducts = (from o in ctx.Orders
                     join p in ctx.Products on o.ProductId equals p.Id
                     select new { p.Name, o.NumberOf })
    .GroupBy(t => t.Name)
    .Select(g => g.Key, ... );

这是怎么回事?

推荐答案

在我看来，您想要的是:

It looks to me like you want:

.Select(g => new { ProductName = g.Key, TotalOrdered = g.Sum(x => x.NumberOf) })

您可以将整个查询作为单个查询表达式或完全不使用查询表达式来进行:

You can do your whole query as either a single query expression or without using query expressions at all though:

var totalProducts = ctx.Orders
                       .Join(ctx.Products, o => o.ProductId, p => p.Id,
                             (o, p) => new { p.Name, o.NumberOf })
                       .GroupBy(t => t.Name,
                                pair => pair.Name, // Key selector
                                pair => pair.NumberOf, // Element selector
                                (key, numbers) => new { 
                                    ProductName = key,
                                    TotalOrdered = numbers.Sum()) 
                                });

或者:

var totalProdcuts = from o in ctx.Orders
                    join p in ctx.Products on o.ProductId equals p.Id
                    group o.NumberOf by p.Name into g
                    select new { ProductName = g.Key, TotalOrdered = g.Sum() };

这篇关于将简单的SQL分组依据转换为LINQ to SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！