Jagajaga Bagabaga Jagajaga Bagabaga - 2 months ago 6
C# Question

Linq query using Group by and Joins

I have two tables:

Banner

--------------------------------------------------------|
BId | Name | Link | Image | AltText| Target | BActive|
--------------------------------------------------------
|1 | hello| http| a.jpg |helloimg| | 1 |
--------------------------------------------------------


Tracking

------------------------------------------------------
|TId | BId| ICount| CCount| CreateDate |
------------------------------------------------------
|1 | 1 | 102 | 300 | 2015-11-17 00:00:000 |
|2 | 1 | 182 | 100 | 2015-11-14 00:00:000 |
|3 | 1 | 192 | 200 | 2015-11-12 00:00:000 |
------------------------------------------------------


I want to find the Sum of ICount and CCound for each BId between 2015-11-12 and 2015-11-15.

Here is the code I have tried:

from p in Bannertables
join q in BannerTrackings
on p.BannerId equals q.BannerId
group p by p.BannerId into myTab
select new {
BannerId = myTab.Key,
ImpressionCount = myTab.Sum( x=> x.ImpressionCount),
ClickCount = myTab.Sum(x => x.ClickCount)
};


How do I sort the result according to the date range I specified?

Answer

Add the followingwhere to your query:

var d1 = new DateTime(2015, 11, 12);
var d2 = new DateTime(2015, 11, 15);
var query=from p in Bannertables
          join q in BannerTrackings
          on p.BannerId equals q.BannerId
          where q.CreateDate>=d1 && q.CreateDate<=d2
          group q by p.BannerId into myTab
          select new { 
                       BannerId = myTab.Key,
                       ImpressionCount = myTab.Sum( x=> x.ImpressionCount),
                       ClickCount = myTab.Sum(x => x.ClickCount)
                      };

Update

If you want to get access to the Banner's properties, include it in the group as I show below:

var d1 = new DateTime(2015, 11, 12);
var d2 = new DateTime(2015, 11, 15);
var query=from p in Bannertables
          join q in BannerTrackings
          on p.BannerId equals q.BannerId
          where q.CreateDate>=d1 && q.CreateDate<=d2
          group new{Banner=p,Tracking=q} by p.BannerId into myTab
          select new { 
                       BannerId = myTab.Key,
                       Bannername=myTab.Select(e=>e.Banner.Name).FirstOrDefault(),
                       ImpressionCount = myTab.Sum( x=> x.Tracking.ImpressionCount),
                       ClickCount = myTab.Sum(x => x.Tracking.ClickCount)
                      };

But if you only want the banner name this is even better:

var d1 = new DateTime(2015, 11, 12);
var d2 = new DateTime(2015, 11, 15);
var query=from p in Bannertables
          join q in BannerTrackings
          on p.BannerId equals q.BannerId
          where q.CreateDate>=d1 && q.CreateDate<=d2
          group q by new {p.BannerId,p.Name} into myTab
          select new { 
                       BannerId = myTab.Key.BannerId,
                       BannerName=myTab.Key.Name,
                       ImpressionCount = myTab.Sum( x=> x.ImpressionCount),
                       ClickCount = myTab.Sum(x => x.ClickCount)
                      };