DefaultIfEmpty异常“错误或限制"；使用EF Core [英] DefaultIfEmpty Exception "bug or limitation" with EF Core

问题描述

我尝试执行以下代码:

await _dbContext.Customers.Select(x => x.CustomerNr).DefaultIfEmpty(0).MaxAsync() + 1;

从本质上讲，它必须从数据库中获取最高的客户编号并将其添加1.如果客户表为空，则应返回0，再加上1.为什么会出现以下异常:

Essentially it has to get the highest customer number from the database and add 1 to it. If the customer table is empty, it should return 0 to which I add 1. Why is it giving me the following exception:

修改:我正在使用.NET Core 3.1和EF Core 3.0.1(与EF Core 3.1.0相同的错误)

I am using .NET Core 3.1 and EF Core 3.0.1 (Same error for EF Core 3.1.0)

推荐答案

避免使用默认值 DefaultIfEmpty 重载-EF Core查询翻译器不支持此重载.

Avoid DefaultIfEmpty overload with default value - it's not supported by EF Core query translator.

除了左外部连接模式外，通常还避免对其他任何情况使用 DefaultIfEmpty 无参数重载，因为尽管支持，但SQL转换还是很奇怪的.

Also in general avoid DefaultIfEmpty parameterless overload for anything else than left outer join pattern because while it is supported, SQL translation is quite weird.

要解决在空集上应用 Max ， Min 和 Average 方法的问题，请使用可为空的重载，该重载将返回 null表示空集，并在需要时将 null 结果转换为0(或其他所需的幻值).

To solve the problem with applying Max, Min and Average methods on empty sets, use the nullable overloads which return null for empty set, and convert null result to 0 (or other desired magic value) if needed.

将其应用于您的方案将是这样(假设 CustomerNr 类型为 int ):

Applying it to your scenario would be somethong like this (assuming the CustomerNr type is int):

(await _dbContext.Customers.MaxAsync(x => (int?)x.CustomerNr)) ?? 0 + 1; 

这篇关于DefaultIfEmpty异常“错误或限制"；使用EF Core的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！