LINQ To SQL 分页

发布于 2024-07-18 00:54:08 字数 2063 浏览 8 评论 0原文

我已经在 LINQ To SQL 中使用 .Skip() 和 .Take() 扩展方法有一段时间了,没有任何问题,但在我使用它们的所有情况下,它始终适用于单个表 - 例如:

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

我的问题是,我现在正在投影来自多个表的一组结果,并且我想对整个结果集进行分页(并且仍然获得在数据库分页的好处)。

我的实体模型如下所示:

一个营销活动[有许多]组,一个组[有许多]联系人

这是通过数据库中的关系建模的,例如

营销活动-> CampaignToGroupMapping -> 组-> 群组到联系人映射 -> 联系人

我需要生成一个数据结构,其中包含活动的详细信息以及通过 CampaignToGroupMapping 与活动关联的每个联系人的列表,即

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

我尝试使用 .SelectMany 编写一个 LINQ 查询来投影一组将每一组的联系人放入一个线性数据集中,希望我可以从中进行 .Skip() .Take() 。

我的尝试是:

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

问题是分页(关于 Skip() 和 Take())是针对每个组而不是整个数据集进行的。

这意味着,如果我对参数 totalRequired 使用值 200(传递给 .Take()),并且我有 3 个与此活动相关的组,则将从每个组中获取 200 个,而不是从总数中获取 200 个来自与活动相关的每个组的数据。

在 SQL 中,我可以通过如下查询来实现此目的:

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

通过此查询,我可以对与特定营销活动关联的每个组中的联系人组合集进行分页。 所以我的问题是,如何使用 LINQ To SQL 语法来实现这一目标?

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

多孤肩上扛 2024-07-25 00:54:09

要模仿您提供的 SQL 查询,您可以执行以下操作:

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(2500).ToList()

您是否尝试对活动、收件人或两者进行分页?

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(2500).ToList()

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

等风也等你 2024-07-25 00:54:09

使用视图聚合多个表的结果,然后在视图上使用 LINQ

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

瑶笙 2024-07-25 00:54:09

我认为你的尝试非常接近; 也许我错过了一些东西,但我认为你只需要在 Skip/Take 之前关闭 SelectMany():

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

注意:在“/* Contact Data */ })”之后添加“)”并从“之后”删除“)” .Take(总共需要)"

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)"

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文