带有嵌套选择的Linq查询

Question

我一直在尝试用linq语句表达以下SQL，但一无所获。

select messageid, comment 
from Message 
where isactive = 1 and messageid in 
(
    select messageid 
    from org.Notification 
    where NotificationTypeId = 7 and orguserid in 
    (
        select OrgUserId 
        from org.OrgUser
        where OrgId in 
        (
            select ChildOrgId from OrgRelation
            where ParentOrgId = 10001
        )
    )
    group by messageid
)

这个查询在SQL Server中可以直接使用，但我想在C#（E-F）代码中使用它，但我似乎一直走了个死胡同。

- BogeyMan

---

嗯，你说的“跑圈圈”是什么意思？我没有看到任何循环引用。 - Bagus Tesa

这些表没有关系吗？如果有关系，为什么不使用连接。如果没有任何关系，为什么需要它们的数据。此外，如果变得复杂，请将其放入存储过程中并调用它。 - Praneet Nadkar

请包含您尝试过的代码和背后的思路。这也会告诉我们您想要使用方法语法还是查询语法。 - Peter B

@BagusTesa “跑圈圈”——这是一个表达方式。我并没有在循环引用方面字面意义上的意思。对于任何困惑，我表示抱歉。 - BogeyMan

@PraneetNadkar - 是的。有一些关联。我想我也可以走连接路径。我之所以使用这个例子，只是因为它给了我需要的数据（在SQL中）。 - BogeyMan

@BogeyMan 使用连接。在linq中也会更容易。另外尝试将其放入存储过程中，这将是最好的选择。 - Praneet Nadkar

Answer 1

显然，Notification对象具有messageId。几个Notification对象可能具有相同的MessageId值。

我想要获得一个子集所有Notification对象中MessageId值等于某个Id的Messages。

首先我会向你展示一条LINQ查询语句来解决你的问题，然后我会告诉你一些关于实体框架的事情，这将使这种类型的LINQ查询更容易理解和维护。

直接解决方案

（1）选择应获取消息的通知：

IQueryable<Notification> notificationsToUse = org.Notifications
    .Where(notification => notification.TypeId == 7
          && ....);

这是您的内部选择。我不确定Notifications，OrgUsers和OrgRelations之间的关系。但这超出了本问题的范围。

(2) 提取所有使用的MessageIds。

IQueryable<int> messageIdsUsedByNotificationsToUse = notificationsToUse
    .Select(notification => notification.MessageId)
    // remove duplicates:
    .Distinct();

(3) 获取所有在`messageIdsUsedByNotificationsToUse`中的活动消息

IQueryable<Message> fetchedActiveMessages = org.Messages
    .Where(message => message.IsActive
        && messageIdsUsedByNotificationsToUse.Contains(message.Id));

(4) 您不需要完整的消息，只需要 MessageId 和 Comment：

var result = fetchedActiveMessages.Select(message => new
{
    MessageId = message.Id,
    Comment = message.Comment,
});

TODO: 如果需要的话：将它们组成一个大的LINQ语句。

到目前为止，您还没有访问数据库。 我只是改变了IQueryable中的Expression。 将其组成一个大的LINQ语句不会显著提高性能，我怀疑它是否会提高可读性和可维护性。

利用Entity Framework的可能性解决方案

似乎Message和Notification之间存在一对多关系：每个Message有零个或多个Notifications，每个Notification恰好属于一个Message，使用外键MessageId。

如果您坚持Entity Framework Code-First约定, 设计类类似于以下方式。(强调一对多关系):

class Message
{
    public int Id {get; set;}

    // every Message has zero or more Notifications (one-to-many)
    public virtual ICollection<Notification> Notifications {get; set;}

    ... // other properties
}

class Notification
{
    public int Id {get; set;}

    // every Notifications belongs to exactly one Message using foreign key
    public int MessageId {get; set;}
    public virtual Message Message {get; set;}

    ... // other properties
}

class MyDbContext : DbContext
{
    public DbSet<Message> Messages {get; set;}
    public DbSet<Notification> Notifications {get; set;}
}

