CODEWITHSUNDEEP
entity-frameworklinq-to-sql
Mike
Mike

Reputation: 2601

Get Counts using Linq to SQL

I have the following SQL which works fine

SELECT 
    f.ForumId, 
    f.Name, 
    COUNT(ft.TopicId) AS TotalTopics, 
    COUNT(fm.MessageId) AS TotalMessages
FROM 
    tblForumMessages fm INNER JOIN
    tblForumTopics ft ON fm.TopicId = ft.TopicId RIGHT OUTER JOIN
    tblForums f ON ft.ForumId = f.ForumId
GROUP BY f.ForumId, f.Name

That I'm trying to convert to Linq.

Here's what I have

 var forums =   (from f in Forums
                join ft in ForumTopics on f.ForumId equals ft.ForumId into topics
                from y in topics.DefaultIfEmpty()
                join fm in ForumMessages on y.TopicId equals fm.TopicId into messages
                from x in messages.DefaultIfEmpty()
                select new { f.ForumId, f.Name, y.TopicId, x.MessageId } into x
                group x by new { x.ForumId, x.Name } into g
                select new
                    {
                        ForumId = g.Key.ForumId,
                        ForumName = g.Key.Name,
                        TopicCount = g.Count(i => i.TopicId),
                        MessageCount = g.Count(i => i.MessageId)
                    }
                ).ToList();

I'm getting an error on TopicCount = g.Count(i => i.TopicId) saying Cannot convert expression type 'System.Guid' to return type 'bool'.

What am I missing to make this work?

Thanks

* EDIT *

Thanks to Rob I got it to work but the counts were always returning 1 for Topic Count and Message Count even though there were no records. It should have been returning 0 for both.

I've modified the query by changing 

select new { f.ForumId, f.Name, y.TopicId, x.MessageId } into x

to 

select new
{
    f.ForumId, f.Name, 
    TopicId = y != null ? y.TopicId : (Guid?)null,
    MessageId = z != null ? z.MessageId : (Guid?)null
} into x

And for the actual counts, I changed the query to

select new
{
    g.Key.ForumId,
    g.Key.Name,
    TopicCount = g.Count(t => t.TopicId != null),
    MessageCount = g.Count(t => t.MessageId != null)
}

Upvotes: 1

Views: 92

Answers (2)

ovolko
ovolko

Reputation: 2797

You can also try TopicCount = g.Select(i => i.TopicId).Distinct().Count()

Upvotes: 0

Rob Lyndon
Rob Lyndon

Reputation: 12651

The offending article is TopicCount = g.Count(i => i.TopicId). The Count method takes a Func<T, bool> (it gives the number of items in the collection that satisfy the predicate).

It looks like you want the number of distinct TopicIds in your group. Try replacing TopicCount = g.Count(i => i.TopicId) with TopicCount = g.GroupBy(i => i.TopicId).Count().

Upvotes: 2

Related Questions