LINQ To SQL Paging

Question

I've been using .Skip() and .Take() extension methods with LINQ To SQL for a while now with no problems, but in all the situations I've used them it has always been for a single table - such as:

database.Users.Select(c => c).Skip(10).Take(10);

My problem is that I am now projecting a set of results from multiple tables and I want to page on the overall set (and still get the benefit of paging at the DB).

My entity model looks like this:

A campaign [has many] groups, a group [has many] contacts

this is modelled through a relationship in the database like

Campaign -> CampaignToGroupMapping -> Group -> GroupToContactMapping -> Contact

I need to generate a data structure holding the details of a campaign and also a list of each contact associated to the campaign through the CampaignToGroupMapping, i.e.

Campaign
   CampaignName
   CampaignFrom
   CampaignDate
   Recipients
      Recipient 1
      Recipient 2
      Recipient n...

I had tried to write a LINQ query using .SelectMany to project the set of contacts from each group into one linear data set, in the hope I could .Skip() .Take() from that.

My attempt was:

 var schedule = (from c in database.Campaigns
                 where c.ID == highestPriority.CampaignID
                 select new PieceOfCampaignSchedule
                 {
                     ID = c.ID,
                     UserID = c.UserID,
                     Name = c.Name,
                     Recipients = c.CampaignGroupsMappings.SelectMany(d => d.ContactGroup.ContactGroupMappings.Select(e => new ContactData() { /*Contact Data*/ }).Skip(c.TotalSent).Take(totalRequired)).ToList()

                 }).SingleOrDefault();

The problem is that the paging (with regards to Skip() and Take()) is happening for each group, not the entire data set.

This means if I use the value 200 for the parameter totalRequired (passed to .Take()) and I have 3 groups associated with this campaign, it will take 200 from each group - not 200 from the total data from each group associated with the campaign.

In SQL, I could achieve this with a query such as:

select * from
(
    select [t1].EmailAddress, ROW_NUMBER() over(order by CampaignID desc) as [RowNumber] from contacts as [t1]
    inner join contactgroupmapping as [t2] on [t1].ID = [t2].ContactID
    inner join campaigngroupsmapping as [t3] on [t3].ContactGroupID = [t2].GroupID
    where [t3].CampaignID = @HighestPriorityCampaignID

) as [Results] where [Results].[RowNumber] between 500 and 3000

With this query, I'm paging over the combined set of contacts from each group associated with the particular campaign. So my question is, how can I achieve this using LINQ To SQL syntax instead?

Answer 1

Use a view to aggregate the results from the multiple tables and then use LINQ over the view

Answer 2

To mimic the SQL query you provided you would do this:

var schedule = (from t1 in contacts
                join t2 in contactgroupmapping on t1.ID equals t2.GroupID
                join t3 in campaigngroupsmapping on t3.ContactGroupID = t2.GroupID
                where t3.CampaignID = highestPriority.CampaignID
                select new PieceOfCampaignSchedule
                {
                  Email = t1.EmailAddress
                }).Skip(500).Take(3000).ToList()

Are you trying to page over campaigns, recipients, or both?

Answer 3

I think your attempt is really close; Maybe I'm missing something, but I think you just need to close your SelectMany() before the Skip/Take:

Recipients = c.CampaignGroupsMappings.SelectMany(d => d.ContactGroup.ContactGroupMappings.Select(e => new ContactData() { /*Contact Data*/ })).Skip(c.TotalSent).Take(totalRequired).ToList()

Note: added ")" after "/* Contact Data */ })" and removed ")" from after ".Take(totalRequired)"