none
Query - Group By Date RRS feed

  • Question

  • I've a RevenueTax query, pulls data from Start Date-End Date, something like this... 

     

    Result

    =====

    Date Rent       Tax LineTotal

    1/1/2011   $100      $10.23 $110.23

    1/2/2011 $200     $20.46 $220.46

    1/2/2011   $100     $10.23 $110.23

     

    I would like to group records by date and consolidate totals (Rent, Tax & LineTotal). 

     

    Date Rent    Tax LineTotal

    1/1/2011 $100  $10.23   $110.23

    1/2/2011 $300   $30.69  $330.69

     

     

    I'm not quite getting Linq grouping here.  Could someone please walk me through with sample?


    -Bala
    • Edited by PowerBala Sunday, December 11, 2011 6:36 AM
    Sunday, December 11, 2011 6:35 AM

Answers

  • Hi PowerBala;

    OK then you will need to add a reference to the dll System.Data.Enty.dll and add the using statement, using System.Data.Objects.SqlClient;. Once that is done you need to modify the query as follows.

    var results = from t in table
                  group t by new { Month = SqlFunctions.DatePart("mm", t.Date),
                             Day = SqlFunctions.DatePart("dd", t.Date),
                             Year = SqlFunctions.DatePart("yyyy", t.Date) }
                             into tGroup  
                  orderby tGroup.Key
                  select new
                  {
                        Date = tGroup.Key,
                        TotalRent = tGroup.Sum (r => r.Rent ),
                        TotalTax = tGroup.Sum (t => t.Tax ),
                        TotalLineTotal = tGroup.Sum (lt => lt.LineTotal )
                  };
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by PowerBala Sunday, December 11, 2011 9:04 PM
    Sunday, December 11, 2011 3:45 PM

All replies

  • Hi PowerBala;

    The following query will do what you want assuming that the ObjectContext instance name is objContext and the table name is table. The results of the query outputs the information in the date order from earliest to the latest. If you do not want to order them just remove the orderby line in the query.

    var results = from t in objContext.table
                  group t by t.Date into tGroup
                  orderby tGroup.Key
                  select new
                  {
                        Date = tGroup.Key,
                        TotalRent = tGroup.Sum (r => r.Rent ),
                        TotalTax = tGroup.Sum (t => t.Tax ),
                        TotalLineTotal = tGroup.Sum (lt => lt.LineTotal )
                  };
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, December 11, 2011 2:40 PM
  • Thanks Fernando.  It works partially.  Date field is actually a DateTime field, it groups by time.  Is there a way to come around this?

     

    This is my latest result

     TransDate TotalRent TotalTax

    12/1/2011 11:31:33 PM 10.600000000000   0.000000000000

    12/1/2011 11:31:33 PM 29.990000000000   3.898700000000

    12/11/2011 9:44:49 AM 10.000000000000  0.000000000000

    12/11/2011 9:44:49 AM   119.970000000000   15.596100000000

    12/11/2011 9:46:00 AM   10.000000000000    0.000000000000

    12/11/2011 9:46:00 AM    149.950000000000   19.493500000000

    12/11/2011 9:46:45 AM    99.970000000000     11.696100000000

     


    -Bala

    • Edited by PowerBala Sunday, December 11, 2011 2:57 PM
    Sunday, December 11, 2011 2:54 PM
  • Hi PowerBala;

    OK then you will need to add a reference to the dll System.Data.Enty.dll and add the using statement, using System.Data.Objects.SqlClient;. Once that is done you need to modify the query as follows.

    var results = from t in table
                  group t by new { Month = SqlFunctions.DatePart("mm", t.Date),
                             Day = SqlFunctions.DatePart("dd", t.Date),
                             Year = SqlFunctions.DatePart("yyyy", t.Date) }
                             into tGroup  
                  orderby tGroup.Key
                  select new
                  {
                        Date = tGroup.Key,
                        TotalRent = tGroup.Sum (r => r.Rent ),
                        TotalTax = tGroup.Sum (t => t.Tax ),
                        TotalLineTotal = tGroup.Sum (lt => lt.LineTotal )
                  };
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by PowerBala Sunday, December 11, 2011 9:04 PM
    Sunday, December 11, 2011 3:45 PM
  •  

    Did that work out for you.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, December 11, 2011 5:23 PM
  • Fernando,

    I actually found another post in StackOverflow with EntityFunctions.TruncateTime function.  But unfortunately, I couldn't check the output by both ways, as I couldn't return an anonymous class.  I'm using VS LightSwitch, so I need to return the same Entity back.  I'm working on it and update this post as it goes.

     

     

    				query = from t in query
    						 group t by EntityFunctions.TruncateTime(t.TransDate) into tGroup
    						 orderby tGroup.Key
    						 select new
    						 {
    							 TransDate = (DateTime)tGroup.Key,
    							 Amount = tGroup.Sum(r => r.Amount),
    							 Tax = tGroup.Sum(t => t.Tax)
    						 };

     


    -Bala
    Sunday, December 11, 2011 5:29 PM
  • Hi Bala;

    Hard to keep up with all the different extension that are added, but anyway I did find that method in the documentation.

    To your statement, "I need to return the same Entity back", Linq to Entity Framework will not allow you to construct a entity of the same type that you are qqueryingunless you are rreturningthe entity unchanged.

    Please let me know what you are attempting to accomplish.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, December 11, 2011 8:44 PM
  • Thanks for that info Fernando.

    In Visual Studio LightSwitch, I have a table "InvoiceLineItem" and a query "RevenueTax" which returns above result.  I also created a DevXpress XtraReports based on RevenueTax query (which returns IEnumarable of "InvoiceLineItem").  

     

    In order to print a monthly report with daily totals, I required the above linq query, but also I need the same entity to pass to XtraReports.  I have another option in LightSwitch is using RIA service.  However LightSwitch needs a ID field and I couldn't use the Date field (like in your Linq query) as an ID field.

    I still couldn't figured out but I guess that is LightSwitch's issue.  Thanks a lot for ur help. :)

     

    Appreciate it.


    -Bala
    Sunday, December 11, 2011 9:15 PM
  •  

    Not a problem, always glad to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, December 11, 2011 9:21 PM