CODEWITHSUNDEEP
c#linq-to-sql
hughesdan
hughesdan

Reputation: 2851

How do I group query results by calendar month and other variables using Linq2Sql?

I have a Billing table in my database that contains daily accruals of amounts owed by users based on their usage of the system. It also contains records of payments received. For example...

BillingID   UserID  Type    Date        Description                 Amount
27298   228 D   11/10/2011 12:00:00 AM  Protection Plan             0.2500
27299   228 D   11/10/2011 12:00:00 AM  Storage Subscription Fee    1.6333
27300   250 D   11/10/2011 12:00:00 AM  Storage Subscription Fee    7.9333
27301   253 D   11/10/2011 12:00:00 AM  Storage Subscription Fee    7.9333
27302   254 D   11/10/2011 12:00:00 AM  Storage Subscription Fee    1.6333
27303   255 D   11/10/2011 12:00:00 AM  Storage Subscription Fee    1.6333
27304   257 D   11/10/2011 12:00:00 AM  Storage Subscription Fee    3.3000
27305   262 D   11/10/2011 12:00:00 AM  Storage Subscription Fee    0.0333
27306   265 D   11/10/2011 12:00:00 AM  Storage Subscription Fee    0.9667
27307   266 D   11/10/2011 12:00:00 AM  Storage Subscription Fee    0.9667
27308   228 D   11/10/2011 12:00:00 AM  Subscription Overage        0.0832
27309   228 D   11/11/2011 12:00:00 AM  Protection Plan             0.2500
27310   228 D   11/11/2011 12:00:00 AM  Storage Subscription Fee    1.6333
27311   250 D   11/11/2011 12:00:00 AM  Storage Subscription Fee    7.9333
27312   253 D   11/11/2011 12:00:00 AM  Storage Subscription Fee    7.9333

The data above is not real but it's representative of what I'm working with. My objective is to write a Linq to Sql query that summarizes this data in an understandable way for the user. Here's an example of the desired output:

User      Month     Type     Description               Amount
228       November   D       Protection Plan            $10   
228       November   D       Storage Subscription Fee   $49
228       November   D       Subscription Overage        $5
228       November   C       Payment                   ($54)
228       December   D       Protection Plan            $10
...

I've been able to accomplish part of what I need with the following code:

BusinessLogic.cs File:

 public static IEnumerable<Billing> GetBillingHistory(int userid, DateTime startDate, DateTime endDate)
    {
        SBMData2.SBMDataContext db = new SBMData2.SBMDataContext();

        return from b in db.Billings
               where b.UserID == userid && b.Date >= startDate && b.Date <= endDate
               select

 new Billing();

        }

Code Behind for User Account Page:

protected void Page_Load(object sender, EventArgs e)
{
    int userID = foo.Data.User.GetIDFromUserName(HttpContext.Current.User.Identity.Name);
    DateTime endDate = DateTime.Now;
    DateTime startDate = endDate.AddMonths(-3);
    RadGrid2.DataSource = BusinessLogic.GetBillingHistory(userID, startDate, endDate);
    RadGrid2.DataBind();

}

The result of this code is a nice grid presented to the user that shows transactions accrued during the last 90 days for that particular user. How do I modify my Linq query to summarize the results by calendar month, type and description as described in the "desired output" section above? I've been working on this for a long time and am really stumped. Any help or guidance would be greatly appreciated.

Upvotes: 0

Views: 119

Answers (2)

Murph
Murph

Reputation: 10190

Grouping in Linq is the one thing that I don't find easy or obvious:

This I have tested (Linqpad is a wonderful toy!)

from b in Billings
group b by new { b.UserID, b.Type, Month = b.Date.Value.Month, b.Description } into groupItem
select new 
{
    groupItem.Key.UserID,
    groupItem.Key.Type,
    groupItem.Key.Month,
    groupItem.Key.Description,
    Total = groupItem.Sum (b => b.Amount)
}

Converting month number to month name for display is an implementation detail (-:

I look at this and think that it may be possible to express it more elegantly - I think I'm probably missing something in terms of tidy syntax, but its doing what its supposed to do.

Upvotes: 1

user240141
user240141

Reputation:

I have'nt tested it on machine. You need to play with. But it might work, i think.

var listInfo = (from i in context.Billings
                where i.UsreId== id
                group i by new
                { i.UsreId, i.Type, i.DateTime } into newInfo
                select new 
                {
                   UserId,
                   Month,
                   Type,
                   Description,
                   Total= Amount.Count()

                }).AsEnumerable();

Upvotes: 1

Related Questions