Max return value if empty query
C#.NetEntity FrameworkLinqLambdaC# Problem Overview
I have this query:
int maxShoeSize = Workers
.Where(x => x.CompanyId == 8)
.Max(x => x.ShoeSize);
What will be in maxShoeSize
if company 8 has no workers at all?
UPDATE:
How can I change the query in order to get 0 and not an exception?
C# Solutions
Solution 1 - C#
int maxShoeSize = Workers.Where(x => x.CompanyId == 8)
.Select(x => x.ShoeSize)
.DefaultIfEmpty(0)
.Max();
The zero in DefaultIfEmpty
is not necessary.
Solution 2 - C#
I know this is an old question and the accepted answer works, but this question answered my question about whether such an empty set would result in an exception or a default(int)
result.
The accepted answer however, while it does work, isn't the ideal solution IMHO, which isn't given here. Thus I am providing it in my own answer for the benefit of anyone who is looking for it.
The OP's original code was:
int maxShoeSize = Workers.Where(x => x.CompanyId == 8).Max(x => x.ShoeSize);
This is how I would write it to prevent exceptions and provide a default result:
int maxShoeSize = Workers.Where(x => x.CompanyId == 8).Max(x => x.ShoeSize as int?) ?? 0;
This causes the return type of the Max
function to be int?
, which allows the null
result and then the ??
replaces the null
result with 0
.
EDIT
Just to clarify something from the comments, Entity Framework doesn't currently support the as
keyword, so the way to write it when working with EF would be:
int maxShoeSize = Workers.Where(x => x.CompanyId == 8).Max<[TypeOfWorkers], int?>(x => x.ShoeSize) ?? 0;
Since the [TypeOfWorkers]
could be a long class name and is tedious to write, I've added an extension method to help out.
public static int MaxOrDefault<T>(this IQueryable<T> source, Expression<Func<T, int?>> selector, int nullValue = 0)
{
return source.Max(selector) ?? nullValue;
}
This only handles int
, but the same could be done for long
, double
, or any other value type you need. Using this extension method is very simple, you just pass in your selector function and optionally include a value to be used for null, which defaults to 0. So the above could be rewritten like so:
int maxShoeSize = Workers.Where(x => x.CompanyId == 8).MaxOrDefault(x => x.ShoeSize);
Hopefully that helps people out even more.
Solution 3 - C#
Max() won't return anything in that case.
It will raise InvalidOperationException since the source contains no elements.
Solution 4 - C#
int maxShoeSize = Workers.Where(x => x.CompanyId == 8)
.Select(x => x.ShoeSize)
.DefaultIfEmpty()
.Max();
Solution 5 - C#
If this is Linq to SQL, I don't like to use Any()
because it results in multiple queries to SQL server.
If ShoeSize
is not a nullable field, then using just the .Max(..) ?? 0
will not work but the following will:
int maxShoeSize = Workers.Where(x = >x.CompanyId == 8).Max(x => (int?)x.ShoeSize) ?? 0;
It absolutely does not change the emitted SQL, but it does return 0 if the sequence is empty because it changes the Max()
to return an int?
instead of an int
.
Solution 6 - C#
int maxShoeSize=Workers.Where(x=>x.CompanyId==8)
.Max(x=>(int?)x.ShoeSize).GetValueOrDefault();
(assuming that ShoeSize
is of type int
)
If Workers
is a DbSet
or ObjectSet
from Entity Framework your initial query would throw an InvalidOperationException
, but not complaining about an empty sequence but complaining that the materialized value NULL can't be converted into an int
.
Solution 7 - C#
Max will throw System.InvalidOperationException "Sequence contains no elements"
class Program
{
static void Main(string[] args)
{
List<MyClass> list = new List<MyClass>();
list.Add(new MyClass() { Value = 2 });
IEnumerable<MyClass> iterator = list.Where(x => x.Value == 3); // empty iterator.
int max = iterator.Max(x => x.Value); // throws System.InvalidOperationException
}
}
class MyClass
{
public int Value;
}
Solution 8 - C#
NB: the query with DefaultIfEmpty()
may be significantly slower.
In my case that was a simple query with .DefaultIfEmpty(DateTime.Now.Date)
.
I was too lazy to profile it but obviously EF tried to obtain all the rows and then take the Max()
value.
Conclusion: sometimes handling InvalidOperationException
might be the better choice.
Solution 9 - C#
You can use a ternary within .Max()
to handle the predicate and set its value;
// assumes Workers != null && Workers.Count() > 0
int maxShoeSize = Workers.Max(x => (x.CompanyId == 8) ? x.ShoeSize : 0);
You would need to handle the Workers
collection being null/empty if that's a possibility, but it would depend on your implementation.
Solution 10 - C#
You can try this:
int maxShoeSize = Workers.Where(x=>x.CompanyId == 8).Max(x => x.ShoeSize) ?? 0;
Solution 11 - C#
You could check if there are any workers before doing the Max().
private int FindMaxShoeSize(IList<MyClass> workers) {
var workersInCompany = workers.Where(x => x.CompanyId == 8);
if(!workersInCompany.Any()) { return 0; }
return workersInCompany.Max(x => x.ShoeSize);
}