Entity Sql Group By problem, please help
help me please with this simple E-sql query:
var qStr = "SELECT SqlServer.Month(o.DatePaid) as month, SqlServer.Sum(o.PaidMoney) as PaidMoney FROM XACCModel.OrdersIncomes as o group by SqlServer.Month(o.DatePaid)";
Here's what I have.
I have simple Entity called OrdersIncomes
with ID,PaidMoney,DatePaid,Order_ID
properties
I want to select Month and Summed PaidMoney like this:
month Paidmoney
1 500
2 700
3 1200
T-SQL looks like this and works fine:
select MONTH(o.DatePaid), SUM(o.PaidMoney)
from OrdersIncomes as o
group by MONTH(o.DatePaid)
results:
-----------
3 31.0000
4 127.0000
5 20.0000
(3 row(s) affected)
but E-SQL doesnot work and I dont know what to do. here my E-SQL which needs refactoring:
var qStr = "SELECT SqlServer.Month(o.DatePaid) as month, SqlServe开发者_如何学运维r.Sum(o.PaidMoney) as PaidMoney FROM XACCModel.OrdersIncomes as o group by SqlServer.Month(o.DatePaid)";
there's an exception: ErrorDescription = "The identifier 'o' is not valid because it is not contained either in an aggregate function or in the GROUP BY clause."
if I include o in group by clause, like: FROM XACCModel.OrdersIncomes as o group by o
then I don't get summed and aggregated results.
Is this a bug, or what I'm doing wrong?
Here's a Linq to Entities query and it works too:
var incomeResult = from ic in _context.OrdersIncomes
group ic by ic.DatePaid.Month into gr
select new { Month = gr.Key, PaidMoney = gr.Sum(i => i.PaidMoney) };
Give an alias name to the Group By clause and use that alias in the select clause.
SELECT PaidMonth, SqlServer.Sum(o.PaidMoney) as PaidMoney FROM XACCModel.OrdersIncomes as o group by SqlServer.Month(o.DatePaid) as PaidMonth
精彩评论