CODEWITHSUNDEEP
c#.netsql-serverdatabaselinq
Adrian Munteanu
Adrian Munteanu

Reputation: 124

Linq outer join and group by with count in select

I have this SQL statement

SELECT 
    users.UserName, COUNT(photos.Id) AS 'photos' 
FROM photos 
RIGHT JOIN users ON photos.UploaderId = users.Id 
GROUP BY users.UserName`

I try to build this linq query

    protected void LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        var appContext = new ApplicationDbContext();
        var x = from u in appContext.Users
                join p in appContext.Photos on u equals p.Uploader into ps
                from sp in ps.DefaultIfEmpty()
                group ps by u into g
                select new { Username = g.Key.UserName, Photos = g.Count() };
        e.Result = x;
    }

but I get all users with no photos with 1 returned from Count()

Upvotes: 0

Views: 158

Answers (1)

Adam Robinson
Adam Robinson

Reputation: 185643

This is to be expected, as there is no group that would have a Count of 0 (because in that case the group wouldn't exist in the first place).

You'll need to modify your group selector to ensure that the resulting count takes into account the fact that a photo could be null. This is slightly tricker than in SQL, since value-typed columns (like an int) will have default values instead of null. You'll need to check by either comparing a non-nullable string column to null (as I do below, since I assume Uploader is not nullable) or you'll have to compare a non-nullable column against an impossible value (say, comparing an identity column to a value outside of its range, perhaps -1).

select new 
{ 
    Username = g.Key.UserName, 
    Photos = g.Where(i => i.Uploader != null).Count() 
};

Upvotes: 3

Related Questions