LINQ to SQL with group by clause with aggregate function
I need equivalent LINQ to SQL query for the query writte开发者_开发问答n in access database. Below is the query in access database.
SELECT Table1.ID, Table1.CODE, MIN (Table1.COST)
FROM Table1 GROUP BY Table1.ID, Table1.CODE
In this query I get COST which is minimum to the given repeated CODE
Below query is giving error "Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access."
var ptQ = from i in
(from uh in ptTable
select new
{
uh.ID, uh.CODE, uh.COST
})
group i by new { i.ID, i.CODE }
into g
select new
{
g.Key.ID, g.Key.CODE, g.Min(uh => uh.COST)
};
You need to specify the property name for the Min
call:
select new {g.Key.ID, g.Key.CODE, MinimumCost = g.Min(uh=>uh.COST)};
The C# compiler will infer a name if the property value is a property or field access (as per ID and CODE), so the above is equivalent to:
select new { ID = g.Key.ID,
CODE = g.Key.CODE,
MinimumCost = g.Min(uh=>uh.COST) };
Personally I would specify the names everywhere if they're not idiomatic C# names otherwise:
select new { Id = g.Key.ID,
Code = g.Key.CODE,
MinimumCost = g.Min(uh=>uh.COST) };
You need to tell it what to call the last value in the anon-type (note that for direct member access members (g.Key.ID
and g.Key.CODE
) the name can be inferred (ID
and CODE
respectively):
select new {g.Key.ID, g.Key.CODE, MinCost = g.Min(uh=>uh.COST)};
You can also supply explicit names for the other members if you want:
select new {Id = g.Key.ID, Code = g.Key.CODE, MinCost = g.Min(uh=>uh.COST)};
You should be able to do it in one statement:
from i in Table1
group i by new { i.ID, i.CODE } into g
select new { g.Key.ID, g.Key.CODE, COST = g.Min(x => x.COST) }
精彩评论