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
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.
User contributions licensed under CC BY-SA 3.0