locked
GroupBy question.... i think!!! RRS feed

  • Question

  • Hi

    I am writing a program in C# that uses a SQL compact database and LINQ to Entities.

    the database consists of 1 table and 4 columns of which one is a DateTime and one is OrderAmount.

    i want to retreive the total sales over a user defined date range on a particular day of the week.  For example i might want to retreive the total sales acheived each Saturday over the last 6 months.

    I would be gratefull if anyone could point me in the right direction.

    James

    Sunday, October 3, 2010 6:13 PM

Answers

  • DateTime nearestSaturday = DateTime.Now.AddDays(DayOfWeek.Saturday - DateTime.Now.DayOfWeek).Date;
    DateTime startDate = new DateTime(2010, 1, 1);
    DateTime endDate = new DateTime(2010, 12, 31, 23, 59, 59);

    var orderTotal = oc.Orders
      .Where(ord => ord.OrderDate >= startDate && ord.OrderDate <= endDate && EntityFunctions.DiffDays(ord.OrderDate, nearestSaturday) % 7 == 0)
      .Sum(o => o.OrderAmount);

    The reason for that DiffDays hoopla is that it appears as if L2E doesn't support translating ord.OrderDate.DayOfWeek == DayOfWeek.Saturday into T-SQL. Maybe ESQL has a weekday equivalent, not sure about that though...

    If you want to get the sum per saturday (the above example gets the total for all saturdays) then you can do something like:

    var orderTotal = oc.Orders
      .Where(ord => ord.OrderDate >= startDate && ord.OrderDate <= endDate && EntityFunctions.DiffDays(ord.OrderDate, nearestSaturday) % 7 == 0)
      .GroupBy(og => og.Orderdate)
      .Select(o => new { OrderDate = o.Key, OrderAmount = o.Sum(oa => (decimal?)oa.OrderAmount) });


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by KristoferA Tuesday, October 5, 2010 3:27 AM
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:48 AM
    Monday, October 4, 2010 10:08 AM
  • Strange... SQL CE supports the TSQL DateDiff function, but maybe the SQL CE provider for EF doesn't.

    One alternative would be to just filter on the date range and do the aggregate in the db query, materialize, and then apply the weekday filter as a linq-to-objects query on the result. It will retrieve a more data from the database than if you apply the weekday filter db-side, but since a SQL CE database is local anyway it will maybe not make a huge difference.

    E.g.:

    var orderTotal = oc.Orders
      .Where(ord => ord.OrderDate >= startDate && ord.OrderDate <= endDate)
      .GroupBy(og => og.Orderdate)
      .Select(o => new { OrderDate = o.Key, OrderAmount = o.Sum(oa => (decimal?)oa.OrderAmount) })
      .ToList();

    var saturdayTotal = orderTotal.Where(o => o.OrderDate.DayOfWeek == DayOfWeek.Saturday).ToList();


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by KristoferA Tuesday, October 5, 2010 3:27 AM
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:48 AM
    Tuesday, October 5, 2010 3:27 AM
  • Hi,

    To use DayOfWeek you can use an Extenssion like is described here:

    http://solidcoding.blogspot.com/2007/11/using-linq-extension-methods.html

    Hope this can help you,

    JAReyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    • Proposed as answer by KristoferA Tuesday, October 5, 2010 3:28 AM
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:48 AM
    Monday, October 4, 2010 12:45 PM

