从两个表Linq分组并选择第一个 [英] Group and Select First From Two Tables Linq

问题描述

我正在尝试使用EFCore创建一个简单的查询，返回与我交谈的人的列表，以及我们两个人之间发送的最后一条消息(非常类似于它在Facebook Messenger或Whatsapp上的显示方式).我创建了linq查询，但是它生成了一个sql查询的地狱.我正在尝试优化linq查询以生成更好的sql，因此全文如下:

I'm trying to create a simple query using EFCore, returning the list of people i'm conversing with, and the last message that was sent between the two of us (pretty much like how it's displayed on Facebook Messenger or Whatsapp). I created the linq query but its generating one hell of an sql query. I'm trying to optimize the linq query to generate a better sql, so here comes the full story:

访客包含访客信息，而ChatMessages包含实际聊天.

The Visitor contains the visitor information, and the ChatMessages contains the actual chat.

我尝试了如下第一次查询:

I tried the first query as follows:

from c in ChatMessages
orderby c.CreatedAt descending 
group c by c.VisitorId  into x
select x.First()

哪个让我获得了按访问者ID分组的最新消息列表:

Which got me the list of latest messages grouped by the visitor id:

这很酷，特别是生成了简短的sql查询:

which is cool, specially with the short sql query generated:

SELECT [t3].[test], [t3].[Id], [t3].[Message], [t3].[UserId], [t3].[VisitorId], [t3].[isDeleted] AS [IsDeleted], [t3].[CreatedAt], [t3].[CreatedBy], [t3].[LastUpdatedAt], [t3].[LastUpdatedBy], [t3].[isFromVisitor] AS [IsFromVisitor]
FROM (
    SELECT [t0].[VisitorId]
    FROM [ChatMessages] AS [t0]
    GROUP BY [t0].[VisitorId]
    ) AS [t1]
OUTER APPLY (
    SELECT TOP (1) 1 AS [test], [t2].[Id], [t2].[Message], [t2].[UserId], [t2].[VisitorId], [t2].[isDeleted], [t2].[CreatedAt], [t2].[CreatedBy], [t2].[LastUpdatedAt], [t2].[LastUpdatedBy], [t2].[isFromVisitor]
    FROM [ChatMessages] AS [t2]
    WHERE (([t1].[VisitorId] IS NULL) AND ([t2].[VisitorId] IS NULL)) OR (([t1].[VisitorId] IS NOT NULL) AND ([t2].[VisitorId] IS NOT NULL) AND ([t1].[VisitorId] = [t2].[VisitorId]))
    ORDER BY [t2].[CreatedAt] DESC
    ) AS [t3]
ORDER BY [t3].[CreatedAt] DESC

2.再试一次，同时也加入Visitor表

现在我也想返回访问者信息，所以我必须加入visitors表:

2. Second Try, Joining the Visitor table as well

Now I want to return the visitor information as well, so I have to join the visitors table:

from c in ChatMessages
join v in Visitors on  c.VisitorId equals v.Id 
orderby c.CreatedAt descending 
group new {Message = c, Visitor = v} by c.Visitor.Id  into x
select x

生成了我想要的东西

问题是，生成的SQL查询非常混乱:

Problem is, the generate SQL query got very messy:

SELECT [t2].[Id] AS [Key]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
GROUP BY [t2].[Id]
GO

-- Region Parameters
DECLARE @x1 BigInt = 1
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 2
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 3
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 4
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 5
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 6
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 7
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 8
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 9
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 10
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 11
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 12
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 13
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 14
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 15
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 16
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 17
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 18
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 19
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC
GO

-- Region Parameters
DECLARE @x1 BigInt = 20
-- EndRegion
SELECT [t0].[Id], [t0].[Message], [t0].[UserId], [t0].[VisitorId], [t0].[isDeleted] AS [IsDeleted], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[isFromVisitor] AS [IsFromVisitor], [t1].[Id] AS [Id2], [t1].[Email], [t1].[Name], [t1].[Phone], [t1].[isDeleted] AS [IsDeleted2], [t1].[CreatedAt] AS [CreatedAt2], [t1].[CreatedBy] AS [CreatedBy2], [t1].[LastUpdatedAt] AS [LastUpdatedAt2], [t1].[LastUpdatedBy] AS [LastUpdatedBy2], [t1].[Fingerprint], [t1].[IP]
FROM [ChatMessages] AS [t0]
INNER JOIN [Visitors] AS [t1] ON [t0].[VisitorId] = ([t1].[Id])
LEFT OUTER JOIN [Visitors] AS [t2] ON [t2].[Id] = [t0].[VisitorId]
WHERE @x1 = [t2].[Id]
ORDER BY [t0].[CreatedAt] DESC

我似乎不希望在数据库中触发哪一个查询.此外，当在asp.net核心应用程序中执行此代码时，即时通讯收到异常EF.Property called with wrong property name.，不确定原因:

Which does not seem like a query i would want to fire at the database. Moreover, when executing this code inside the asp.net core app, im getting an exception EF.Property called with wrong property name., not sure why:

crit: converse_app.Controllers.VisitorsController[0]
      There was an error on 'GetVisitorsAsync' invocation: System.InvalidOperationException: EF.Property called with wrong property name.
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
         at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.Expand(Expression source, MemberIdentity member)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.VisitMember(MemberExpression memberExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.VisitMember(MemberExpression memberExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.WeakEntityExpandingExpressionVisitor.Expand(SelectExpression selectExpression, Expression lambdaBody)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.RemapLambdaBody(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateGroupBy(ShapedQueryExpression source, LambdaExpression keySelector, LambdaExpression elementSelector, LambdaExpression resultSelector)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
         at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
         at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
         at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
         at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
         at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
         at converse_app.Controllers.VisitorsController.GetVisitorsAsync(Int32 pageSize, Int32 pageNumber

很抱歉，我的问题很长，所以我的问题是如何优化linq查询以获得更好的sql输出以及该错误可能引发的原因.

Sorry for the long for the post, so my question is how can i optimize the linq query for a better sql output, as well as the reason this error might be firing.

我正在将.NET Core 3预览8与EF Core 3预览8结合使用，并针对MSSQL运行.

I'm using .NET Core 3 preview8 with EF Core 3 preview8, and running against MSSQL.

推荐答案

您要查找的查询标准以LINQ to Entities(EF)的形式表示，如下所示(无join s，无GroupBy，请使用导航属性):

The query you are looking for standardly is expressed in LINQ to Entities (EF) with something like this (no joins, no GroupBy, use navigation properties):

var query = context.Visitors
    .Select(v => new
    {
        Visitor = v,
        Message = v.VisitorChatMessages
            .OrderByDescending(m => m.CreatedAt)
            .FirstOrDefault()
    });

但这是陷阱. EF6创建了效率很低的SQL查询，而EF Core直到现在仍产生(同样效率很低)N + 1个SQL查询.

But here is the trap. EF6 creates quite inefficient SQL query, and EF Core until now produces (again quite inefficient) N + 1 SQL queries.

但是，这在EF Core 3.0中正在朝着积极的方向改变！通常(仍然)我不建议使用EF Core 3.0的预览版(测试版)，因为它们会重写整个查询转换/处理管道，因此很多事情无法按预期进行.

But this is changing in EF Core 3.0 in a positive direction! Usually (and still) I don't recommend using the preview (beta) versions of EF Core 3.0, because they are rewriting the whole query translation/processing pipeline, so many things don't work as expected.

但是今天我将我的EF Core测试环境更新为EF Core 3.0 Preview 9 ，我很高兴地看到上面的查询现在可以很好地转换为以下单个SQL查询:

But today I've updated my EF Core test environment to EF Core 3.0 Preview 9 and I'm pleased to see that the above query now nicely translates to the following single SQL query:

  SELECT [v].[Id], [v].[CreatedAt], [v].[CreatedBy], [v].[Email], [v].[Fingerprint], [v].[IP], [v].[IsDeleted], [v].[LastUpdatedAt], [v].[LastUpdatedBy], [v].[Name], [v].[Phone], [t0].[Id], [t0].[CreatedAt], [t0].[CreatedBy], [t0].[IsDeleted], [t0].[IsFromVisitor], [t0].[LastUpdatedAt], [t0].[LastUpdatedBy], [t0].[Message], [t0].[UserId], [t0].[VisitorId]
  FROM [Visitors] AS [v]
  LEFT JOIN (
      SELECT [t].[Id], [t].[CreatedAt], [t].[CreatedBy], [t].[IsDeleted], [t].[IsFromVisitor], [t].[LastUpdatedAt], [t].[LastUpdatedBy], [t].[Message], [t].[UserId], [t].[VisitorId]
      FROM (
          SELECT [c].[Id], [c].[CreatedAt], [c].[CreatedBy], [c].[IsDeleted], [c].[IsFromVisitor], [c].[LastUpdatedAt], [c].[LastUpdatedBy], [c].[Message], [c].[UserId], [c].[VisitorId], ROW_NUMBER() OVER(PARTITION BY [c].[VisitorId] ORDER BY [c].[CreatedAt] DESC) AS [row]
          FROM [ChatMessages] AS [c]
      ) AS [t]
      WHERE [t].[row] <= 1
  ) AS [t0] ON [v].[Id] = [t0].[VisitorId]

请注意ROW_NUMBER() OVER (PARTITION BY ORDER BY)构造的优美用法.这是EF查询翻译有史以来第一次.我很兴奋.干得好，EF核心团队！

Note the beautiful utilization of the ROW_NUMBER() OVER (PARTITION BY ORDER BY) construct. This is the first time EF query translation does that ever. I'm excited. Good job, EF Core team!

更新:与您的第一个查询完全相同(在预览9中，btw失败，并带有运行时异常)

Update: The exact equivalent of your first query (which btw fails with runtime exception in Preview 9)

from c in context.ChatMessages
orderby c.CreatedAt descending 
group c by c.VisitorId  into x
select x.First()

但有其他信息是

from v in context.Visitors
from c in v.VisitorChatMessages
    .OrderByDescending(c => c.CreatedAt)
    .Take(1)
orderby c.CreatedAt descending
select new
{
    Visitor = v,
    Message = c
})

生成的SQL几乎相同-仅LEFT OUTER JOIN变为INNER JOIN，最后还有其他ORDER BY.

The generated SQL is pretty much the same - just the LEFT OUTER JOIN becomes INNER JOIN and there is additional ORDER BY at the end.

看起来像要完成这项工作，必须避免使用GroupBy并使用GroupJoin(此集合导航属性在LINQ to Entities查询中表示)或相关的SelectMany才能实现所需的分组.

Looks like that to make this work, it's essential to avoid GroupBy and use GroupJoin (which collection navigation property represents in LINQ to Entities queries) or correlated SelectMany to achieve the desired grouping.

这篇关于从两个表Linq分组并选择第一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！