efcore: Error by using include command from SQL-Server: maximum of 8060

In net core 2.2 the “include - command” works without error. After we change to net core 3.0 we get follow error:

System.Data.SqlClient.SqlException: Cannot create a row of size 8213 which is greater than the allowable maximum of 8060. The statement has been terminated.

Is it because the queries are now running on the server side???

How should we catch this error? We have no options at all!

Steps to reproduce

Query Code:

result = _context.kinder.AsNoTracking() .Where(k => kinderAusgewaehlt.Contains(k.kind_id)) .Include(k => k.abrechnungNavigation) .Include(k => k.kontoinhaberNavigation) .Include(k => k.schuleNavigation) .Include(k => k.versicherer_abweichendNavigation) .Include(k => k.kigazuordnung).ThenInclude(z => z.bezugserzieherNavigation) .Include(k => k.kigazuordnung).ThenInclude(z => z.mandant_adressennrNavigation).ThenInclude(m => m.adressennrNavigation) .Include(k => k.kigazuordnung).ThenInclude(z => z.gruppenartnrNavigation) .Include(k => k.kigazuordnung).ThenInclude(z => z.gruppennrNavigation) .Include(k => k.kindermerkmale) .Include(k => k.kinderarztNavigation) .Include(k => k.adressennrNavigation).ThenInclude(a => a.sprache_) .Include(k => k.adressennrNavigation).ThenInclude(a => a.sprache_id_2Navigation) .Include(k => k.adressennrNavigation).ThenInclude(a => a.sprache_id_3Navigation) .Include(k => k.adressennrNavigation).ThenInclude(a => a.sprache_id_4Navigation) .Include(k => k.adressennrNavigation).ThenInclude(a => a.adressen_bankverbindungadressennrNavigation).ThenInclude(b => b.kontoinhaberNavigation) .Include(k => k.adressennrNavigation).ThenInclude(a => a.nationalitaetnrNavigation) .Include(k => k.adressennrNavigation).ThenInclude(a => a.krankheitenz).ThenInclude(k => k.krankheitsnrNavigation) .Include(k => k.adressennrNavigation).ThenInclude(a => a.impfungenz).ThenInclude(i => i.impfungsnrNavigation) .Include(k => k.adressennrNavigation).ThenInclude(a => a.einverstaendnisz).ThenInclude(e => e.einverstaendnisnrNavigation) .OrderBy(k => k.adressennrNavigation.name1) .ThenBy(k => k.adressennrNavigation.vorname) .ThenBy(k => k.adressennrNavigation.adressennr).ToList();

Got Exceptions? Include both the message and the stack trace

System.Data.SqlClient.SqlException: Cannot create a row of size 8213 which is greater than the allowable maximum of 8060. The statement has been terminated.

Further technical details

EF Core version: 3.1.1 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: NET Core 3.1.1 Operating system: Windows 10 IDE: Visual Studio 2019 16.4.5

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 16 (3 by maintainers)

Most upvoted comments

Essentially it is a row-overflow which in SqlServer cannot exceed 8KB. Certain types like (n)varchar/varbinary are excluded from limit by using pointer to different page. But data of other types in single row must fit in the size. With those many includes, it is likely that you hit that limitation. One work-around would be to split your query in 2 and load half of the include at a time, so that row size does not cross the limit.