Subquery returned more than 1 value error when using Update-Database in Entity Framework

Question

I am using Entity Framework and trying to run a database migration from the Package Manager Console in Visual Studio. When I do, I get the following error and stack trace.

PM> Update-Database -Verbose Using StartUp project 'Project'. Using NuGet project 'DataModels'. Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Target database is: 'Database' (DataSource: Server, Provider: System.Data.SqlClient, Origin: Configuration). Applying explicit migrations: [201612061704158_MigrationName]. Applying explicit migration: 201612061704158_MigrationName. ALTER TABLE [dbo].[TableName] ADD [ColumnName] [bit] NOT NULL DEFAULT 0 System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext1 c) at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClass30.<ExecuteStatements>b__2e() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation) at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements, DbTransaction existingTransaction) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable1 operations, IEnumerable1 systemOperations, Boolean downgrading, Boolean auto)
at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) at System.Data.Entity.Migrations.DbMigrator.<>c__DisplayClassc.b__b() at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.Run() at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate) at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
at System.Data.Entity.Migrations.Design.ToolingFacade.Run(BaseRunner runner) at System.Data.Entity.Migrations.Design.ToolingFacade.Update(String targetMigration, Boolean force) at System.Data.Entity.Migrations.UpdateDatabaseCommand.<>c__DisplayClass2.<.ctor>b__0() at System.Data.Entity.Migrations.MigrationsDomainCommand.Execute(Action command) ClientConnectionId:40e2fc22-5bbd-4d83-88f3-ce5b2bc378dc Error Number:512,State:1,Class:16 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

I can't figure out what's going on here. Running the SQL statement directly works fine

ALTER TABLE [dbo].[TableName] ADD [ColumnName] [bit] NOT NULL DEFAULT 0

This is what the migration file looks like

public partial class MigrationName : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.TableName", "ColumnName", c => c.Boolean(nullable: false));
    }

    public override void Down()
    {
        DropColumn("dbo.TableName", "ColumnName");
    }
}

If I then comment out the contents of Up() in the migration file, I can successfully run the (empty) migration.

Also, if I run all the migrations, including this one, against an empty database, everything works fine. So there's some issue with the data that is causing the error (which is expected, given the error message).

I've tried looking at SQL Server Profiler to try to find out what SQL is being executed, but nothing is showing up.

I have about 20 other migrations that worked well up until this point, but this one and the previous one are consistently throwing this error.

Does anyone know what the underlying issue might be, or how I can get more information about what's going wrong?

Answer 1

My connection string had MultipleActiveResultSets=True set. Removing that from the connection string solved the issue.

Answer 2

Have you try to write the migration yourself. I would do something like this

public override void Up()
{
    DropColumn("dbo.TablaName", "ColumnName");
    AddColumn("dbo.TableName", "ColumnName", c => c.Boolean(nullable: false));
}

OR

public override void Up()
{
    AlterColumn("dbo.TableName", "ColumnName", c => c.Boolean(nullable: false));
}

OR just add a column

 public override void Up()
{
    DropColumn("dbo.TablaName", "ColumnName");
    AddColumn("dbo.TableName", "ColumnName", c => c.Boolean(nullable: false));
}

I would leave the Down empty.

public override void Down(){}

Cheers!

Answer 3

Changing the connection string also worked for me as Kris suggested, but I was concerned about how that would affect anything else using the same connection string.

What also worked for me was to empty out the Up() and Down() Methods, run the migration and roll back to the previous migration using update-database -targetmigration:"PreviousMigrationName". Once this was done I could recreate the migration and run it without changing the connection string.