How to select multiple columns from datatable in linq to sql?

Question

HI,

I am using LINQ to SQL and i want to select multiple columns with datatable .. I have done that but the same code i have to write again and again.. thats not good  so i want to write only one query and multiple columns should be select.... how can i do this....

Here is my Code::

var queryAD1 = (from m in dt.AsEnumerable() 

	   where m.Field<string>("DiamondRangeName") == "H" 

  	   select m.Field<Decimal>("AD1")).Sum(); 

var queryAD2 = (from p in dt.AsEnumerable() 

	    where p.Field<string>("DiamondRangeName") == "H" 

	    select p.Field<Decimal>("AD2")).Sum(); 

var queryAD3 = (from r in dt.AsEnumerable()

	   where r.Field<string>("DiamondRangeName") == "H" 

	   select r.Field<Decimal>("AD3")).Sum();

In this i have to select AD1, AD2, AD3 in a single statement.... How can i do this???
plz help me!!!!

Answer 1

HI,

I am using LINQ to SQL and i want to select multiple columns with datatable .. I have done that but the same code i have to write again and again.. thats not good  so i want to write only one query and multiple columns should be select.... how can i do this....

Here is my Code::

var queryAD1 = (from m in dt.AsEnumerable() 

	  where m.Field<string>("DiamondRangeName") == "H" 

 	  select m.Field<Decimal>("AD1")).Sum(); 

var queryAD2 = (from p in dt.AsEnumerable() 

	  where p.Field<string>("DiamondRangeName") == "H" 

	  select p.Field<Decimal>("AD2")).Sum(); 

var queryAD3 = (from r in dt.AsEnumerable()

	  where r.Field<string>("DiamondRangeName") == "H" 

	  select r.Field<Decimal>("AD3")).Sum();

In this i have to select AD1, AD2, AD3 in a single statement.... How can i do this???
plz help me!!!!

   Hi, max_james:

   try like this:

   

