none
Linq SQL Query RRS feed

  • Question

  • Hi Experts, 

    I have data table that get loaded from a csv. Could anyone please help with an equivalent linq query for the below SQL query to group the result of the data table.  The grouped result set need to be in Datatable type only so I can do bulk insert into my DB table.

            var dt = new DataTable();
                dt.Columns.Add(Activity_id, typeof(string));
                dt.Columns.Add(First_Name, typeof(string));
                dt.Columns.Add(Salary, typeof(Int32));
                dt.Columns.Add(SalaryDate, typeof(DateTime));
    
    
    	SELECT Activity_id,Max(First_Name) AS First_Name, SUM(Salary) AS Salary, Max(SalaryDate) AS SalaryDate
    			FROM DT
    			GROUP BY Activity_id

    Thanks & regards

    Priya


    • Edited by Priya Bange Thursday, December 19, 2019 7:29 AM .
    Thursday, December 19, 2019 7:27 AM

Answers

  • Working with DataTables is not the best-performing solution. Classes with properties would be better.

    But it would work that way:

    var result =
        from row in dt.AsEnumerable()
        group row by row["Activity_id"] into g
        select new
        {
            Activity_id = g.Key,
            First_Name = g.Max(r => r["First_Name"]),
            Salary = g.Sum(r => (Int32)r["Salary"]),
            SalaryDate = g.Max(r => (DateTime)r["SalaryDate"])
        };


    • Marked as answer by Priya Bange Friday, December 20, 2019 4:34 AM
    Thursday, December 19, 2019 9:49 AM
  • Hi Priya,

    Thank you for posting here.

    According to your description, you want to convert this SQL statement into a Linq statement with the same function.

    Try the following code to get it.

            static void Main(string[] args)
            {
                using (SqlConnection sqlConnection = new SqlConnection(@"ConnString")) 
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand("select * from dt", sqlConnection))
                    {
                        DataSet dataSet = new DataSet();
                        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                        sqlDataAdapter.SelectCommand = sqlCommand;
                        sqlDataAdapter.Fill(dataSet);
    
                        DataTable dataTable = dataSet.Tables[0];
    
                        var result = from row in dataTable.AsEnumerable()
                                group row by row.Field<string>("Activity_id") into newGrope
                                select new
                                {
                                    Active_id = newGrope.Key,
                                    First_Name = newGrope.Max(x=>x.Field<string>("First_Name")),
                                    Salary = newGrope.Sum(x=>x.Field<Int32>("Salary")),
                                    SalaryDate = newGrope.Max(x=>x.Field<DateTime>("SalaryDate"))
                                };
                        var dt = new DataTable();
                        dt.Columns.Add("Activity_id", typeof(string));
                        dt.Columns.Add("First_Name", typeof(string));
                        dt.Columns.Add("Salary", typeof(Int32));
                        dt.Columns.Add("SalaryDate", typeof(DateTime));
    
                        foreach (var item in result)
                        {
                            dt.Rows.Add(item.Active_id, item.First_Name, item.Salary, item.SalaryDate);
                        }
    
                    }
                }
    
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Priya Bange Friday, December 20, 2019 4:34 AM
    Thursday, December 19, 2019 9:51 AM

All replies

  • Working with DataTables is not the best-performing solution. Classes with properties would be better.

    But it would work that way:

    var result =
        from row in dt.AsEnumerable()
        group row by row["Activity_id"] into g
        select new
        {
            Activity_id = g.Key,
            First_Name = g.Max(r => r["First_Name"]),
            Salary = g.Sum(r => (Int32)r["Salary"]),
            SalaryDate = g.Max(r => (DateTime)r["SalaryDate"])
        };


    • Marked as answer by Priya Bange Friday, December 20, 2019 4:34 AM
    Thursday, December 19, 2019 9:49 AM
  • Hi Priya,

    Thank you for posting here.

    According to your description, you want to convert this SQL statement into a Linq statement with the same function.

    Try the following code to get it.

            static void Main(string[] args)
            {
                using (SqlConnection sqlConnection = new SqlConnection(@"ConnString")) 
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = new SqlCommand("select * from dt", sqlConnection))
                    {
                        DataSet dataSet = new DataSet();
                        SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
                        sqlDataAdapter.SelectCommand = sqlCommand;
                        sqlDataAdapter.Fill(dataSet);
    
                        DataTable dataTable = dataSet.Tables[0];
    
                        var result = from row in dataTable.AsEnumerable()
                                group row by row.Field<string>("Activity_id") into newGrope
                                select new
                                {
                                    Active_id = newGrope.Key,
                                    First_Name = newGrope.Max(x=>x.Field<string>("First_Name")),
                                    Salary = newGrope.Sum(x=>x.Field<Int32>("Salary")),
                                    SalaryDate = newGrope.Max(x=>x.Field<DateTime>("SalaryDate"))
                                };
                        var dt = new DataTable();
                        dt.Columns.Add("Activity_id", typeof(string));
                        dt.Columns.Add("First_Name", typeof(string));
                        dt.Columns.Add("Salary", typeof(Int32));
                        dt.Columns.Add("SalaryDate", typeof(DateTime));
    
                        foreach (var item in result)
                        {
                            dt.Rows.Add(item.Active_id, item.First_Name, item.Salary, item.SalaryDate);
                        }
    
                    }
                }
    
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }

    Hope this could be helpful.

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Priya Bange Friday, December 20, 2019 4:34 AM
    Thursday, December 19, 2019 9:51 AM
  • Seems that if you are intending to use SqlBulkCopy.WriteToServer stick with a DataTable. Translating from current code to grouping with LINQ will need to have the result set be strongly typed for SqlBulkCopy (that is assuming this is the intention) and iterated which will take more time than a single method.

    DataTable containers have their place while many times using concrete classes is the better choice, its dependent on the task.

    Lastly, if the intent is to simply to try out LINQ this is the wrong place.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, December 19, 2019 10:28 AM
    Moderator