Batch update should throw meaningful exception if navigation property is used

[yegorandrosov]

Code to reproduce

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Z.EntityFramework.Plus;

namespace ConsoleApp1
{
    public class Selection
    {
        public int Id { get; set; }

        public int Status { get; set; }

        public virtual ICollection<BetSelection> Selections { get; set; }
    }

    public class BetSelection
    {
        public int Id { get; set; }

        public int Status { get; set; }

        public int SelectionId { get; set; }

        public virtual Selection Selection { get; set; }
    }

    public class AppDbContext : DbContext
    {
        public DbSet<Selection> Selections { get; set; }

        public DbSet<BetSelection> BetSelections { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=DESKTOP-5PVJ0I5;Database=zefcore-so12;Integrated Security=true");

            base.OnConfiguring(optionsBuilder);
        }
    }

    class Program
    {
        static async Task Main(string[] args)
        {
            BatchUpdateManager.BatchUpdateBuilder = builder => {
                builder.Executing = c =>
                {
                    Console.WriteLine(c.CommandText);
                };
            };

            var db = new AppDbContext();

            var selection = db.Selections.Add(new Selection()
            {
                Status = 1,
            });

            db.BetSelections.Add(new BetSelection()
            {
                Selection = selection.Entity,
                Status = 2,
            });

            db.SaveChanges();

            await db.BetSelections
                .UpdateAsync(x => new BetSelection() { Status = x.Selection.Status });
        }
    }
}

Description

As I can see, batch update with navigation properties is not implemented. Correct me, if I am wrong. It either throws only 'Invalid column name' exception or it does not even occur because of failed sql generation.

await db.BetSelections.UpdateAsync(x => new BetSelection() { Status = x.Selection.Status });

generates

UPDATE A
SET A.[Status] = B.[Status]
FROM [BetSelections] AS A
INNER JOIN ( SELECT [b].[Id], [b].[SelectionId], [b].[Status]
FROM [BetSelections] AS [b]
           ) AS B ON A.[Id] = B.[Id]

which literally does nothing. If you rename Selection.Status to something else, you will get invalid column name exception

   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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Z.EntityFramework.Extensions.BatchUpdate.<>c.(DbCommand , Nullable`1 )
   at Z.EntityFramework.Extensions.BatchUpdate.Execute[T](IQueryable`1 query, Expression`1 updateFactory)
   at Z.EntityFramework.Plus.BatchUpdateExtensions.Update[T](IQueryable`1 query, Expression`1 updateFactory, Action`1 batchUpdateBuilder)
   at Z.EntityFramework.Plus.BatchUpdateExtensions.<>c__DisplayClass2_0`1.<UpdateAsync>b__0()
   at System.Threading.Tasks.Task`1.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at ConsoleApp1.Program.<Main>d__0.MoveNext() in C:\Users\jjjjjjjjjjjj\source\repos\ConsoleApp1\ConsoleApp1\Program.cs:line 69

this happens due to invalid SQL generated

UPDATE A
SET A.[Status] = B.[Status111]
FROM [BetSelections] AS A
INNER JOIN ( SELECT [b].[Id], [b].[SelectionId], [b].[Status]
FROM [BetSelections] AS [b]
           ) AS B ON A.[Id] = B.[Id]

Further technical details

• EF version: 3.1.9
• EF Plus version: 3.0.68

[JonathanMagnan]

Hello @yegorandrosov ,

Thank you for reporting, my developer will look at it and indeed provide a better error message if we cannot yet support this scenario.

Best Regards,

Jon

---

Performance Libraries context.BulkInsert(list, options => options.BatchSize = 1000); Entity Framework Extensions • Entity Framework Classic • Bulk Operations • Dapper Plus

Runtime Evaluation Eval.Execute("x + y", new {x = 1, y = 2}); // return 3 C# Eval Function • SQL Eval Function

[JonathanMagnan]

Hello @yegorandrosov ,

Unfortunately, my developer didn't succeed yet to make it works.

The major problem is both column Status have the same time which currently makes it too hard to handle.

We are currently completing the model re-write for EF Core 5 (which will also be used in EF Core 3).

So we will probably re-visit this request later but for now, we need to pause it.