CODEWITHSUNDEEP
c#linq-to-sqllinq-to-entities
Nick
Nick

Reputation: 5882

Grouping & counting records in LINQ to SQL while including all records from count

The following LINQ to Entities query gets all link clicks from an email, groups them by URL and projects them into a model.

The desired end result would be something like:

Clicks  |  URL     |  Clickers
-------------------------------------------------------------------------------------
2          x.com      User 1, User 2...
1          y.com      User 1

I have an object CampaignEmailRecipient which contains ALL users who clicked the link, which I'd also like to include in my model in property Clickers.

I'm just not sure how to do this properly with my group in place and wondered if anybody would be able to assist?

My model

public class CampaignEmailReportLinksViewModel
{
    public int Id { get; set; }
    public int CampaignElementId { get; set; }
    public string LinkURL { get; set; }
    public int QtyClicks { get; set; }
    public CampaignEmailRecipient Clickers { get; set; }
}

My query

public List<CampaignEmailReportLinksViewModel> GetCampaignLinksByElementId(int id, int cid, string user)
{
    var items = (from t1 in db.CampaignLinkClicks
                    where
                        (t1.CampaignLink.CampaignElementId == id) &&
                        (t1.CampaignLink.CampaignElement.Campaign.CompanyId == cid)

                    group new {t1} by new
                                        {
                                            t1.CampaignLink.URL,
                                            t1.CampaignLink.CampaignElementId
                                        }
                    into g

                    select new CampaignEmailReportLinksViewModel
                            {
                                LinkURL = g.Key.URL,
                                QtyClicks = g.Count(),
                                Clickers = ???????????
                            }).OrderByDescending(x => x.QtyClicks).ToList();

    return items.ToList();
}

EDIT

CampaignLinkClick looks like this:

public int Id
public int CampaignRecipientId
public string IP
public datetime Timestamp
public int CampaignLinkId

Upvotes: 0

Views: 174

Answers (1)

Ryan Amies
Ryan Amies

Reputation: 4922

If you think on what you're trying to do, your SQL will group items by the URL, so if you had:

User  |  URL     
--------------------------------------------------
User1          x.com      
User2          x.com      
User1          y.com

When you group by URL you will get a result set of and so the sub element of user will be lost, as so,

URL     
--------------------------------------------------
x.com      
y.com

One (rather costly) way would be to get the results of each URL,

select new CampaignEmailReportLinksViewModel
                            {
                                LinkURL = g.Key.URL,
                                QtyClicks = g.Count(),
                                Clickers = ""
                            }

foreach(var item in items){
    item.Clickers = db.CampaignLinkClicks
                           .Where(x=>x.Url == item.URL)
                           .Clickers.Select(x=>x.Name).ToString();
}

Upvotes: 1

Related Questions