ozzii ozzii - 5 months ago 29
SQL Question


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

// 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; }



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)

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)