有关此 LINQ to SQL 查询的帮助

发布于 2024-11-02 04:14:53 字数 855 浏览 0 评论 0原文

我有以下查询：

var content = (from ca in db.ContentAccesses 
               where !candidateList.Contains(ca.Content) &&
                     ca.DateAccessed >= DateTime.Now.AddDays(-90)
               group ca by ca.ContentId)
               .OrderByDescending(c => c.Count()).Take(5);

它解析为以下 T-SQL

SELECT TOP (5) [t1].[ContentId] AS [Key]
FROM (
    SELECT COUNT(*) AS [value], [t0].[ContentId]
    FROM [dbo].[ContentAccesses] AS [t0]
    WHERE (NOT ([t0].[ContentId] = @p0)) AND ([t0].[DateAccessed] >= @p1)
    GROUP BY [t0].[ContentId]
    ) AS [t1]
ORDER BY [t1].[value] DESC

但我需要实际的“Content”对象，而不仅仅是 ContentId...所以我尝试在之后添加 select ca.Content group by 但编译器会抱怨。

ContentAccesses 有一个 FK (ContentId) 到 Content 表。

我还不太了解 LINQ。

原文

I have the following query:

var content = (from ca in db.ContentAccesses 
               where !candidateList.Contains(ca.Content) &&
                     ca.DateAccessed >= DateTime.Now.AddDays(-90)
               group ca by ca.ContentId)
               .OrderByDescending(c => c.Count()).Take(5);

Which is resolving to the following T-SQL

SELECT TOP (5) [t1].[ContentId] AS [Key]
FROM (
    SELECT COUNT(*) AS [value], [t0].[ContentId]
    FROM [dbo].[ContentAccesses] AS [t0]
    WHERE (NOT ([t0].[ContentId] = @p0)) AND ([t0].[DateAccessed] >= @p1)
    GROUP BY [t0].[ContentId]
    ) AS [t1]
ORDER BY [t1].[value] DESC

But I need the actual "Content" objects, not just the ContentId's... so I've tried adding select ca.Content after the group by but compiler will complain.

ContentAcceses has a FK (ContentId) to Content table.

I don't understand LINQ quite well yet.

分享到QQ

分享到微博

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

发布评论

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

亣腦蒛氧 2024-11-09 04:14:53

您需要将分组展开以从组中获取单个对象。但是，由于您想要对每个 ContentAccess 的 Content 进行分组，因此您也应该按该分组。

var content = (from ca in db.ContentAccesses 
               where !candidateList.Contains(ca.Content)
                  && ca.DateAccessed >= DateTime.Now.AddDays(-90)
               group ca by ca.ContentId into g
               orderby g.Count() descending
               from ca in g        // flatten the group
               select ca)
              .Take(5);

为了获得更简单的等效 TSQL 查询，您可能需要使用 LINQ to Objects 进行扁平化并获取前 5 个。

var content = (from ca in db.ContentAccesses 
               where !candidateList.Contains(ca.Content)
                  && ca.DateAccessed >= DateTime.Now.AddDays(-90)
               group ca by ca.ContentId into g
               orderby g.Count() descending
               select g)
              .AsEnumerable()     // use LINQ to Objects
              .SelectMany(g => g) // flatten the group
              .Take(5);

You need to flatten the groupings down to get the individual objects from the group. However since you wanted to group each ContentAccess's Content, you should group by that as well.

var content = (from ca in db.ContentAccesses 
               where !candidateList.Contains(ca.Content)
                  && ca.DateAccessed >= DateTime.Now.AddDays(-90)
               group ca by ca.ContentId into g
               orderby g.Count() descending
               from ca in g        // flatten the group
               select ca)
              .Take(5);

For the sake of having a simpler equivalent TSQL query, you might want to use LINQ to Objects to do the flattening and get the first 5.

var content = (from ca in db.ContentAccesses 
               where !candidateList.Contains(ca.Content)
                  && ca.DateAccessed >= DateTime.Now.AddDays(-90)
               group ca by ca.ContentId into g
               orderby g.Count() descending
               select g)
              .AsEnumerable()     // use LINQ to Objects
              .SelectMany(g => g) // flatten the group
              .Take(5);

回复收藏 0 原文

椒妓 2024-11-09 04:14:53

var content =  (from ca in db.ContentAccesses 
               where !candidateList.Contains(ca.Content) &&
                     ca.DateAccessed >= DateTime.Now.AddDays(-90)
               group ca by ca.ContentId into cag
               select new
               {
                 ContentId = cag.Key,
                 Contents = cag
               }).OrderByDescending(c => c.Contents.Count()).Take(5);

var content =  (from ca in db.ContentAccesses 
               where !candidateList.Contains(ca.Content) &&
                     ca.DateAccessed >= DateTime.Now.AddDays(-90)
               group ca by ca.ContentId into cag
               select new
               {
                 ContentId = cag.Key,
                 Contents = cag
               }).OrderByDescending(c => c.Contents.Count()).Take(5);

回复收藏 0 原文

~没有更多了~