CODEWITHSUNDEEP
c#linq
aj12
aj12

Reputation: 289

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?

Upvotes: 0

Views: 59

Answers (1)

ocuenca
ocuenca

Reputation: 39326

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)
                      };

Upvotes: 1

Related Questions