帮助此LINQ to SQL查询

| 我有以下查询：

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 \ ...，所以我试图在group by之后添加select ca.Content，但是编译器会抱怨。 ContentAcceses具有FK（ContentId）到Content表。我还不太了解LINQ。

已邀请:

2 个回复

届甸衬丝蚕

您需要展平分组以从组中获取单个对象。但是，由于要对每个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);

窃誓额

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);

要回复问题请先登录或注册

帮助此LINQ to SQL查询

2 个回复

发起人

linq

linq_to_sql

select

问题状态

帮助此LINQ to SQL查询

与内容相关的链接

2 个回复

发起人

linq

linq_to_sql

select

问题状态