Awful LINQ instruction, the best way?

Question

I have a LINQ statement that adds the values ​​of several columns, each of which starts 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 remove this? I have to make many variations of this (in different methods) so that it can clear a lot of “fluff”, if it were possible.

I would also like to call .GetValueOrDefault() for each column, but currently I have taken this due to clutter, with the exception of the last two columns.

Suggestions highly appreciated!

Answer 1

I think 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 not verified

Answer 2

Maybe I'm wrong because I don’t know your data, but it seems to me that they are not completely normalized (duplicate attributes). You might think about moving on to the normal 3rd form - create some separate table that will contain one value per row, and then join your two tables in your linq query.

A link request will look much better, and you can change your HH fields later without changing your requests.

Answer 3

One suggestion is to reorganize the above code to use the LINQ and lambdas method chains (personal preferences) and then extract the selected lambda into a separate method. For example:

 // Note select e and .Select(x => x..) is redundant. Only need one var query1 = Data.Where(e => e.SD == date).Select(SumOfHValues); return query1.FirstOrDefault(); // Note types are unclear in your question so I've put dummy placeholders private static QueryResultType SumOfHValues(YourInputClassType x) { // Nothing wrong with this syntactically, it will be faster than a // reflection solution // // Algorithmic code tends to have this sort of look & feel. // You could make it more readable // by commenting exactly what the summation is doing and // with a mathematical notation or link to documentation / web source return 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() } 

Additionally, if you want to call GetValueOrDefault () for each HHxx property, you can wrap this in an additional helper function. it really comes down to code preference. Which do you prefer? Seeing .GetValueOrDefault () at the end of every access to a resource or function around it? eg

 return x.HH01 + x.HH16 + x.HH17 + x.HH18 

becomes

 return Get(x.HH01) + Get(x.HH16) + Get(x.HH17) + Get(x.HH18) ... private static HClassType Get(HClassType input) { return input.GetValueOrDefault(); } 

Personally, I would just go with the HHxx + HHyy code order in the columns and call .GetValueOrDefault () for each of them. If it places the helper method at least once it is only once, even if it is verbose.

Yours faithfully,