none
Help with group by / Sum() query RRS feed

  • Question

  • I need a linq2Sql version of the followng T_SQL query, that I am not able to realize... :

    Thank you

    select Sum(DateDiff(hh, date_begin, date_closing)) diff, 
    month(date_closing), 
    year(date_closing)
    From table
    Group by month(date_closing), year(date_closing)

    Thursday, October 25, 2012 4:46 PM

Answers

  • Hi sukram;

    The following code snippet should do what you need.

    // You will need this using statement
    using System.Data.Linq.SqlClient;
    
    var ctx = new DataClasses1DataContext();
    
    var results = from t in ctx.table
                  group t by new {t.date_closing.Month, t.date_closing.Year}
                  into tGrouping
                  select new
                  {
                      diff = tGrouping.Select(d => new {d.date_begin, d.date_closing})
                             .Sum(d => SqlMethods.DateDiffHour(d.date_begin, d.date_closing)),
                      month = tGrouping.Key.Month,
                      year = tGrouping.Key.Year
                  };

      


    Fernando (MCSD)

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

    • Marked as answer by Markus Pöhler Friday, October 26, 2012 12:34 PM
    Friday, October 26, 2012 4:12 AM

All replies

  • Hi sukram;

    The following code snippet should do what you need.

    // You will need this using statement
    using System.Data.Linq.SqlClient;
    
    var ctx = new DataClasses1DataContext();
    
    var results = from t in ctx.table
                  group t by new {t.date_closing.Month, t.date_closing.Year}
                  into tGrouping
                  select new
                  {
                      diff = tGrouping.Select(d => new {d.date_begin, d.date_closing})
                             .Sum(d => SqlMethods.DateDiffHour(d.date_begin, d.date_closing)),
                      month = tGrouping.Key.Month,
                      year = tGrouping.Key.Year
                  };

      


    Fernando (MCSD)

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

    • Marked as answer by Markus Pöhler Friday, October 26, 2012 12:34 PM
    Friday, October 26, 2012 4:12 AM
  • Of course you can use answer above, Presented by Fernando Soto but when you are using SQL functions in your query you must remember that not all off them may be supported by your application layer.

    So sometimes you'd better create function from your query and use function in LINQ instead of building complex LINQ queries,


    Please mark as reply if helped.
    Also visit my blog http://msguy.net/

    Friday, October 26, 2012 11:47 AM
  • Thank you.

    I do not like LINQ Queries. After 15 years of writing T-SQL queries I am not able to get used to this syntax as soon these queries get a little more complicated - as this one does. And additionaly, if I look at the resulting T-SQL query that is being fired against SQL Server from the following query, I feel encouraged even more to better prepare the requred data as stored views or tables ON the sql server and query them with a very easy and not performance consuming linq queries wherever I need this data... Look at this:

    var results = from t in ctx.T_Table
                              where t.STATUS == 69 && t.TYPE != null
                              group t by new { t.DATE_CLOSING.Value.Month, t.DATE_CLOSING.Value.Year }
    into tGrouping 
    select new
                                  {
                                      AverageHours = tGrouping.Select(d => new { d.DATE_BEGIN, d.DATE_CLOSING }).Average(d => SqlMethods.DateDiffHour(d.DATE_BEGIN, d.DATE_CLOSING)),
                                      month = tGrouping.Key.Month,
                                      year = tGrouping.Key.Year,
                                      mj = new DateTime(tGrouping.Key.Year, tGrouping.Key.Month, 1)
                                  };
    

    The resulting query processed in SQL Server is:

    SELECT (
        SELECT AVG([t4].[value])
        FROM (
            SELECT DATEDIFF(Hour, [t3].[DATE_BEGIN], [t3].[DATE_CLOSING]) AS [value], [t3].[DATE_CLOSING], [t3].[STATUS], [t3].[TYPE]
            FROM [dbo].[v_elan_Incident] AS [t3]
            ) AS [t4]
        WHERE ((([t2].[value] IS NULL) AND (DATEPART(Month, [t4].[DATE_CLOSING]) IS NULL)) OR (([t2].[value] IS NOT NULL) AND (DATEPART(Month, [t4].[DATE_CLOSING]) IS NOT NULL) AND ((([t2].[value] IS NULL) AND (DATEPART(Month, [t4].[DATE_CLOSING]) IS NULL)) OR (([t2].[value] IS NOT NULL) AND (DATEPART(Month, [t4].[DATE_CLOSING]) IS NOT NULL) AND ([t2].[value] = DATEPART(Month, [t4].[DATE_CLOSING])))))) AND ((([t2].[value2] IS NULL) AND (DATEPART(Year, [t4].[DATE_CLOSING]) IS NULL)) OR (([t2].[value2] IS NOT NULL) AND (DATEPART(Year, [t4].[DATE_CLOSING]) IS NOT NULL) AND ((([t2].[value2] IS NULL) AND (DATEPART(Year, [t4].[DATE_CLOSING]) IS NULL)) OR (([t2].[value2] IS NOT NULL) AND (DATEPART(Year, [t4].[DATE_CLOSING]) IS NOT NULL) AND ([t2].[value2] = DATEPART(Year, [t4].[DATE_CLOSING])))))) AND ([t4].[STATUS] = 69) AND ([t4].[TYPE] IS NOT NULL)
        ) AS [AverageHours], [t2].[value] AS [month], [t2].[value2] AS [year], CONVERT(DATETIME, CONVERT(NCHAR(2), [t2].[value]) + ('/' + (CONVERT(NCHAR(2), @p1) + ('/' + CONVERT(NCHAR(4), [t2].[value2])))), 101) AS [mj]
    FROM (
        SELECT [t1].[value], [t1].[value2]
        FROM (
            SELECT DATEPART(Month, [t0].[DATE_CLOSING]) AS [value], DATEPART(Year, [t0].[DATE_CLOSING]) AS [value2], [t0].[STATUS], [t0].[TYPE]
            FROM [dbo].[T_TABLE] AS [t0]
            ) AS [t1]
        WHERE ([t1].[STATUS] = 69) AND ([t1].[TYPE] IS NOT NULL)
        GROUP BY [t1].[value], [t1].[value2]
        ) AS [t2]

    This query needs 9 seconds.

    The same result from this very easy query is returned in 0 secs:

    select AVG(DateDiff(hh, date_begin, date_closing)) AverageHours, [type],
    month(date_closing), year(date_closing)
    From [t_table]
    Where status = 69 AND [TYPE] is not null
    Group by [type], month(date_closing), year(date_closing)

    Friday, October 26, 2012 1:06 PM