A simple looking delete query fails with error: Invalid column name 'Id'

[vijay-singh-16]

This piece of code:

var baseQuery =  context.OrderItems;
var latest3 = baseQuery.OrderByDescending(r => r.Id).Take(3);
var toDelete = baseQuery.Except(latest3);
var deletedItems = toDelete.DeleteFromQuery();

Fails with this exception:

Stack Trace:

[System.Data.SqlClient.SqlException: Invalid column name 'Id'.]
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Scalar>b__6(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Scalar(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at Z.EntityFramework.Extensions.BatchDelete.<>c.(DbCommand , DbCommandInterceptionContext )
   at Z.EntityFramework.Extensions.BatchDelete.Execute[T](IQueryable`1 query)
   at DbContextExtensions.[](IQueryable`1 , Action`1 , CancellationToken )
   at DbContextExtensions.DeleteFromQuery[TEntity](IQueryable`1 query, Action`1 bulkOperationFactory)
   at Program.Main() :line 44

.NET Fiddle
https://dotnetfiddle.net/6N3nfr

Thanks

[JonathanMagnan]

Hello @vijay-singh-16 ,

Thank you for reporting.

We will look at it to check if we can easily support this issue caused by the Except or not.

Best Regards,

Jon

[JonathanMagnan]

Hello @vijay-singh-16,

We released a quick fix today. The most basic case should now be working, and other cases will throw a custom error message.

My developer is currently working on improving the fix.

Best Regards,

Jon