locked
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!!!!





    Tuesday, April 26, 2011 5:32 AM

Answers

  • 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".
    Wednesday, April 27, 2011 1:40 PM

All replies

  • 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".
    Tuesday, April 26, 2011 7:28 AM
  • 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...

     



    Tuesday, April 26, 2011 7:47 AM
  • 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".
    • Edited by Tim TangMVP Tuesday, April 26, 2011 8:35 AM ,
    Tuesday, April 26, 2011 8:34 AM
  • 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... 

    Tuesday, April 26, 2011 9:11 AM
  • 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".
    Tuesday, April 26, 2011 3:01 PM
  • 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...

     

     

     

    Wednesday, April 27, 2011 6:04 AM
  • 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????????? 

    Wednesday, April 27, 2011 6:33 AM
  • 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".
    Wednesday, April 27, 2011 1:40 PM
  • 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.

    Wednesday, April 27, 2011 2:11 PM
  • 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???

    Friday, April 29, 2011 10:12 AM
  • 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".
    Friday, April 29, 2011 1:30 PM
  • 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
    • Proposed as answer by shiras Wednesday, May 04, 2011 3:26 PM
    Wednesday, May 04, 2011 3:26 PM
  • 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

    Wednesday, June 15, 2011 5:18 AM