ozzii ozzii - 4 months ago 25
SQL Question

LINQ To Entities INNER JOIN with COUNT

I'm trying to do replicate the following query in LINQ using the comprehension method. My SQL is as follows:

SELECT COUNT(Post.Id), User.Id, User.Name
FROM Post
INNER JOIN User ON Post.ModeratorId = User.Id
WHERE Post.Status IN ("Live", "Pending", "Expired")
GROUP BY User.Id, User.Name


My LINQ query is as follows but its still returns a 0 count when no moderator has been assigned to the post. Note a Post.ModeratorId is a nullable value.

I only want a list of moderators and a count of post they are or have moderated.
How can I replicate the above SQL in LINQ?

public IEnumerable<ModeratorPostViewModel> GetModeratorPostCount()
{
var posts = _context.Post
.Include(p => p.Moderator)
.Include(p => p.Status)
.Where(p => p.ModeratorId != null && p.Status.Name IN ("Live", "Pending", "Expired"))
.OrderBy(p => p.Moderator.Name)
.Select(p => new ModeratorPostViewModel
{
Id = p.ModeratorId,
Name = p.Moderator.Name,
CountOfPosts = p.Moderator.ModeratorPosts.Count()
})
.ToList();

// Return list
return posts
}


My models are defined as follows:

public class Post
{
int Id { get; set; }
int StatusId { get; set; }
string ModeratorId { get; set; }

// Navigation properties
public Status Status { get; set; }
public ApplicationUser Moderator { get; set; }

// some other other properties
}

public class ApplicationUser : IdentityUser
{
public string Name { get; set; }

// Navigation property
public ICollection<Post> ModeratorPosts { get; set; }

}

Answer

I only want a list of moderators and a count of post they are or have moderated

Then base your query on Moderator (or whatever it's called) entity:

var statuses = new [] { "Live", "Pending", "Expired" };
var posts = _context.Moderator
    .OrderBy(m => m.Name)
    .Select(m => new ModeratorPostViewModel 
    {
        Id = m.Id,
        Name = m.Name,
        CountOfPosts = m.ModeratorPosts.Count(p => statuses.Contains(p.Status.Name))
    })
    .Where(m => m.CountOfPosts != 0)
    .ToList();

UPDATE: I have to admit that the above LINQ query does not produce a very good SQL, especially with the last Where condition. So you might resort to the exact LINQ equivalent of your SQL query (you missed the GROUP BY part):

var statuses = new [] { "Live", "Pending", "Expired" };
var posts = _context.Post
    .Where(p => p.ModeratorId != null && statuses.Contains(p.Status.Name))
    .GroupBy(p => new { Id = p.ModeratorId, Name = p.Moderator.Name })
    .Select(g => new ModeratorPostViewModel 
    {
        Id = g.Key.Id,
        Name = g.Key.Name,
        CountOfPosts = g.Count()
    })
    .OrderBy(m => m.Name)
    .ToList();
Comments