0

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.

Improve this question
2
  • 1
    What you mean by "actual "Content" objects" ? All the columns ? You can't group by and get all the columns in table. Commented Apr 15, 2011 at 20:54
  • Yes, but I'm grouping the ContentAcceses table, which has the FK. I got the ids I need, I just need to do get the Content objects which ids are returned in that query, without dropping another query. Commented Apr 15, 2011 at 20:58

2 Answers 2

Reset to default
2

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Great, nice and clean, except I needed to do 'select ca.Content' instead of just ca. Thank you =)
@emzero: Oh so Content is just a column in a ContentAccess? Then you should group those instead. I'll update my queries.
No, 'Content' is another table which is referenced in 'ContentAcceses' by a FK called 'ContentId'.
@emzero: In that case, I'll just revert to how it was. As long as it leads you to the answer.
Yeah, it works, although the sql generated is a bit odd, but I can live with it.
0 
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);
Improve this answer

1 Comment

Thanks, but I prefer not to create anonymous objects, besides 'cag' doesn't contain the Content objects, it's just the Key/Count group.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.