Lab Lab Lab Lab - 1 year ago 95
ASP.NET (C#) Question

How can I get a value inside the select query using linq?

I have combined 2 tables Clients and Payments using LINQ inner join. After this, I fill in a new model Totals with a values from those two tables. I just need to get a

Username
. Any ideas? I'll be very thankful!

var results = from client in _db.Clients
join trade in _db.Payments on client.Id equals trade.ClientId
where (client.Id == trade.ClientId &&
DateTime.Compare(trade.TradeDate, (DateTime)fromDate) >= 0 &&
DateTime.Compare(trade.TradeDate, (DateTime) toDate) <= 0)
select new { client.Username, trade};
var cs = results
.GroupBy(tr => tr.trade.ClientId)
.Select(item => new Totals
{
Username = //here I need to set a value
CommBcf = item.Sum(client => client.trade.CommBcf),
EcnAll = item.Sum(client => client.trade.EcnAll),
EcnRbt = item.Sum(client => client.trade.EcnRbt),
OpenGross = item.Sum(client => client.trade.OpenGross),
CloseGross = item.Sum(client => client.trade.CloseGross)
}).ToList();

Answer Source

Group by key and name. You can group by mutiple columns by creating new anonymous object with new {id,name} .

 var results = from client in _db.Clients
                              join trade in _db.Payments on client.Id equals trade.ClientId
                              where (client.Id == trade.ClientId &&
                              DateTime.Compare(trade.TradeDate, (DateTime)fromDate) >= 0 &&
                              DateTime.Compare(trade.TradeDate, (DateTime) toDate) <= 0)
                              select new { client.Username, trade};
                var cs = results
                    .GroupBy(tr => new {id=tr.trade.ClientId,name=tr.trade.Name})
                    .Select(item => new Totals
                    {
                        Username = item.Key.name
                        CommBcf = item.Sum(client => client.trade.CommBcf),
                        EcnAll = item.Sum(client => client.trade.EcnAll),
                        EcnRbt = item.Sum(client => client.trade.EcnRbt),
                        OpenGross = item.Sum(client => client.trade.OpenGross),
                        CloseGross = item.Sum(client => client.trade.CloseGross)
    }).ToList();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download