Pomelo.entityframeworkcore.mysql: Getting error as "Too many columns" on retrieving the entity from DB

[deepak-khopade-isdr]

Steps to reproduce

Add DB records and try retrieving them through EF Core 3.0

The issue

Getting error (looks like from DB) when retrieving the entities from DB.

Exception message: Too many columns
Stack trace: at MySqlConnector.Core.ResultSet.<ScanRowAsync>g__ScanRowAsyncAwaited|9_0(ResultSet this_, Task`1 payloadTask, Row row_, CancellationToken token) in C:\\projects\\mysqlconnector\\src\\MySqlConnector\\Core\\ResultSet.cs:line 225\n   at MySqlConnector.Core.ResultSet.ReadAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\\projects\\mysqlconnector\\src\\MySqlConnector\\Core\\ResultSet.cs:line 179\n   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.AsyncQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()\n   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)\n   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)\n   at

Further technical details

MySQL version: Ver 14.14 Distrib 5.7.27, for Linux (x86_64)
Operating system: Ubuntu 16.04
Pomelo.EntityFrameworkCore.MySql version: 3.0.0-rc1.final
Microsoft.AspNetCore.App version: 3.0.0

[jespersh]

In case no one sees this, Too Many Columns is supposedly neither from Pomelo or mysqlconnector but from the mysql server.
So I think your query is just too damn powerful (over 4096 columns seem to be the hard limit). Add query logging (https://docs.microsoft.com/en-us/ef/core/miscellaneous/logging?tabs=v3) in OnConfiguring to see what is being generated.
I am unsure if joins count towards the too many columns 🤔

[lauxjpn]

Please post the C# code you use for the query, the generated SQL of the query and the CREATE TABLE SQL statements of the involved tables.

[deepak-khopade-isdr]

@jespersh : I am checking with the logs to see the query, however, the same code is working just well with .NET Core 1.1, EF Core 1.1 and Pomelo.EntityFrameworkCore.MySql 1.1.2 and for last 2+ yrs :).

So all I have done in last 1 week is just upgraded from .NET Core 1.1 to .NET Core 3.0. No core code is changed for this except few adjustments for UseMvc in Startup.cs.

[deepak-khopade-isdr]

@lauxjpn, @jespersh ... I found the problem when trying to .Include() dependent entities where its adding all columns of all entities in giant query which is odd; it never use to do this until the versions i have mentioned above. (1.1). As stated earlier again, no code has been changed at all except few tweaks in Startup.cs.

[mguinness]

You're probably impacted by changes to Including multiple levels:

Since version 3.0.0, each Include will cause an additional JOIN to be added to SQL queries produced by relational providers, whereas previous versions generated additional SQL queries. This can significantly change the performance of your queries, for better or worse. In particular, LINQ queries with an exceedingly high number of Include operators may need to be broken down into multiple separate LINQ queries in order to avoid the cartesian explosion problem.

See https://github.com/aspnet/EntityFrameworkCore/issues/12098 for more details.

[deepak-khopade-isdr]

@mguinness : quick question. So do you mean multiple level joins are not allowed anymore?

[mguinness]

They're still allowed but the generated SQL will be different as you've discovered. See https://github.com/aspnet/EntityFrameworkCore/issues/18022#issuecomment-536071161 for a possible workaround.

You'll be better off asking there or on Stack Overflow a way to mitigate your issue.

[lauxjpn]

Another representation of this change in EF Core is #874.