Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL Server: cannot batch-insert entities with generated values only that have a computed column #27507

Closed
roji opened this issue Feb 25, 2022 · 0 comments
Assignees
Labels
area-save-changes area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-enhancement
Milestone

Comments

@roji
Copy link
Member

roji commented Feb 25, 2022

If an entity type has only generated values, trying to insert a number of entities which exceeds Min Batch Size throws:

fail: 2/25/2022 13:51:03.366 CoreEventId.SaveChangesFailed[10000] (Microsoft.EntityFrameworkCore.Update) 
      An exception occurred in the database while saving changes for context type 'BlogContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The column "GeneratedOnUpdate" cannot be modified because it is either a computed column or is the result of a UNION operator.

The SQL:

SET NOCOUNT ON;

DECLARE @inserted0 TABLE ([Id] int);

INSERT INTO [WithAllDatabaseGenerated] ([GeneratedOnUpdate])
OUTPUT INSERTED.[Id]
INTO @inserted0
VALUES (DEFAULT),
(DEFAULT),
(DEFAULT),
(DEFAULT);

SELECT [t].[Id], [t].[GeneratedOnUpdate] FROM [WithAllDatabaseGenerated] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id]);
Repro
await using var ctx = new BlogContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

for (var i = 0; i < 3; i++)
{
    ctx.WithAllDatabaseGenerated.Add(new());
}

await ctx.SaveChangesAsync();

public class BlogContext : DbContext
{
    public DbSet<WithAllDatabaseGenerated> WithAllDatabaseGenerated { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<WithAllDatabaseGenerated>()
            .Property(w => w.GeneratedOnUpdate)
            .HasComputedColumnSql("80");
    }
}

public class WithAllDatabaseGenerated
{
    public int Id { get; set; }
    public int GeneratedOnUpdate { get; }
}

/cc @AndriySvyryd, discovered while working on #27372.

@roji roji self-assigned this Feb 25, 2022
@roji roji changed the title SQL Server: cannot batch-insert entities with generated values only SQL Server: cannot batch-insert entities with generated values only that have a computed column Feb 25, 2022
@ajcvickers ajcvickers added this to the 7.0.0 milestone Mar 1, 2022
roji added a commit to roji/efcore that referenced this issue Mar 2, 2022
roji added a commit to roji/efcore that referenced this issue Mar 2, 2022
@roji roji closed this as completed in fe5bebc Mar 4, 2022
@roji roji added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Mar 4, 2022
@ajcvickers ajcvickers modified the milestones: 7.0.0, 7.0.0-preview3 Mar 15, 2022
@ajcvickers ajcvickers modified the milestones: 7.0.0-preview3, 7.0.0 Nov 5, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area-save-changes area-sqlserver closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-enhancement
Projects
None yet
Development

No branches or pull requests

2 participants