CODEWITHSUNDEEP
c#.netdatabaselinqlinq-to-sql
Dilberted
Dilberted

Reputation: 1172

Linq Query: Sum of columns for a given row

I searched a lot of questions posted here but I'm unable to find a better solution for my problem.

I have a table with 6 columns storing decimal values and want to do a sum of all the columns in the query. Currently this is what I am doing.

(from i in db.OperationalValues
 where i.Timestamp == date
 select new
 {
   CumulativeValues = i.Value1.GetValueOrDefault(0)
                           + i.Value2.GetValueOrDefault(0)
                           + i.Value3.GetValueOrDefault(0)
                           + i.Value4.GetValueOrDefault(0)
                           + i.Value5.GetValueOrDefault(0)
                           + i.Value6.GetValueOrDefault(0),
 }).FirstOrDefault();

I have looked at many questions and answers but was hoping to get a better way to write this LINQ query. As the table could have more columns and if the number of columns increase the query will become huge.

If anyone has a better way to write this LINQ query, it would be highly appreciated.

Thanks in advance.

Upvotes: 3

Views: 885

Answers (3)

linquize
linquize

Reputation: 20366

var result = new { CumulativeValues = 0 };
var item = db.OperationalValues.FirstOrDefault(i => i.Timestamp == date);
if (item != null)
    result = new { CumulativeValues = item.Value1 ?? 0 + item.Value2 ?? 0 + item.Value3 ?? 0 + item.Value4 ?? 0 + item.Value5 ?? 0 + item.Value6 ?? 0 };

Upvotes: 1

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102368

It think this is an option you have because of the dynamic nature you mention:

Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library)

Upvotes: 0

Reddog
Reddog

Reputation: 15579

That is the best/only way to go without delving into reflection for finding the decimal property types.

However, if you have loads of these "value" columns then you might want to redesign your database to move them into rows in a related table. If you did that then you could make use of the Sum functionality of Linq-to-sql.

Upvotes: 0

Related Questions