All replies

  • DateTime nearestSaturday = DateTime.Now.AddDays(DayOfWeek.Saturday - DateTime.Now.DayOfWeek).Date;
    DateTime startDate = new DateTime(2010, 1, 1);
    DateTime endDate = new DateTime(2010, 12, 31, 23, 59, 59);

    var orderTotal = oc.Orders
      .Where(ord => ord.OrderDate >= startDate && ord.OrderDate <= endDate && EntityFunctions.DiffDays(ord.OrderDate, nearestSaturday) % 7 == 0)
      .Sum(o => o.OrderAmount);

    The reason for that DiffDays hoopla is that it appears as if L2E doesn't support translating ord.OrderDate.DayOfWeek == DayOfWeek.Saturday into T-SQL. Maybe ESQL has a weekday equivalent, not sure about that though...

    If you want to get the sum per saturday (the above example gets the total for all saturdays) then you can do something like:

    var orderTotal = oc.Orders
      .Where(ord => ord.OrderDate >= startDate && ord.OrderDate <= endDate && EntityFunctions.DiffDays(ord.OrderDate, nearestSaturday) % 7 == 0)
      .GroupBy(og => og.Orderdate)
      .Select(o => new { OrderDate = o.Key, OrderAmount = o.Sum(oa => (decimal?)oa.OrderAmount) });


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by KristoferA Tuesday, October 5, 2010 3:27 AM
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:48 AM
    Monday, October 4, 2010 10:08 AM
  • Thanks for your reply Kristofer.

    Am getting the following Exception though:

     "The function is not recognized by SQL Server Compact Edition. [ Name of function = DIFFDAYS,Data type (if known) =  ]"

    Im using a SQL Compact Edition database and .Net 4.0

    Any other ideas?

    James

     

    Monday, October 4, 2010 12:31 PM
  • Hi,

    To use DayOfWeek you can use an Extenssion like is described here:

    http://solidcoding.blogspot.com/2007/11/using-linq-extension-methods.html

    Hope this can help you,

    JAReyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    • Proposed as answer by KristoferA Tuesday, October 5, 2010 3:28 AM
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:48 AM
    Monday, October 4, 2010 12:45 PM
  • Strange... SQL CE supports the TSQL DateDiff function, but maybe the SQL CE provider for EF doesn't.

    One alternative would be to just filter on the date range and do the aggregate in the db query, materialize, and then apply the weekday filter as a linq-to-objects query on the result. It will retrieve a more data from the database than if you apply the weekday filter db-side, but since a SQL CE database is local anyway it will maybe not make a huge difference.

    E.g.:

    var orderTotal = oc.Orders
      .Where(ord => ord.OrderDate >= startDate && ord.OrderDate <= endDate)
      .GroupBy(og => og.Orderdate)
      .Select(o => new { OrderDate = o.Key, OrderAmount = o.Sum(oa => (decimal?)oa.OrderAmount) })
      .ToList();

    var saturdayTotal = orderTotal.Where(o => o.OrderDate.DayOfWeek == DayOfWeek.Saturday).ToList();


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by KristoferA Tuesday, October 5, 2010 3:27 AM
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:48 AM
    Tuesday, October 5, 2010 3:27 AM
  • Hi Kristofer

    Im getting closer!  the following code you gave me compiles without error and lists all the transactions on every Monday (27/09/2010 & 04/10/2010) but it does not give me the sum of transaction amounts for the Monday. Rather it list all the indivudal transactions.

    Have not had mush experiance with the Method syntax! Have i missed somthing?

     

     

    var orderTotal = context.Transactions
    .Where(ord => ord.DateTime >= startDate && ord.DateTime <= endDate)
    .GroupBy(og => og.DateTime)
    .Select(o =>
    new { DateTime = o.Key, PricePaid = o.Sum(oa => (decimal?)oa.PricePaid) })
    .ToList();

     

     

    var saturdayTotal = orderTotal.Where(o => o.DateTime.DayOfWeek == DayOfWeek.Monday).ToList();

     

     

    StringBuilder sb = new StringBuilder();

     

     

    foreach (var x in saturdayTotal)
    {
    sb.AppendLine(x.ToString());
    }

    textBox1.Text = sb.ToString();

     

    Output from TextBox1:

    { DateTime = 27/09/2010 19:03:15, PricePaid = 8 }
    { DateTime = 27/09/2010 19:03:17, PricePaid = 4 }
    { DateTime = 27/09/2010 19:03:19, PricePaid = 6 }
    { DateTime = 27/09/2010 19:03:20, PricePaid = 6 }
    { DateTime = 27/09/2010 19:03:20, PricePaid = 6 }
    { DateTime = 27/09/2010 19:03:22, PricePaid = 48 }
    { DateTime = 27/09/2010 19:03:23, PricePaid = 14 }
    { DateTime = 27/09/2010 19:03:23, PricePaid = 15 }
    { DateTime = 27/09/2010 19:03:24, PricePaid = 10 }
    { DateTime = 27/09/2010 19:03:26, PricePaid = 25 }
    { DateTime = 27/09/2010 19:05:05, PricePaid = 7 }
    { DateTime = 27/09/2010 19:05:22, PricePaid = 8 }
    { DateTime = 27/09/2010 19:05:38, PricePaid = 7 }
    { DateTime = 27/09/2010 19:10:34, PricePaid = 8 }
    { DateTime = 27/09/2010 19:13:31, PricePaid = 9 }
    { DateTime = 27/09/2010 19:44:04, PricePaid = 8 }
    { DateTime = 27/09/2010 19:45:02, PricePaid = 15 }
    { DateTime = 27/09/2010 21:00:11, PricePaid = 10 }
    { DateTime = 27/09/2010 21:04:08, PricePaid = 16 }
    { DateTime = 27/09/2010 21:04:55, PricePaid = 6 }
    { DateTime = 27/09/2010 21:08:33, PricePaid = 25 }
    { DateTime = 27/09/2010 21:09:57, PricePaid = 9 }
    { DateTime = 27/09/2010 21:18:59, PricePaid = 9 }
    { DateTime = 27/09/2010 21:19:01, PricePaid = 8 }
    { DateTime = 27/09/2010 21:19:01, PricePaid = 7 }
    { DateTime = 27/09/2010 21:19:02, PricePaid = 3 }
    { DateTime = 27/09/2010 21:19:03, PricePaid = 5 }
    { DateTime = 27/09/2010 21:19:11, PricePaid = 36 }
    { DateTime = 27/09/2010 21:22:23, PricePaid = 20 }
    { DateTime = 27/09/2010 21:22:30, PricePaid = 3 }
    { DateTime = 27/09/2010 21:26:13, PricePaid = 9 }
    { DateTime = 27/09/2010 21:26:15, PricePaid = 8 }
    { DateTime = 27/09/2010 21:26:16, PricePaid = 7 }
    { DateTime = 27/09/2010 21:26:16, PricePaid = 2 }
    { DateTime = 27/09/2010 21:26:17, PricePaid = 3 }
    { DateTime = 27/09/2010 21:26:19, PricePaid = 15 }
    { DateTime = 27/09/2010 21:26:56, PricePaid = 7 }
    { DateTime = 27/09/2010 21:28:55, PricePaid = 9 }
    { DateTime = 27/09/2010 21:29:27, PricePaid = 8 }
    { DateTime = 27/09/2010 21:31:35, PricePaid = 8 }
    { DateTime = 27/09/2010 21:31:51, PricePaid = 9 }
    { DateTime = 27/09/2010 21:52:46, PricePaid = 8 }
    { DateTime = 27/09/2010 21:53:03, PricePaid = 7 }
    { DateTime = 27/09/2010 21:53:04, PricePaid = 8 }
    { DateTime = 27/09/2010 21:53:30, PricePaid = 25 }
    { DateTime = 04/10/2010 14:56:08, PricePaid = 8 }
    { DateTime = 04/10/2010 14:56:10, PricePaid = 7 }
    { DateTime = 04/10/2010 14:56:11, PricePaid = 8 }
    { DateTime = 04/10/2010 14:56:12, PricePaid = 3 }

    Tuesday, October 5, 2010 9:35 AM
  • Sorry, I guess I didn't pay attention. If the order date column also has time, you need to strip the time from it. E.g.:

    .GroupBy(og => EntityFunctions.TruncateTime(og.OrderDate))


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    Tuesday, October 5, 2010 9:47 AM
  • It doesnt seem like TruncateTime is supported by SQL Compact either!

    Unless you have an alternative i am thinking it may be easier to just create another column in the DB and seperate the "Date" and "Time" elements?

    Wednesday, October 6, 2010 3:48 PM
  • Hi,

    I think it is easier to use og.OrderDate.Date instead of TruncateTime, but ensure before than OrderDate is not null.

    Best regards,

    JAReyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    Wednesday, October 6, 2010 5:04 PM