psyoptica psyoptica - 7 days ago 7
C# Question

Groupby then select data using lambda/linq

I'm trying to group some db data using the following statement.

var res = from messages in db.Messages
group messages by messages.Message_Sent_Date into grp
select new { message_count = grp.Sum(g => g.Message_Count), message_cost = grp.Sum(g => g.Customer_Price) };


which is equivalent to:

var query = db.Messages.Where(m => m.Message_Sent_Date != null && m.Message_Count != 99)
.GroupBy(d => d.Message_Sent_Date)
.Select(g => new {
message_count = g.Sum(c => c.Message_Count),
message_cost = g.Sum(c => c.Customer_Price),
});


My question is, the object selected has 2 fields namely message_count and message_cost. If I want to add a third field 'Message_Sent_Date' which is essentially the column by which the data is grouped into. How do I do that?

Answer

Use g.Key which will be the Message_Sent_Date for each group, so the snippet will be like this:

 // Modified select after the GroupBy clause 
.Select(g =>
        new
        {
            message_count = g.Sum(c => c.Message_Count),
            message_cost = g.Sum(c => c.Customer_Price),
            Message_Sent_Date= g.Key
        });
Comments