Entity Framework calling MAX on null on Records

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();

Yes, casting to Nullable of T is the recommended way to deal with the problem in LINQ to Entities queries. Having a MaxOrDefault() method that has the right signature sounds like an interesting idea, but you would simply need an additional version for each method that presents this issue, which wouldn't scale very well.

This is one of many mismatches between how things work in the CLR and how they actually work on a database server. The Max() method’s signature has been defined this way because the result type is expected to be exactly the same as the input type on the CLR. But on a database server the result can be null. For that reason, you need to cast the input (although depending on how you write your query it might be enough to cast the output) to a Nullable of T.

Here is a solution that looks slightly simpler than what you have above:

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

Hope this helps.

Try this to create a default for your max.

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

You could write a simple extension method like this, it returns the default value of type T if no records exist and is then apply Max to that or the query if records exist.

public static T MaxOrEmpty<T>(this IQueryable<T> query)
{
    return query.DefaultIfEmpty().Max();
}

and you could use it like this

maxId = context.Competition.Select(x=>x.CompetitionId).MaxOrEmpty();

I couldnt take no for an answer :) I have tested the below and it works, I havent checked the SQL generated yet so be careful, I will update this once I have tested more.

var test = ctx.Entries
    .Where(e => e.Competition.CompetitionId == storeCompetition.CompetitionId)
    .MaxOrDefault(x => x.Version);

public static TResult? MaxOrDefault<TSource, TResult>(this IEnumerable<TSource> source, Func<TSource, TResult> selector)
    where TResult : struct
{
    return source
        .Select(selector)
        .Cast<TResult?>()
        .Max();
}

Try this:

IEnumerable<AlertsResultset> alerts = null;

    alerts = (from POA in SDSEntities.Context.SDS_PRODUCT_ORDER_ALERT
              join A in SDSEntities.Context.SDS_ALERT on POA.ALERT_ID equals A.ALERT_ID
              orderby POA.DATE_ADDED descending
              select new AlertsResultset
              {
                  ID = POA.PRODUCT_ORDER_ALERT_ID == null ? 0:POA.PRODUCT_ORDER_ALERT_ID ,
                  ITEM_ID = POA.ORDER_ID.HasValue ? POA.ORDER_ID.Value : POA.PRODUCT_ID.Value,
                  Date = POA.DATE_ADDED.Value,
                  orderType = SDSEntities.Context.SDS_ORDER.Where(o => o.ORDER_ID == POA.ORDER_ID.Value).FirstOrDefault().ORDER_TYPE,
                  TransactionNumber = POA.PRODUCT_ID.HasValue ? (SDSEntities.Context.SDS_PRODUCT.Where(p => p.PRODUCT_ID == POA.PRODUCT_ID.Value).FirstOrDefault().TRANSACTION_NUMBER) : (SDSEntities.Context.SDS_ORDER.Where(o => o.ORDER_ID == POA.ORDER_ID.Value).FirstOrDefault().TRANSACTION_NUMBER),
                  Publisher = POA.PRODUCT_ID.HasValue ?
                  (
                  SDSEntities.Context.SDS_PRODUCT.Where(p => p.PRODUCT_ID == POA.PRODUCT_ID.Value).FirstOrDefault().PRODUCT_TYPE_NUMBER == "ISSUE" ? (from prod in SDSEntities.Context.SDS_PRODUCT
                                                                                                                                                      join ji in SDSEntities.Context.SDS_JOURNAL_ISSUE on prod.PRODUCT_ID equals ji.PRODUCT_ID
                                                                                                                                                      join j in SDSEntities.Context.SDS_JOURNAL on ji.JOURNAL_ID equals j.JOURNAL_ID
                                                                                                                                                      where prod.PRODUCT_ID == POA.PRODUCT_ID
                                                                                                                                                      select new { j.PUBLISHER_NAME }).FirstOrDefault().PUBLISHER_NAME : (from prod in SDSEntities.Context.SDS_PRODUCT
                                                                                                                                                                                                                          join bi in SDSEntities.Context.SDS_BOOK_INSTANCE on prod.PRODUCT_ID equals bi.PRODUCT_ID
                                                                                                                                                                                                                          join b in SDSEntities.Context.SDS_BOOK on bi.BOOK_ID equals b.BOOK_ID
                                                                                                                                                                                                                          where prod.PRODUCT_ID == POA.PRODUCT_ID
                                                                                                                                                                                                                          select new { b.PUBLISHER_NAME }).FirstOrDefault().PUBLISHER_NAME
                  )
                  : (SDSEntities.Context.SDS_ORDER.Where(o => o.ORDER_ID == POA.ORDER_ID.Value).FirstOrDefault().PUBLISHER_NAME),
                  Alert = A.ALERT_NAME,
                  AlertType = A.ALERT_TYPE,
                  IsFlagged = POA.IS_FLAGGED.Value,
                  Status = POA.ALERT_STATUS
              });

how about

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

less ugly, more elegant

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;
        }

You can use:

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