CODEWITHSUNDEEP
c#linqfor-loopsum
DysonGuy
DysonGuy

Reputation: 163

Looping through fields to get sum

I know there are probably 100 much easier ways to do this but until I see it I can't comprehend how to go about it. I'm using linqpad for this. Before I go to phase 2 I need to get this part to work!

I've connected to an SQL database. I'm running a query to retrieve some desired records.

var DesiredSym = (from r in Symptoms
where r.Status.Equals(1) && r.Create_Date < TimespanSecs  
select r).Take(5);

So, in this example, I retrieve 5 'records' essentially in my DesiredSym variable as iQueryable (linqpad tells me this)

The DesiredSym contains a large number of fields including a number feilds that hold a int of Month1_Used, Month2_Used, Month3_Used .... Month12_Use.

So I want to loop through the DesiredSym and basically get the sum of all the Monthx_Used fields. 

foreach (var MonthUse in DesiredSym)
{
  // get sum of all fields where they start with MonthX_Used;
}

This is where I'm not clear on how to proceed or even if I'm on the right track. Thanks for getting me on the right track.

Upvotes: 1

Views: 139

Answers (1)

p.s.w.g
p.s.w.g

Reputation: 149000

Since you've got a static number of fields, I'd recommend this:

var DesiredSym = 
    (from r in Symptoms
     where r.Status.Equals(1) && r.Create_Date < TimespanSecs  
     select retireMe)
    .Take(5);

var sum = DesiredSym.Sum(s => s.Month1_Use + s.Month2_Use + ... + s.Month12_Use);

You could use reflection, but that would be significantly slower and require more resources, since you'd need to pull the whole result set into memory first. But just for the sake of argument, it would look something like this:

var t = DesiredSym.GetType().GenericTypeArguments[0];
var props = t.GetProperties().Where(p => p.Name.StartsWith("Month"));
var sum = DesiredSym.AsEnumerable()
                    .Sum(s => props.Sum(p => (int)p.GetValue(s, null)));

Or this, which is a more complicated use of reflection, but it has the benefit of still being executed on the database:

var t = DesiredSym.GetType().GenericTypeArguments[0];
var param = Expression.Parameter(t);
var exp = t.GetProperties()
           .Where(p => p.Name.StartsWith("Month"))
           .Select(p => (Expression)Expression.Property(param, p))
           .Aggregate((x, y) => Expression.Add(x, y));
var lambda = Expression.Lambda(exp, param);
var sum = DesiredSym.Sum(lambda);

Now, to these methods (except the third) to calculate the sum in batches of 5, you can use MoreLINQ's Batch method (also available on NuGet):

var DesiredSym = 
    from r in Symptoms
    where r.Status.Equals(1) && r.Create_Date < TimespanSecs  
    select retireMe;

// first method
var batchSums = DesiredSym.Batch(5, b => b.Sum(s => s.Month1_Use ...)); 

// second method
var t = DesiredSym.GetType().GenericTypeArguments[0];
var props = t.GetProperties().Where(p => p.Name.StartsWith("Month"));
var batchSums = DesiredSym.Batch(5, b => b.Sum(s => props.Sum(p => (int)p.GetValue(s, null))));

Both these methods will be a bit slower and use more resources since all the processing has to be don in memory. For the same reason the third method will not work, since MoreLinq does not support the IQueryable interface.

Upvotes: 2

Related Questions