StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POLINQ To SQL Paging
text
Body
copied!<p>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:</p> <pre><code>database.Users.Select(c => c).Skip(10).Take(10); </code></pre> <p>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).</p> <p>My entity model looks like this:</p> <p><strong>A campaign [has many] groups, a group [has many] contacts</strong></p> <p>this is modelled through a relationship in the database like</p> <p><strong>Campaign -> CampaignToGroupMapping -> Group -> GroupToContactMapping -> Contact</strong></p> <p>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.</p> <pre><code>Campaign CampaignName CampaignFrom CampaignDate Recipients Recipient 1 Recipient 2 Recipient n... </code></pre> <p>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.</p> <p>My attempt was:</p> <pre><code> 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(); </code></pre> <p>The problem is that the paging (with regards to Skip() and Take()) is happening for each group, not the entire data set. </p> <p>This means if I use the value 200 for the parameter <em>totalRequired</em> (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.</p> <p>In SQL, I could achieve this with a query such as:</p> <pre><code>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 </code></pre> <p>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?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload