CODEWITHSUNDEEP
c#linq
user7951164
user7951164

Reputation:

Sum of varchar field in linq , how to do conversion

I Have a query in sql 

select SUM(convert(numeric ,Sub_Key_Value)) from [dbo].[Business_Report] where Parent_Tab='Staffing' and Sub_Key_Name='BM'

It is running fine , I am trying to implement it using linq in c# , Below is the code

int sum = dt.AsEnumerable().Where(x => x.Field<string>("Parent_Tab") == "Staffing")
                           .Where((y => y.Field<string>("Sub_Key_Name") == "BM")).Sum(z => z.Field<int>("Sub_Key_Value"));

Problem is , when i run this code it shows "Specified cast is not valid." Probably in section 

Sum(z => z.Field<int>("Sub_Key_Value")

It is coming as string from database and cant cast it as int to get sum
Any idea how to get sum in linq is appreciable Thanks

Upvotes: 2

Views: 496

Answers (2)

sujith karivelil
sujith karivelil

Reputation: 29026

This is happening like that because, the Sub_Key_Value in the databases may not be of type integer or else it cannot be convertible to an integer. Let the column is of type Varchar, and we are applying the above query. Now we can access that column value using .Field<string> but Sum() expect some numeric values so we have to convert them to integer by using some conversion mechanisms. Here is a sample code for you:

.Sum(z => int.Parse(z.Field<string>("Sub_Key_Value"))

Upvotes: 2

Slai
Slai

Reputation: 22876

Because Field doesn't do the conversion to int.

int sum = dt.Rows.Cast<DatRow>().Sum(r => "Staffing".Equals(r["Parent_Tab"]) 
              && "BM".Equals(r["Sub_Key_Name"]) ? Convert.ToInt32(r["Sub_Key_Value"]) : 0);

Upvotes: 2

Related Questions