Ugly LINQ statement, a better way?

Question

I have a LINQ statement which is adding up the values of multiple columns, each beginning with 'HH' although there are other columns available:

//TODO Clean up this mess
var query1 = (from e in Data
                where e.SD == date
                select e).Select(x =>   x.HH01 + x.HH16 + x.HH17 + x.HH18 + x.HH19 + x.HH20 + x.HH21 + x.HH22 + x.HH23 +
                                        x.HH24 + x.HH25 + x.HH26 + x.HH27 + x.HH28 + x.HH29 + x.HH30 + x.HH31 + x.HH32 + 
                                        x.HH33 + x.HH34 + x.HH35 + x.HH36 + x.HH37 + x.HH38 + x.HH39 + x.HH40 + x.HH41 +
                                        x.HH42 + x.HH43 + x.HH44 +x.HH45 + x.HH46 + x.HH47 + x.HH48 + x.HH49.GetValueOrDefault()+
                                        x.HH50.GetValueOrDefault());

return query1.FirstOrDefault();

Is there any way to tidy this up? I have to do lots of variations of this (in different methods) so it would clear out a lot of 'fluff' if it could be.

Also I'd like to call .GetValueOrDefault() on each column, but currently I've taken this out due to the mess except for the last two columns.

Suggestions much appreciated!

Answer 1

I guess you can use Reflections for this:

double GetHHSum<T>(T x) where T : class
{
   double result = 0;

   var properties = typeof(T).GetProperties();
   foreach (var property in properties)
   {
       if (property.Name.StartsWith("HH"))
          sum += Convert.ToSingle(property.GetValue(x)).GetValueOrDefault();
   }

   return result;
}

And then use it like this:

return (from e in Data
        where e.SD == date
        select e).ToList().Select(x => GetHHSum(x)).FirstOrDefault();

Code is not tested

Answer 2

I might be wrong because I don't know your data, but it seems to me that they are not fully normalized (repetitive attributes). You might consider going to the 3rd form normal - thus create a/some separate table that will contain one value by row - and then to join your 2 tables in your linq query.

The link query will look much much better, and you will later be able to change your HH fields without changing your queries.