我该如何编写这个嵌套查询？

Question

我正在努力编写一个查询，该查询取决于两个具有自己的查询操作的表的结果。

谁能帮助我吗？

---

我的解决方法如下：

    public IQueryable<Message> GetMessages(int user_id)
    {
        // Get MessageReceiver tables that share receiver id
        IQueryable<MessageReceiver> messageReceivers = GetMessageReceivers().Where(messageReceiver => messageReceiver.receiver_id == user_id);

        List<Message> messages = new List<Message>();
        foreach (MessageReceiver messageReceiver in messageReceivers)
        {
            foreach (Message message in DataContext.Messages)
            {
                if (message.id == messageReceiver.message_id)
                {
                    messages.Add(message);
                }
            }
        }

        return messages.AsQueryable<Message>();
    }

问候， 斯科特·尼姆罗德

Answer 1

由于 MessageReciever 是从与 Message 相同的数据上下文加载的，因此我们可以将它们连接到同一个查询中。

public IQueryable<Message> GetMessages(int user_id)
{    
    // Get MessageReceiver tables that share receiver id
    var messageReceivers = GetMessageReceivers().Where(messageReceiver => messageReceiver.receiver_id == user_id);

    // get all messages that have been recieved by a user
    var messages = from m in DataContext.Messages
                   join r in messageReceivers
                   on m.id equals r.message_id
                   select m;                  

    //return the messages
    return messages;
}

这将导致类似于以下内容的 SQL：（假设您将 LINQ to SQL 或 LINQ to EF 与 Microsoft SQL 提供程序一起使用）

SELECT [t0].[id], ... other columns ...
FROM [Messages] AS [t0]
INNER JOIN [MessageReceivers] AS [t1] ON [t0].[id] = [t1].[message_id]