none
problem converting my SQL (dateadd, datediff) RRS feed

  • Question

  • This is my SQL Query:

    SELECT DATEADD(month, DATEDIFF(month, 0, PrestatieZendingOntvangen), 0) AS TimeSpan, COUNT(RecordID) AS Count
    FROM bam_Prestatie_AllInstances
    GROUP BY DATEADD(month, DATEDIFF(month, 0, PrestatieZendingOntvangen), 0)
    ORDER BY hour

    but I need it as LINQ and I have no idea how to handle the dateadd, datediff. Is there anyone who can translate this?
    Wednesday, July 8, 2009 11:09 AM

Answers

  • If it is nullable, use the .Value and .HasValue properties as necessary.

    E.g. :

    pai.PrestatieZendingOntvangen.Value.Date

    ...and/or...

    (pai.PrestatieZendingOntvangen.HasValue ? pai.PrestatieZendingOntvangen.Value : defaultDate)

    ...and/or...

    ((em.HireDate ?? DateTime.Today) - (em.HireDate ?? DateTime.Today).Date).TotalHours
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by WtFudgE Friday, July 10, 2009 9:46 AM
    Thursday, July 9, 2009 1:08 PM
    Answerer

All replies

  • You can use [almost all] the normal the normal .net DateTime object and TimeSpan object functionality to do datediffs, dateadds etc. L2S will translate into the TSQL equivalents.

    The query above translates roughly to:

    from pai in dc.PrestatieAllInstances
    group pai by new DateTime(pai.HireDate.Year, pai.HireDate.Month, 1) into pg
    select new { ts = pg.Key, count = pg.Count() };
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Wednesday, July 8, 2009 12:55 PM
    Answerer
  • It's not working, this query u give only gives one record. A date and the total count.

    I need the count of records for EACH hour. And my query does that so it seems
    Wednesday, July 8, 2009 1:57 PM
  • In that case you have probably left out some portions of your original query.
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Wednesday, July 8, 2009 2:22 PM
    Answerer
  • Well obviously, but if I knew how I wouldn't be here right?
    Wednesday, July 8, 2009 2:48 PM
  • Ok, so let's try to figure out what you want to do....

    In your original query you use DATEADD(month, DATEDIFF(month, 0, PrestatieZendingOntvangen), 0) for grouping. The result of that is a datetime set to the year, month, and first day of month of the date in PrestatieZendingOntvangen. The effect of this is that the query in your original post will group by year and month. Based on this, I replied with a query that does the same, minus the "order by hour" which would make the original SQL query fail since there is no 'hour' field in the groupings or aggregations involved.

    Now, if you instead want to change that to group by hour instead of month, try something along the lines:

    from pai in dc.PrestatieAllInstances
    group pai by (pai.PrestatieZendingOntvangen - pai.PrestatieZendingOntvangen.Date).TotalHours into pg
    orderby pg.Key
    select new { pg.Key, count = pg.Count() };
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Wednesday, July 8, 2009 3:21 PM
    Answerer
  • Ok i seem to have told it wrong, in my query it's supposed to count all the records for each month, if i replace month with hour it does this for each hour. The thing is I need both so it doesn't matter, but let's keep it with months for now. So let's say I want to count all the records or each month like my query does.
    Your linq code doesn't work, i can't use .Date on PrestatieZendingOntvangen, neither can I use .Year or .Month. although the column is a "datetime?", problam is probably becuz of the nullable, so how to fix?

    I tried doing this:

    var items = from pai in ctx.bam_Prestatie_AllInstances
                      group pai by new DateTime(((DateTime)pai.PrestatieZendingOntvangen).Year, ((DateTime)pai.PrestatieZendingOntvangen).Month, 01) into pg
                      orderby pg.Key
                      select new { pg.Key, count = pg.Count() };

    but it says, the group by function uses an expression that can't be translated although it works when i do it non linq with test dates.
    The same goes for the other one:

    var items = from pai in ctx.bam_Prestatie_AllInstances
                      group pai by (pai.PrestatieZendingOntvangen - ((DateTime)pai.PrestatieZendingOntvangen).Date) into pg
                      orderby pg.Key
                      select new { pg.Key, count = pg.Count() };


    • Edited by WtFudgE Thursday, July 9, 2009 7:28 AM
    Thursday, July 9, 2009 7:12 AM
  • If it is nullable, use the .Value and .HasValue properties as necessary.

    E.g. :

    pai.PrestatieZendingOntvangen.Value.Date

    ...and/or...

    (pai.PrestatieZendingOntvangen.HasValue ? pai.PrestatieZendingOntvangen.Value : defaultDate)

    ...and/or...

    ((em.HireDate ?? DateTime.Today) - (em.HireDate ?? DateTime.Today).Date).TotalHours
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by WtFudgE Friday, July 10, 2009 9:46 AM
    Thursday, July 9, 2009 1:08 PM
    Answerer
  • Great! It's finally working. Thanks a lot my friend, you've been a big help, couldn't do it without u.
    Thursday, July 9, 2009 1:48 PM
  • Great! It's finally working. Thanks a lot my friend, you've been a big help, couldn't do it without u.

    Glad to hear it worked out. :)
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by WtFudgE Friday, July 10, 2009 9:46 AM
    • Unmarked as answer by WtFudgE Friday, July 10, 2009 9:46 AM
    Friday, July 10, 2009 3:21 AM
    Answerer