Efcore: "Invalid column name" message is not helpful

[vanillajonathan]

The following error message is not so helpful:

Invalid column name 'Tenantd'.

I might have hundreds of entities which all have a "TenantId" column. It makes it very difficult to pinpoint the exact source of the problem.

Could the error message be improved to contain more contextual information such as the relevant entity or table?

[ajcvickers]

@vanillajonathan Please post the full stack trace.

[vanillajonathan]

This has occurred to me several times during configuring using fluent configuration (due to the mistakes that I made in the configuration). I can easily reproduce this by calling the HasColumnName method with something bogus.

Example:

modelBuilder.Entity<Blog>()
    .Property(b => b.TenantId)
    .HasColumnName("SomethingBogus");

Invalid column name 'SomethingBogus'.

Then it will say "Invalid column name 'SomethingBogus'.", but without stating where it looked.

Stacktrace

   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) in /_/src/System.Private.CoreLib/shared/System/Collections/Generic/List.cs:line 86
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Foo.Bar.<PushVoucherForAllAsync>d__4.MoveNext() in C:\Foo\Bar.cs:line 37
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in /_/src/System.Private.CoreLib/shared/System/Runtime/ExceptionServices/ExceptionDispatchInfo.cs:line 63
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 180
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 151
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult() in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 107
   at Foo.Program.<Main>d__0.MoveNext() in C:\Foo\Program.cs:line 40
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in /_/src/System.Private.CoreLib/shared/System/Runtime/ExceptionServices/ExceptionDispatchInfo.cs:line 63
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 180
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 151
   at System.Runtime.CompilerServices.TaskAwaiter.GetResult() in /_/src/System.Private.CoreLib/shared/System/Runtime/CompilerServices/TaskAwaiter.cs:line 107
   at Foo.Program.<Main>(String[] args)

[ajcvickers]

@vanillajonathan This error is generated by SQL Server. When you ask, "where it looked" it means SQL Server looked for the column in the table, and it wasn't in that table, so I guess SQL Server could add the table to the message. You could try asking this in https://github.com/dotnet/SqlClient, but I doubt that they will be able to do much either--it likely requires a change in SQL Server itself.

[BarsikV]

Sorry to reopen this, but I feel like this error is hard to debug, when you have many columns with the same name in different entities. I believe it is possible to extract the context and the data that points to the exact entity, which have caused the error.
Maybe some kind of Configuration.Assert() would do, which could check the validity of the columns separately for each entity.

[vanillajonathan]

Yes, it is very hard to debug when you have many columns with the same name in different entities. I think that your idea of validating the columns of each entity separately sounds interesting.

[smitpatel]

The invalid column exception comes from SqlServer rather than EF Core generating it. Further it is not necessarily column name you have in property. It could be something altogether different if the column is being lifted from a subquery and has an alias.

Best way to debug such query is to get the SQL which generated the error. You can use TagWith API to correlate from LINQ query to generated SQL. If you put the SQL which caused the error, in SSMS (or any other tool which has code completion for SQL), it will highlight bad column reference. From there it is easy to track down why the column name did not exist. e.g. if it is because entity type configured incorrect column name then in the SQL you can see which table is trying to pull incorrect column and go to entity type mapped to that table.