Knowledgebase

If you do a count on a group by in LINQ when using a left outer join, the count will be 1 even though there are no records found for the group. This is caused by the DefaultIfEmpty. The DefaultIfEmpty will insert 1 default object for each group that does not contain any records.

To fix this, adjust the Count, so it ignores the inserted default objects:

using System;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;

namespace Research.Rli.Tests
{
   
    [TestClass]
    public class ResearchTester
    {
        [TestMethod]
        public void Count_should_be_zero_when_left_outer_join_contains_no_values()
        {
            var statussen = new List<StatusDto> 
            { 
                new StatusDto { Id = 0 }, 
                new StatusDto { Id = 1 }, 
                new StatusDto { Id = 2 } 
            };
            var actualAlerts = new List<RecordDto> 
            { 
                new RecordDto { Id = 1, StatusId = 2 }, 
                new RecordDto { Id = 2, StatusId = 2 }, 
                new RecordDto { Id = 3, StatusId = 2 }, 
                new RecordDto { Id = 4, StatusId = 2 } 
            };

            int defaultRecordId = -2;
            List<ResultDto> totals = (from s in statussen
                                      join aa in actualAlerts on s.Id equals aa.StatusId into gaa
                                      from aaEmpty in gaa.DefaultIfEmpty(new RecordDto { Id = defaultRecordId })
                                      group aaEmpty by s.Id into grp
                                      select new ResultDto 
                                                { 
                                                    StatusId = grp.Key, 
                                                    Count = grp.Count(x => x.Id != defaultRecordId) 
                                                }).ToList();

            Assert.AreEqual(0, totals[0].Count);
            Assert.AreEqual(0, totals[1].Count);
            Assert.AreEqual(4, totals[2].Count);

        }
    }
    public class RecordDto
    {
        public int Id { get; set; }
        public int StatusId { get; set; }
    }
    public class StatusDto
    {
        public int Id { get; set; }
    }
    public class ResultDto
    {
        public int StatusId { get; set; }
        public int Count { get; set; }
    }
}