Entity Framework throws exception on updates only - Invalid object name 'dbo.BirdBrain.service'

5

I've created an EF Core model from an existing database and all operations on entities work EXCEPT for updates; on updates EF Core is using an incorrect database name.

eg. When I perform an update I get a SqlException: Invalid object name 'BirdBrain.dbo.services'.. BirdBrainContext is the name of my DbContext, but the database I'm connecting to is BirdBrain_test.

I tried updating from EF Core 2.1 to EF Core 2.2 but the issue persists. When connecting to the production database named BirdBrain the same code functions perfectly.

I am initializing my context using a connection string like the following

Server=****;Database=BirdBrain_test;User Id=****;Password=****;Trusted_Connection=False;Multisubnetfailover=true;

and I do not know how this leads to updates being run against 'BirdBrain.dbo.services' when the database is BirdBrain_test.

Relevant DbContext code

public class BirdBrainContextFactory
{
    private readonly string _connectionString;

    public BirdBrainContextFactory(string connectionString)
    {
        _connectionString = connectionString;
    }

    public BirdBrainContext Create()
    {
        var optionsBuilder = new DbContextOptionsBuilder<BirdBrainContext>();
        optionsBuilder.UseSqlServer(_connectionString);
        return new BirdBrainContext(optionsBuilder.Options);
    }
}

public class BirdBrainContext : DbContext
{
    public BirdBrainContext(DbContextOptions<BirdBrainContext> options)
        : base(options)
    {
    }

    public DbSet<Service> Services { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Service>(entity =>
        {
            entity.HasIndex(e => e.Tag)
                .IsUnique();

            entity.Property(e => e.CreatedAt)
                .HasDefaultValueSql("(getutcdate())");

            entity.Property(e => e.UpdatedAt)
                .HasDefaultValueSql("(getutcdate())");
        });
    }
}

The table for the associated I am using the TableAttribute to refer to the table name.

[Table("services")]
public class Service
{
  ...
}

Relevant Update code

public Service UpdateService(Service service)
{
    using (var context = _contextFactory.Create())
    {
        EnforceServiceExists(context, service);
        context.Entry(service).State = EntityState.Modified;
        context.SaveChanges();
        return service;
    }
}

Gets, Inserts, and Deletes work on this same table.

Microsoft.EntityFrameworkCore.DbUpdateException
  HResult=0x80131500
  Message=An error occurred while updating the entries. See the inner exception for details.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at DataService.BirdBrainContext.SaveChanges() in C:\git\BirdBrainAPI\src\DataService\BirdBrainContext.cs:line 180
...

Inner Exception 1:
SqlException: Invalid object name 'BirdBrain.dbo.services'.

EDIT: I added EF Core logging as Ivan Stoev suggested and pasted the results for the update below. It looks like EF is connecting to the BirdBrain_test database and running a UPDATE [services] ... as opposed to UPDATE [BirdBrain].[dbo].[services] as the error would suggest. Still not sure what is going on.

dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'BirdBrain_test' on server '****'.
dbug: Microsoft.EntityFrameworkCore.Database.Transaction[20200]
      Beginning transaction with isolation level 'ReadCommitted'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@p19='?' (DbType = Int32), @p0='?' (DbType = DateTime), ...], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [services] SET [created_at] = @p0, ... , [updated_at] = @p18
      WHERE [id] = @p19;
      SELECT @@ROWCOUNT;
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (61ms) [Parameters=[@p19='?' (DbType = Int32), @p0='?' (DbType = DateTime), ...], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      UPDATE [services] SET [created_at] = @p0, ... , [updated_at] = @p18
      WHERE [id] = @p19;
      SELECT @@ROWCOUNT;
System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'BirdBrain.dbo.services'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
ClientConnectionId:b2b87fd1-8e34-4fcf-80f1-290de30b28dd
Error Number:208,State:1,Class:16
c#
sql
sql-server
entity-framework
ef-core-2.2
asked on Stack Overflow Jul 12, 2019 by Sean Boczulak • edited Jul 24, 2019 by GalAbra

1 Answer

2

The issue was that there was an underlying update trigger on the database that was using the BirdBrain.dbo.services naming. I tested changing naming to services from BirdBrain.dbo.services and this resolved the error. Ultimately I removed the trigger entirely as the logic it handled was unnecessary at the present date.

To figure this out I added logging to the DbContext as @IvanStoev suggested and confirmed that that EF was in fact targetting the services object correctly and not BirdBrain.dbo.services as I originally believed.

I then connected to the database using SSMS and expanded the table to look at its triggers, which was where I found the offending trigger.

In case you are facing a similar issue, you should check if there are any triggers on the table you are modifying.

answered on Stack Overflow Jul 15, 2019 by Sean Boczulak

User contributions licensed under CC BY-SA 3.0