Querying Entity Framework. Part 24 – Aggregating Nullable Fields – Andrey Zavadskiy

It’s a very simple case for Entity Framework model. I have a nullable column in Clients table.

public int? Rating { get; set; }

1	public int? Rating { get; set; }

I want to count values and to sum up them. The LINQ query to EF model can be very simple.

var count241 = ctx.Clients
    .Count();
var sum241 = ctx.Clients
    .Select(x => x.Rating)
    .Sum();

var count241 = ctx.Clients

.Count();

var sum241 = ctx.Clients

.Select(x => x.Rating)

.Sum();

But these queries reveals some magic. Count() returns int type, but Sum() return int?. So you may fall into one of the following cases.

Method	Return type	Result on all rows	Result on not-nullable rows	Result on empty dataset
Count()	int	Count all rows regardless null or not null	Count only not-nullable rows	Zero
Sum()	int?	Sum only not-nullable values	Sum only not-nullable values	Null

The most dangerous is the last case when Sum() returns null, so you should add a check for null value. If you want to get zero, you need to change a request and add DefaultIfEmpty() method.

var sum244 = ctx.Clients
    .Select(x => x.Rating)
    .DefaultIfEmpty(0)
    .Sum();

var sum244 = ctx.Clients

.Select(x => x.Rating)

.DefaultIfEmpty(0)

.Sum();

Leave a Reply Cancel reply