dataobjects-net icon indicating copy to clipboard operation
dataobjects-net copied to clipboard

Wrong bulk update query

Open letarak opened this issue 1 year ago • 1 comments

DO 7.1.1 Structure inner field bulk update produce wrong sql query, see code sample below

    private static async Task Main(string[] args)
    {
        try
        {
            DbHelper.ExecuteNonQuery("DROP DATABASE [DO-Tests]");
        }
        catch (Exception)
        {
        }

        DbHelper.ExecuteNonQuery("CREATE DATABASE [DO-Tests]");

        var dc = new DomainConfiguration("sqlserver", new SqlConnectionStringBuilder(DbHelper.ConnectionString()).ToString());

        dc.Sessions.Add(new SessionConfiguration(WellKnown.Sessions.Default) { BatchSize = 25 });
        
        dc.Types.Register(typeof(StructureEntity));
        dc.Types.Register(typeof(SqlLogModule));

        dc.UpgradeMode = DomainUpgradeMode.Recreate;

        await using var d = await Domain.BuildAsync(dc);

        await using var s = await d.OpenSessionAsync();
        await using var t = await s.OpenTransactionAsync();
        
        SqlLogModule.Enabled = true;
        
        await s.Query.All<StructureEntity>()
            .Set(it => it.Field1.Value, it => it.Field1.Value + it.Field2.Value)
            .UpdateAsync();
        
        await s.Query.All<StructureEntity>()
            .Set(it => it.Field2.Value, it => it.Field1.Value + it.Field2.Value)
            .UpdateAsync();
        
        await s.Query.All<StructureEntity>()
            .Set(it => it.Field1.Value, it => it.Field2.Value + it.Field1.Value)
            .UpdateAsync();
        
        await s.Query.All<StructureEntity>()
            .Set(it => it.Field2.Value, it => it.Field2.Value + it.Field1.Value)
            .UpdateAsync();
            
        SqlLogModule.Enabled = false;
    }

    [HierarchyRoot]
    public class StructureEntity : Entity
    {
        public StructureEntity(Session session) : base(session)
        {
        }

        [Key] [Field(Nullable = false)] public int Id { get; set; }
        
        [Field]
        public StructureField Field1 { get; set; }
        
        [Field]
        public StructureField Field2 { get; set; } 
    }
    
    public class StructureField : Structure
    {
        [Field]
        public decimal Value { get; set; }
    }

    /// <summary>SqlLogModule</summary>
    public class SqlLogModule : IModule
    {
        public static bool Enabled { get; set; }
        
        /// <inheritdoc />
        public void OnBuilt(Domain domain)
        {
            domain.SessionOpen += (source, args) =>
            {
                var accessor = args.Session.Events;

                accessor.DbCommandExecuting += DbCommandExecuting;
            };
        }

        /// <inheritdoc />
        public void OnDefinitionsBuilt(BuildingContext context, DomainModelDef model)
        {
        }

        private void DbCommandExecuting(object? sender, DbCommandEventArgs e)
        {
            if (Enabled)
            {
                Console.WriteLine(e.Command.CommandText);
            }
        }
    }

Output

UPDATE [dbo].[Program.StructureEntity] SET [Field1.Value] = ([Program.StructureEntity].[Field1.Value] + [Program.StructureEntity].[Field2.Value]);

UPDATE [dbo].[Program.StructureEntity] SET [Field1.Value] = ([Program.StructureEntity].[Field1.Value] + [Program.StructureEntity].[Field2.Value]);

UPDATE [dbo].[Program.StructureEntity] SET [Field1.Value] = ([Program.StructureEntity].[Field2.Value] + [Program.StructureEntity].[Field1.Value]);

UPDATE [dbo].[Program.StructureEntity] SET [Field1.Value] = ([Program.StructureEntity].[Field2.Value] + [Program.StructureEntity].[Field1.Value]);

letarak avatar Nov 23 '23 12:11 letarak