开发者

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) }
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