这是Entity Framework所需了解的关于您在 Messages 和 Notifications 之间规划一对多关系的全部内容。Entity Framework 知道您打算将哪些属性用作主键和外键，以及表格之间的关系。

有时候有很好的理由要违反这些约定。这需要使用属性或流畅 API 来解决。

重要的是具有从 Message 到 Notification 的虚拟 ICollection 结构和从属于它的Message 的Notification 的虚拟引用返回。

如果您设计的类就像这样，那么查询将会非常简单：

（1）选择您想要使用的通知：

IQueryable<Notification> notificationsToUse = ... same as above

(2) 现在您可以直接选择属于这些通知的消息：

var result = notificationsToUse.Select(notification => notification.Message)

因为每个通知都属于唯一的一条消息，所以我确定没有重复。

继续说：只有活动消息的MessageId和Comment。

    .Where(message => message.IsActive)
    .Select(message => new
    {
        MessageId = message.Id,
        Comment = message.Comment,
    });

我不确定Notifications、OrgUsers和OrgRelations之间的关系。如果您设计类来表示适当的一对多或多对多关系，那么即使是表达式(1)也会简单得多。

- Harald Coppoolse

Answer 2

你可以按照以下方式将查询分解为三个不同的部分 -

1. 首先过滤掉OrgUserIds

var filteredOrgUserIds = dc.OrgUsers.Where(u => dc.OrgRelations.Where(o 
    =>o.ParentOrgId == 10001).Select(d => 
    d.ChildOrgId).ToList().Contains(u.OrgId))
    .Select(uid => uid.OrgUserId)
    .Distinct()
    .ToList();

使用filteredOrgUserIds来过滤通知。

var filteredNotifications = dc.Notifications.Where(n => 
     n.NotificationTypeId == 7 && filteredOrgUserIds.Contains(n.OrgUserId))
     .Select(m => m.messageid)
     .Distinct()
     .ToList();

3. 最后，通过查看 filteredNotifications 来筛选出 messages。

    m.isactive == 1 && filteredNotifications.Contains(m.messageid))
    .Select(m => new { message = m.messageid, comment = m.comment })
    .Distinct()
    .ToList();

这应该可以正常工作。请您尝试一下，如果有帮助，请告知我们。

- Aritra B

---

这是我目前采用的方法。它似乎运行得相当不错。然而，在 Id 列表变得很大的情况下，性能似乎会大大降低。 - BogeyMan

进一步评论此答案。 在我的情况下，这对我起作用，但最终我进行了一些小修改。 在上面的示例中，每个序列都调用.ToList()。在我的情况下，我删除了它以推迟最终执行。 - BogeyMan

您好，如果您仍然遇到性能问题，为什么不考虑使用存储过程呢？如果您需要关于在EF6中调用SP的进一步帮助，请告诉我们。 - Aritra B

Answer 3

从我将 SQL 转换为 LINQ to SQL 的食谱中：

1. 将子查询转换为单独声明的变量。
2. 按照 LINQ 子句顺序翻译每个子句，将单子和聚合运算符（DISTINCT、TOP、MIN、MAX 等）转换为应用于整个 LINQ 查询的函数。
3. SELECT 字段必须替换为 select new { ... }，创建一个带有所有所需字段或表达式的匿名对象。
4. 将 IN 转换为 .Contains()，将 NOT IN 转换为 !...Contains()。

请注意，如果您的 SQL 查询使用了 GROUP BY，而应该使用 DISTINCT，然后应用上述规则：

var childOrgs = from or in OrgRelation where or.ParentOrgId == 1001 select or.ChildOrgId;
var orgUsers = from ou in OrgUser where childOrgs.Contains(ou.OrgId) select ou.OrgUserId;
var notificationMessages = (from n in Notification
                            where n.NotificationTypeId == 7 && orgUsers.Contains(n.orguserid)
                            select n.messageid).Distinct();
var ans = from m in Message
          where m.isactive == 1 && notificationMessages.Contains(m.messageid)
          select new { m.messageid, m.comment };

- NetMage

Answer 4

如果您需要使用IN子句，应该使用Contains方法。

- Marco Barbero