LINQ-to-SQL - "Sum" inside the selected new

Question

I have a LINQ-to-SQL query that goes through a table and I want to select 3 sum - the sum of "Rate" and "AdditionalCharges", so I have something like this:

var sums = from d in dc.Deliveries where d.TripDate == DateTime.Now select new { Rate = d.Rate, AdditionalCharges = d.AdditionalCharges }; 

However, obviously, this returns a new line for each delivery, which means that I have to summarize later - which seems pretty inefficient. Is there an easier way?

Answer 1

If you use query syntax you can do something like the following

 var data = dc.Deliveries.Where(d => d.TripDate == DateTime.Now) var rateSum = data.Sum(d => d.Rate); var additionalCharges = data.Sum(d => d.AdditionalCharges); 

it is from the top of the head and not verified

Answer 2

I know this is an old question, but hey I found it, so hopefully this helps someone else ...

You can also do this using the Fluent syntax:

 var sums = dc.Deliveries .Where(d => d.TripDate == DateTime.Now) .GroupBy(d => d.TripDate) .Select(g => new { Rate = g.Sum(s => s.Rate), AdditionalCharges = g.Sum(s => s.AdditionalCharges) }); 

Hope this helps someone ...

Answer 3

Not sure, but you can try using the bye by down function below

 var sums = from d in dc.Deliveries where d.TripDate == DateTime.Now group d by new {d.Rate,d.AdditionalCharges,d.TripDate} into g select new { Rate = g.Sum(s => s.Rate ), AdditionalCharges = g.Sum(s => s.AdditionalCharges) }; 

Answer 4

You should be able to do this:

 DateTime d = DateTime.Now; var sums = from d in dc.Deliveries select new { Rate = dc.Deliveries.Where(n => n.TripDate == d).Sum(n => n.Rate), AdditionalCharges = dc.Deliveries.Where(n => n.TripDate == d).Sum(n => n.AdditionalCharges) }; var result = sums.FirstOrDefault(); 

Answer 5

  var sums = from d in dc.Deliveries where d.TripDate == DateTime.Now Group by d.TripDate // or primary key Into TotalRate = sum(d.Rate), TotalAdditionalCharges = sum(d.AdditionalCharges) Select TotalRate , TotalAdditionalCharges