CODEWITHSUNDEEP
c#.net-coreef-core-3.0
mikeyy
mikeyy

Reputation: 933

DefaultIfEmpty().Max() still throws 'Sequence contains no elements.'

After I updated my project to dotnet core 3.0RC1 (might be in preview9 as well) my code that used to work 

var value = context.Products.Where(t => t.CategoryId == catId).Select(t => t.Version).DefaultIfEmpty().Max();

started throwing System.InvalidOperationException: Sequence contains no elements. The table in question is empty.

If I add ToList() so it looks like this DeafultIfEmpty().ToList().Max(), it starts to work again. Could not find any information about a breaking change. When I run 

var expectedZero = new List<int>().DefaultIfEmpty().Max();

it works fine. That made me think maybe something wrong with EF Core. Then I created test in xUnit with exactly the same setup but there tests are passing (table is empty as well, uses InMemoryDatabase instead of live SQL Server instance).

I am truly puzzled. Relevant stack trace:

System.InvalidOperationException: Sequence contains no elements.
   at int lambda_method(Closure, QueryContext, DbDataReader, ResultContext, int[], ResultCoordinator)
   at bool Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor+QueryingEnumerable<T>+Enumerator.MoveNext()
   at TSource System.Linq.Enumerable.Single<TSource>(IEnumerable<TSource> source)
   at TResult Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute<TResult>(Expression query)
   at TResult Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute<TResult>(Expression expression)
   at TSource System.Linq.Queryable.Max<TSource>(IQueryable<TSource> source)
   at ... (my method that run the code)

Edit

Product class:

   [Table("tmpExtProduct", Schema = "ext")]
    public partial class Product
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Version { get; set; }

        [Column(TypeName = "datetime")]
        public DateTime ImportDate { get; set; }

        public int CategoryId { get; set; }
        public string Description { get; set; }

        [ForeignKey(nameof(Ext.Category))]
        public int CategoryId { get; set; }        

        [InverseProperty(nameof(Ext.Category.Products))]
        public virtual Category Category { get; set; }
    }

2nd edit Sql produced by ef core

exec sp_executesql N'SELECT MAX([t0].[Version])
FROM (
    SELECT NULL AS [empty]
) AS [empty]
LEFT JOIN (
    SELECT [p].[Version], [p].[CategoryId], [p].[ImportDate], [p].[Description]
    FROM [ext].[tmpExtProduct] AS [p]
    WHERE (([p].[CategoryId] = @__categoryId_0) AND @__categoryId_0 IS NOT NULL)
) AS [t0] ON 1 = 1',N'@__categoryId_0 int',@__categoryId_0=5

Upvotes: 12

Views: 5336

Answers (3)

tia
tia

Reputation: 9718

One workaround might be using OrderBy and FirstOrDefault combination

var value = context.Products
    .Where(t => t.CategoryId == catId)
    .OrderByDescending(t => t.Version)
    .Select(t => t.Version)
    .FirstOrDefault();

I usually use this method and rarely use Max().

Upvotes: 4

Mr. Squirrel.Downy
Mr. Squirrel.Downy

Reputation: 1177

By the mikeyy's self-answer, an exception will still be throwed, but this exception message becomes to "Nullable object must have a value.". Because returns is null when sequence is empty, but null cannot be assigned to a value type int.

The correct thing to do is to cast the target value type to Nullable<>, which will return null instead of throw "Sequence contains no elements." or "Nullable object must have a value." exception when the sequence is empty.

var value = context.Products
    .Where(t => t.CategoryId == catId)
    .Select(t => (int?)t.Version)
    .Max();

Now, the type of variable value is Nullable<int>, remember to check it's null or not.

Upvotes: 0

mikeyy
mikeyy

Reputation: 933

So I opened the issue in EF Core repo and got an answer. Apparently this is current behaviour, that may change later.

The suggestion is to use the following approach

var valueFail = context.Products.Where(t => t.CategoryId == catId)
                .GroupBy(e => 1)
                .Select(t => t.Max(e => e.Version))
                .ToList()[0];

This is better than my workaround DeafultIfEmpty().ToList().Max() as it will do all the work server-side, while my solution will calculate Max() on the client.

Upvotes: 6

Related Questions