How to create Linq2Sql query, calculating aggregates?

Question

Is there any way to create Linq2SQL query, that will be translated to this:

SELECT  COUNT(*) as totalCount ,
 SUM(v.field1) AS totalfield1,
....
 SUM(v.<fieldN>) AS total<fieldN>
FROM    [dbo].[SomeTable] v

Answer 1

http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

See aggregate operators

Answer 2

I have found soluthion with DataObjects.NET

var query = from product in Query.All() where product.ProductName.Contains("a") select new {Product = product, FakeKey = 0} into i group i.Product.UnitPrice by i.FakeKey into g select new { Count = g.Count(), Price = g.Sum() };

var result = query.AsEnumerable().Single();

SQL:

SELECT Count_big(*) AS [column1], SUM([a].[UnitPrice]) AS [column2] FROM (SELECT [b].[ProductId], [b].[TypeId], [b].[ProductName], [b].[Seller], [b].[Category.Id], [b].[ProductType], [b].[UnitPrice], [b].[UnitsInStock], [b].[UnitsOnOrder], [b].[ReorderLevel], [b].[QuantityPerUnit], 0 AS [column] FROM [dbo].[Products] [b] WHERE ( [b].[ProductName] LIKE '%a%' )) [a] GROUP BY [a].[column];

Answer 3

You can try something like

var query = from p in SalesOrderDetails group p by p.ProductID into g select new {id = g.Key, Count = g.Count(), Sum = g.Sum(p => p.OrderQty)};

Console.Write(query);

but your solution of using fake key and then enumerating seems much better.

var query = from p in SalesOrderDetails group p by p.ProductID into g select new {id = g.Key, FalseKey = 1, Count = g.Count(), Sum = g.Sum(p => p.OrderQty) };

//Console.Write(query);
var result = from q in query
                  group q by q.FalseKey into c
                  select new { summation = c.Sum(q => q.Sum)};

Console.Write(result);