var query= (from m in dt.AsEnumerable() 

	  where m.Field<string>("DiamondRangeName") == "H" 

      group m by new{m.Field<Decimal>("AD1"),m.Field<Decimal>("AD2"),m.Field<Decimal>("AD3")} 

      into g

 	  select new

      {

       a1Sum=g.Sum(a1=>a1.Field<Decimal>("AD1")),

       a2Sum=g.Sum(a1=>a1.Field<Decimal>("AD2")),

       a3Sum=g.Sum(a1=>a1.Field<Decimal>("AD3")),

      }  

 

 

---

Best Regards,
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 2

Hi Tim Tang,

very thanks for reply... i tried your code as u suggest me to do but it showing this errror in

   group m by new{m.Field<Decimal>("AD1"),m.Field<Decimal>("AD2"),m.Field<Decimal>("AD3")} 

this particular line and the error says::

"Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access."

hey now i made little changes in your code and that has worked fine and changes are::

group m by

new { AD1 = m.Field<Decimal>("AD1"),

                          AD2 = m.Field<Decimal>("AD2"),

                           AD3 = m.Field<Decimal>("AD3"),

 

                       }

Now its not showing the compilation error but it is not giving me the output what i was looking for....

Its not giving me the SUM of AD1,AD2, AD3 where DiamondRangeName == "H"... its just showing the values of AD1,AD2, AD3 of every row...

what to do now????

waiting for your reply...

 

Answer 3

oh..I'm sorry

There is something wrong..

and I make a exmple for NorthWind:

 

 

 (from q in Products
 let temp= from p in Products
 			where p.ProductName.Contains("C") 
			select new
			{
			 p.UnitPrice,
			 p.ReorderLevel,
			 p.UnitsInStock
			}
 select new
	{
		sum1=temp.Sum(t=>t.UnitPrice),
		sum2=temp.Sum(t=>t.UnitsInStock),
		sum3=temp.Sum(t=>t.ReorderLevel)
	}).FirstOrDefault()

 Hope it can help you

 

---

Best Regards,
If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 4

No its not that code which i am looking for... The first code which you provide to me that was fine and worked but there was only one problem that i was not getting the sum..

can you please make some changes on that code so that it could resolve my Sum of AD1, AD2, AD3 issue...

 i am also trying on my end..

i'll be very thankful to you... 

Answer 5

Hi max_james;

The following query should do what you need.

var queryAD1 = (from m in dt.AsEnumerable()
        where m.Field<string>("DiamondRangeName") == "H"
        group m by m.Field<string>("DiamondRangeName") into A123Group
        select new {
          A1 = A123Group.Sum (a1 => a1.Field<Decimal>("AD1")),
          A2 = A123Group.Sum (a2 => a2.Field<Decimal>("AD2")),
          A3 = A123Group.Sum (a3 => a3.Field<Decimal>("AD3")),
        };

If you do not understand something let me know.

Fernando

 

---

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 6

Thanks Fernandoo Soto your code absolutly worked fine.. No issues regarding this at all. Thank you very much but now i want one thing that from this code i am getting the Sum of AD1, AD2, AD3 and now i want to add all of three, for that i used .Sum() but its not getting done so can you plz tell me that how to do sum of AD1, AD2, AD3...

 

 

 

Answer 7

Hey i was doing Hit and Trial methods and now i have got the solution for sum of AD1, AD2, AD3..

I did something like this....

var a = queryAD1[0].A1 + queryAD1[0].A2 + queryAD1[0].A3;

and now i have got the Sum of AD1, AD2, AD3....

Is there any way to do that like if i dont want to use other new variable for Sum, i just do something and could get the Sum of AD1, AD2, AD3 in  var query1 variable.

Is that Possible????????? 

Answer 8

Hi max_james;

All you need to do is place all the columns you wish to add together in the function for the Sum method as shown in the code snippet.

// Replace your select clause with one like this.
select new {
 A1 = A123Group.Sum (a1 => a1.Field<Decimal>("AD1")),
 A2 = A123Group.Sum (a2 => a2.Field<Decimal>("AD2")),
 A3 = A123Group.Sum (a3 => a3.Field<Decimal>("AD3")),
 ATotals = A123Group.Sum ( at => at.Field<Decimal>("AD1") + 
                 at.Field<Decimal>("AD2") +
                 a3.Field<Decimal>("AD3"))
};

Fernando 

 

---

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 9

Hi max_james;

All you need to do is place all the columns you wish to add together in the function for the Sum method as shown in the code snippet.

 

// Replace your select clause with one like this.
select new {
 A1 = A123Group.Sum (a1 => a1.Field<Decimal>("AD1")),
 A2 = A123Group.Sum (a2 => a2.Field<Decimal>("AD2")),
 A3 = A123Group.Sum (a3 => a3.Field<Decimal>("AD3")),
 ATotals = A123Group.Sum ( at => at.Field<Decimal>("AD1") + 
         at.Field<Decimal>("AD2") +
         a3.Field<Decimal>("AD3"))
};

Fernando 

 

 

---

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

      Hi Max，

      Welcome to this forum and Thanks to all the participators and all your helpful advices.

      I think you could try the solution as Ferna mentioned. Here is my sample code:

      linq:

      from m in Addresses

      where m.City=="Bothell"

      group m by m.City into a123

      select new { a1=a123.Sum(a=>a.AddressID),             a2=a123.Sum(a=>a.StateProvinceID),a3=a123.Sum(a=>a.StateProvinceID),

   total=a123.Sum(a=>a.AddressID+a.StateProvinceID+a.StateProvinceID)}

   The SQL scripts created:

   SELECT SUM([t0].[AddressID]) AS [a1], SUM([t0].[StateProvinceID]) AS [a2], SUM([t0].[StateProvinceID]) AS [a3], SUM([t0].     [AddressID] + [t0].[StateProvinceID] + [t0].[StateProvinceID]) AS [total]

FROM [Person].[Address] AS [t0]

WHERE [t0].[City] = @p0

GROUP BY [t0].[City]

 

I hope that would be help you. Is there any issues please let me know.

Best Regards.

 

---

If it's helpful for you, Please vote or mark. Thank you!

David Peng [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 10

hey fernandoo Sorry for late reply..

i got the Sum but I am not getting access ATotals variable outside the select clause..

i need to use that value outside the Select Clause...

How can i do that???

Answer 11

The way it is writen will give you access to ATotals just like A1, A2 and A3. If you have not yet found the issue please post the code from you project sop that I can have a look.

---

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 12

DataColumnCollection

 

columnCollection = dsResult.Tables[0].Columns;

 

var

queryOppor =

 

from opportunityColumns in columnCollection.Cast<DataColumn

>()

 

where opportunityColumns.ColumnName.Contains("column names to filter"

)

 

select opportunityColumns;

 

Hope this helps

Shiras

 

 

---

shiras abdulrahman

Answer 13

hi,

 

First u can create a seperate class with the values that u need to obtain.

eg:

public class sample

{

public decimal AB1{get;set;}

public decimal AB2{get;set;}

public decimal AB3{get;set;}

}

then in the use these class to get those variables as

Iennumerable<sample> val = from m in dt.AsEnumerable()

  where m.Field<string>("DiamondRangeName") == "H"

  select new sample{AB1= (decimal)m["ab1"], AB2 =(decimal) m["ab2"], AB3=(decimal) m["ab3"]};

 

kindly note:

ab1, ab2 , ab3 are the column name in data table.

and u can replace (decimal) as convert.todecimal(m["ab1"]) if any of the column value is null.

 

instead u can also use anonymous types as 

 

Iennumerable<object> val = from m in dt.AsEnumerable()

  where m.Field<string>("DiamondRangeName") == "H"

  select new {A1= m["ab1"], A2 =m["ab2"], A3= m["ab3"]};

 

note here A1,A2,A3 are anonymous types.

 

Thanks,

Sam_P