Hi,

I'm having an issue when trying to group by and have a COUNT on object that might be NULL. The join on those is not performed correctly and aggregated values of the group by is wrong, could you look into it ?

Many Thanks,

Model

  [HierarchyRoot]
  public class Battery : Entity
  {
    [Field, Key]
    public long Id { get; private set; }
    [Field]
    public double Volts { get; set; }
    [Field]
    public int Amps { get; set; }
    [Field]
    public int Cell { get; set; }
  }

  [HierarchyRoot]
  public class Product : Entity
  {
    [Field, Key]
    public long Id { get; private set; }
    [Field]
    public string Description { get; set; }
    [Field]
    public Battery Battery { get; set; }
  }

Data population

List<Battery> batt = new List<Battery>()
      {
        null,
        new Battery(){Amps = 8, Cell = 12, Volts = 12.4},
        new Battery(){Amps = 2, Cell = 5, Volts = 3.7}
      };

      for (int i = 0; i < 10; i++)
      {
        var p = new Product()
        {
          Description = i % 3 != 0 ? "Lap:" + i%3 : null,
          Battery = batt[i%3]
        };
      }

Query

var x = Query.All<Product>().GroupBy(g=>g.Battery)
  .Select(g=>new{g.Key, Cnt = g.Count()});

Result

foreach(var e in x){
    output.WriteLine(e.Key + " - " + e.Cnt);
}
Count: Null Battery: 4
 - 0
Battery, (187246744) - 3
Battery, (187246745) - 3

The expected result is to have a count of 4 on Key null.

Sql

SELECT [a].[Id] AS [#a.Id], 1228 AS [#a.TypeId], [a].[Volts] AS [#a.Volts], [a].[Amps] AS [#a.Amps], [a].[Cell] AS [#a.Cell], (SELECT COUNT_BIG(*) FROM [dbo].[SPB.PartnerSoftware.Sfr.Reports.Product] [b] WHERE (**[b].[Battery.Id] = [c].[Battery.Id]**)) AS [c01umn] FROM (SELECT [d].[Battery.Id] AS [Battery.Id] FROM [dbo].[SPB.PartnerSoftware.Sfr.Reports.Product] [d] GROUP BY [d].[Battery.Id]) [c] LEFT OUTER JOIN [dbo].[SPB.PartnerSoftware.Sfr.Reports.Battery] [a] ON ([c].[Battery.Id] = [a].[Id])

Here, when Battery is null, the condition is not met, needed to add a or ([b].[Battery.Id] IS NULL and [c].[Battery.Id] IS NULL) ?

In Memory LINQ