SaveChangesAsync works only once, then it always fails

asp.net-core-webapi c# entity-framework-core

Question

I'm experiencing a strange issue with one of my queries. When I execute it for the first time it works but all subsequent calls fail with

Cannot insert explicit value for identity column in table 'Message'.

It also works again when I restart the web-service.

The schema of my tables is:

internal class Message
{
    public long Id { get; set; }
    public int TimeRangeId { get; set; }
    public byte[] Body { get; set; }
    public string BodyHash { get; set; }
    public DateTime? DeletedOn { get; set; }
    #region Navigation properties
    public TimeRange TimeRange { get; set; }
    #endregion
}

internal class Queue
{
    public int Id { get; set; }
    public string Name { get; set; }
    #region Navigation properties
    public TimeRange TimeRange { get; set; }
    #endregion
}

internal class TimeRange
{
    public int Id { get; set; }
    public int QueueId { get; set; }
    public DateTime StartsOn { get; set; }
    public DateTime EndsOn { get; set; }
    public DateTime CreatedOn { get; set; }
    #region Navigation properties
    public Queue Queue { get; set; }
    public List<Message> Messages { get; set; }
    #endregion
}

where the context is setup as following:

internal class MessageQueueContext : DbContext
{
    private readonly string _connectionString;

    private readonly string _schema;

    public MessageQueueContext(string connectionString, string schema)
    {
        _connectionString = connectionString;
        _schema = schema;
    }

    public DbSet<TimeRange> TimeRanges { get; set; }

    public DbSet<Message> Messages { get; set; }

