Entity Framework calling MAX on null on Records

user_45909 0
Viewed 32183

When calling Max() on an IQueryable and there are zero records I get the following exception.

The cast to value type 'Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

var version = ctx.Entries
    .Where(e => e.Competition.CompetitionId == storeCompetition.CompetitionId)
    .Max(e => e.Version);

Now I understand why this happens my question is how is the best way to do this if the table can be empty. The code below works and solves this problem, but its very ugly is there no MaxOrDefault() concept?

int? version = ctx.Entries
    .Where(e => e.Competition.CompetitionId == storeCompetition.CompetitionId)
    .Select(e => (int?)e.Version)
    .Max();
entity-framework
8 Answers

I want to suggest a merge from the existing answers:

@divega answer works great and the sql output is fine but because of 'don't repeat yourself' an extension will be a better way like @Code Uniquely showed. But this solution can output more complex sql as you needed. But you can use the following extension to bring both together:

 public static int MaxOrZero<TSource>(this IQueryable<TSource> source, 
 Expression<Func<TSource, int>> selector)
        {
            var converted = Expression.Convert(selector.Body, typeof(int?));
            var typed = Expression.Lambda<Func<TSource, int?>>(converted, selector.Parameters);

            return source.Max(typed) ?? 0;
        }
user_55066730

You can use:

FromSqlRaw("Select ifnull(max(columnname),0) as Value from tableName");
user_105436670
Related