我有一个像下面的表结构 .
CREATE TABLE [dbo].[SubscriptionMessageCount](
[SubscriptionMessageCountId] [int] IDENTITY(1,1) NOT NULL,
[ActiveMessageCount] [bigint] NOT NULL,
[DeadLetterMessageCount] [bigint] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](max) NULL,
[SubscriptionId] [int] NOT NULL,
CONSTRAINT [PK_dbo.SubscriptionMessageCount] PRIMARY KEY CLUSTERED
(
[SubscriptionMessageCountId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
我正在尝试查找每个subscriptionID的最新记录(这不是唯一的) . 我可以通过遵循SQL查询来实现这一点 .
select *
from SubscriptionMessageCount MsgCounterOutter
where MsgCounterOutter.createdDate =
( select Max(MsgCounterInner.createdDate)
from SubscriptionMessageCount MsgCounterInner
where MsgCounterOutter.SubscriptionID = MsgCounterInner.SubscriptionID
group by MsgCounterInner.SubscriptionID )
我需要一个linq查询,它产生上面的SQL语句或相同的结果 . 我尝试使用linqpad工具,并提出了下面的linq查询,它给了我类似的SQL语句和相同的结果 .
from MsgCntOuter in SubscriptionMessageCounts
where MsgCntOuter.CreatedDate == ( from MsgCntInner in SubscriptionMessageCounts
where MsgCntOuter.SubscriptionId == MsgCntInner.SubscriptionId
group MsgCntInner by MsgCntInner.SubscriptionId into g
select g.Max(s=>s.CreatedDate)).First()
select MsgCntOuter
它生成的SQL如下所示 .
SELECT [t0].[SubscriptionMessageCountId], [t0].[ActiveMessageCount], [t0].[DeadLetterMessageCount], [t0].[CreatedDate], [t0].[CreatedBy], [t0].[SubscriptionId]
FROM [SubscriptionMessageCount] AS [t0]
WHERE [t0].[CreatedDate] = ((
SELECT TOP (1) [t2].[value]
FROM (
SELECT MAX([t1].[CreatedDate]) AS [value]
FROM [SubscriptionMessageCount] AS [t1]
WHERE [t0].[SubscriptionId] = [t1].[SubscriptionId]
GROUP BY [t1].[SubscriptionId]
) AS [t2]
))
结果也匹配我的SQL语句结果 . 但是当我尝试在我的控制台应用程序中使用相同的linq查询时,它会生成不同的SQL语句,并且结果也不同 .
var latestdata = from MsgCntOuter in context.SubscriptionMessageCounts
where MsgCntOuter.CreatedDate == (from MsgCntInner in context.SubscriptionMessageCounts
where MsgCntOuter.SubscriptionId == MsgCntInner.SubscriptionId
group MsgCntInner by MsgCntInner.SubscriptionId into g
select g.Max(s => s.CreatedDate)).FirstOrDefault()
select MsgCntOuter;
它生成的SQL如下所示 .
SELECT [Project4].[SubscriptionMessageCountId] AS [SubscriptionMessageCountId],
[Project4].[ActiveMessageCount] AS [ActiveMessageCount],
[Project4].[DeadLetterMessageCount] AS [DeadLetterMessageCount],
[Project4].[CreatedDate] AS [CreatedDate],
[Project4].[CreatedBy] AS [CreatedBy],
[Project4].[SubscriptionId] AS [SubscriptionId]
FROM
(SELECT [Project2].[SubscriptionMessageCountId] AS [SubscriptionMessageCountId],
[Project2].[ActiveMessageCount] AS [ActiveMessageCount],
[Project2].[DeadLetterMessageCount] AS [DeadLetterMessageCount],
[Project2].[CreatedDate] AS [CreatedDate],
[Project2].[CreatedBy] AS [CreatedBy],
[Project2].[SubscriptionId] AS [SubscriptionId],
[Project2].[C1] AS [C1],
(SELECT TOP (1) [GroupBy2].[A1] AS [C1]
FROM
(SELECT [Extent3].[SubscriptionId] AS [K1],
MAX([Extent3].[CreatedDate]) AS [A1]
FROM [dbo].[SubscriptionMessageCount] AS [Extent3]
WHERE [Project2].[SubscriptionId] = [Extent3].[SubscriptionId]
GROUP BY [Extent3].[SubscriptionId]) AS [GroupBy2]) AS [C2]
FROM
(SELECT [Extent1].[SubscriptionMessageCountId] AS [SubscriptionMessageCountId],
[Extent1].[ActiveMessageCount] AS [ActiveMessageCount],
[Extent1].[DeadLetterMessageCount] AS [DeadLetterMessageCount],
[Extent1].[CreatedDate] AS [CreatedDate],
[Extent1].[CreatedBy] AS [CreatedBy],
[Extent1].[SubscriptionId] AS [SubscriptionId],
(SELECT TOP (1) [GroupBy1].[A1] AS [C1]
FROM
(SELECT [Extent2].[SubscriptionId] AS [K1],
MAX([Extent2].[CreatedDate]) AS [A1]
FROM [dbo].[SubscriptionMessageCount] AS [Extent2]
WHERE [Extent1].[SubscriptionId] = [Extent2].[SubscriptionId]
GROUP BY [Extent2].[SubscriptionId]) AS [GroupBy1]) AS [C1]
FROM [dbo].[SubscriptionMessageCount] AS [Extent1]) AS [Project2]) AS [Project4]
WHERE [Project4].[CreatedDate] = (CASE
WHEN ([Project4].[C1] IS NULL) THEN convert(datetime2, '0001-01-01 00:00:00.0000000', 121)
ELSE [Project4].[C2]
END)
我正在使用Entity Framework 6和SQL azure进行上述使用 . 需要帮助写一个合适的linq查询,它可以给我正确的结果 . 我尝试在我的linq查询中使用“let”,但它也给了我相同的SQL和相同的错误结果 .