    public DbSet<Queue> Queues { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(_connectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {            
        modelBuilder.Entity<Message>(e =>
        {
            e.ToTable(nameof(Message), _schema).HasKey(t => t.Id);
            e.Property(p => p.Id).UseSqlServerIdentityColumn();
            e.Property(p => p.TimeRangeId).IsRequired();
            e.Property(p => p.Body).IsRequired();
            e.Property(p => p.BodyHash).IsRequired();
        });

        modelBuilder.Entity<TimeRange>(e =>
        {
            e.ToTable(nameof(TimeRange), _schema).HasKey(t => t.Id);
            e.Property(p => p.Id).UseSqlServerIdentityColumn();
            e.Property(p => p.StartsOn).IsRequired();
            e.Property(p => p.EndsOn).IsRequired();
            e.Property(p => p.CreatedOn).ValueGeneratedOnAdd();
        });

        modelBuilder.Entity<Queue>(e =>
        {
            e.ToTable(nameof(Queue), _schema).HasKey(t => t.Id);
            e.Property(p => p.Id).UseSqlServerIdentityColumn();
            e.Property(p => p.Name).IsRequired();
        });
    }
}

The query that fails is this one:

public async Task<int> EnqueueAsync(DateTime timeRangeStartsOn, DateTime timeRangeEndsOn, IEnumerable<byte[]> bodies, CancellationToken cancellationToken)
{
    const int nothingEnqueued = 0;

    using (var context = new MessageQueueContext(_connectionString, _schema))
    {
        context.Queues.Add(_queue.Value);
        context.Entry(_queue.Value).State = EntityState.Unchanged;

        var messages =
            (from body in bodies
             let bodyHash = _computeBodyHash(body).ToHexString()
             where true // CanEnqueueDuplicates || !context.Messages.AsNoTracking().Any(m => m.BodyHash == bodyHash && m.DeletedOn == null)
             select new Message
             {
                 Body = body,
                 BodyHash = bodyHash
             }).ToList();

        if (!CanEnqueueEmptyTimeRange && !messages.Any())
        {
            return nothingEnqueued;
        }

        var timeRange = new Entities.TimeRange
        {
            Queue = _queue.Value,
            StartsOn = timeRangeStartsOn,
            EndsOn = timeRangeEndsOn,
            Messages = messages
        };

        await context.TimeRanges.AddAsync(timeRange, cancellationToken);
        return await context.SaveChangesAsync(cancellationToken);
    }
}

It properly generates the Message.Id and it's also properly set in the database as Identity.


I debugged it with the SQL Server Profiler and found that the second request is dfferent from the first one:

This is what the first call generates:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [smq].[TimeRange] ([EndsOn], [QueueId], [StartsOn])
VALUES (@p0, @p1, @p2);
SELECT [Id], [CreatedOn]
FROM [smq].[TimeRange]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

',N'@p0 datetime2(7),@p1 int,@p2 datetime2(7)',@p0='2018-05-02 00:00:00',@p1=1,@p2='2018-05-01 00:00:00'


exec sp_executesql N'SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Id] bigint, [_Position] [int]);
MERGE [smq].[Message] USING (
VALUES (@p3, @p4, @p5, @p6, 0),
(@p7, @p8, @p9, @p10, 1)) AS i ([Body], [BodyHash], [DeletedOn], [TimeRangeId], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Body], [BodyHash], [DeletedOn], [TimeRangeId])
VALUES (i.[Body], i.[BodyHash], i.[DeletedOn], i.[TimeRangeId])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;

SELECT [t].[Id] FROM [smq].[Message] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
ORDER BY [i].[_Position];

',N'@p3 varbinary(8000),@p4 varbinary(8000),@p5 datetime2(7),@p6 int,@p7 varbinary(8000),@p8 varbinary(8000),@p9 datetime2(7),@p10 int',@p3=0x7,@p4=0x7,@p5=NULL,@p6=21,@p7=0x7,@p8=0x1,@p9=NULL,@p10=21

And this is what EF generates with all subsequent calls:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [smq].[Message] ([Id], [Body], [BodyHash], [DeletedOn], [TimeRangeId])
VALUES (@p0, @p1, @p2, @p3, @p4),
(@p5, @p6, @p7, @p8, @p9);
INSERT INTO [smq].[TimeRange] ([EndsOn], [QueueId], [StartsOn])
VALUES (@p10, @p11, @p12);
SELECT [Id], [CreatedOn]
FROM [smq].[TimeRange]
WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

',N'@p0 bigint,@p1 varbinary(8000),@p2 varbinary(8000),@p3 datetime2(7),@p4 int,@p5 bigint,@p6 varbinary(8000),@p7 varbinary(8000),@p8 datetime2(7),@p9 int,@p10 datetime2(7),@p11 int,@p12 datetime2(7)',@p0=21,@p1=0x7,@p2=0x7,@p3=NULL,@p4=21,@p5=22,@p6=0x7,@p7=0x1,@p8=NULL,@p9=21,@p10='2018-05-02 00:00:00',@p11=1,@p12='2018-05-01 00:00:00'

Am I missing something obvious? It doesn't look normal that the calls are different. They shouldn't, am I right?


If you need any additional info let me know and I'll edit the question.

1
2
2/18/2018 12:16:20 PM

Popular Answer

I've found the bug.

private readonly Lazy<Entities.Queue> _queue;

public async Task<int> EnqueueAsync(DateTime timeRangeStartsOn, DateTime timeRangeEndsOn, IEnumerable<byte[]> bodies, CancellationToken cancellationToken)
{
    const int nothingEnqueued = 0;

    using (var context = new MessageQueueContext(_connectionString, _schema))
    {
        context.Queues.Add(_queue.Value);
        context.Entry(_queue.Value).State = EntityState.Unchanged;

It's the last two lines where I add the info about the current queue to the context. Since this is a field, it appears to survive between contexts and somehow demages it.

I changed this to

context.Queues.Attach(new Entities.Queue 
{
    Id = _queue.Value.Id, 
    Name = _queue.Value.Name 
});

and it now works without issues. Simply attaching does not fix the bug (it's just easier than adding and changing the state).

The real fix is creating a copy of the entity because there was something about it that the db-context didn't like. I guess one needs to know the internals of EF to understand it...

1
2/18/2018 1:49:08 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow