public void CheckDatabaseSchemaAgainstEntityFrameworkExpectedSchema()
{
using (var container = new Container(c =>
{
c.AddRegistry <ConfigurationRegistry>();
}))
{
var configuration = container.GetInstance <ProviderRelationshipsConfiguration>();
using (var connection = new SqlConnection(configuration.DatabaseConnectionString))
{
var optionsBuilder = new DbContextOptionsBuilder <ProviderRelationshipsDbContext>().UseSqlServer(connection);
using (var context = new ProviderRelationshipsDbContext(optionsBuilder.Options))
{
var config = new CompareEfSqlConfig
{
TablesToIgnoreCommaDelimited = "ClientOutboxData,OutboxData"
};
config.IgnoreTheseErrors("EXTRA IN DATABASE: SFA.DAS.ProviderRelationships.Database->Column 'Users', column name. Found = Id");
var comparer = new CompareEfSql(config);
var hasErrors = comparer.CompareEfWithDb(context);
hasErrors.Should().BeFalse(comparer.GetAllErrors);
}
}
}
}
public void CompareReadOnlyDbContextDifferentColumnType()
{
//SETUP
var options = this.CreateUniqueClassOptions <ReadOnlyDbContext>(
builder => builder.ReplaceService <IModelCacheKeyFactory, ReadOnlyModelCacheKeyFactory>());
using var context = new ReadOnlyDbContext(options, ReadOnlyDbContext.Configs.BadMappedToViewClass);
context.Database.EnsureClean();
context.Database.ExecuteSqlRaw(
"CREATE OR ALTER VIEW MyView AS SELECT Id, MyDateTime, MyString FROM NormalClasses");
var config = new CompareEfSqlConfig();
config.IgnoreTheseErrors(
"NOT CHECKED: Entity 'MappedToQuery', not mapped to database. Expected = <null>, found = MappedToQuery");
var comparer = new CompareEfSql(config);
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeTrue();
var errors = CompareLog.ListAllErrors(comparer.Logs).ToList();
(errors.Count == 2).ShouldBeTrue(comparer.GetAllErrors);
errors[0].ShouldEqual("DIFFERENT: MappedToViewBad->Property 'MyString', column type. Expected = int, found = nvarchar(max)");
errors[1].ShouldEqual("DIFFERENT: MappedToViewBad->Property 'MyString', nullability. Expected = NOT NULL, found = NULL");
}
public void CompareWithBookDatabase()
{
//SETUP
string connectionString;
using (var context = new BookContext(GetBookContextOptions()))
{
connectionString = context.Database.GetDbConnection().ConnectionString;
context.Database.EnsureCreated();
}
using (var context = new BookOrderContext(_options))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(connectionString, context);
//VERIFY
hasErrors.ShouldBeTrue();
var errors = CompareLog.ListAllErrors(comparer.Logs).ToList();
errors.Count.ShouldEqual(2);
errors[0].ShouldEqual(
"NOT IN DATABASE: Entity 'LineItem', table name. Expected = LineItem");
errors[1].ShouldEqual(
"NOT IN DATABASE: Entity 'Order', table name. Expected = Orders");
}
}
public void CompareReadOnlyDbContextIgnoreNormalClasses()
{
//SETUP
var options = this.CreateUniqueClassOptions <ReadOnlyDbContext>(
builder => builder.ReplaceService <IModelCacheKeyFactory, ReadOnlyModelCacheKeyFactory>());
using var context = new ReadOnlyDbContext(options);
context.Database.EnsureClean();
var filepath = TestData.GetFilePath("AddViewToDatabase.sql");
context.ExecuteScriptFileInTransaction(filepath);
var config = new CompareEfSqlConfig
{
TablesToIgnoreCommaDelimited = "NormalClasses"
};
var comparer = new CompareEfSql(config);
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeTrue();
var errors = CompareLog.ListAllErrors(comparer.Logs).ToList();
errors.Count.ShouldEqual(3);
errors[0].ShouldEqual(
"NOT CHECKED: Entity 'MappedToQuery', not mapped to database. Expected = <null>, found = MappedToQuery");
errors[1].ShouldEqual(
"NOT IN DATABASE: MappedToQuery->Entity 'NormalClass', table name. Expected = NormalClasses");
errors[2].ShouldEqual(
"NOT IN DATABASE: MappedToQuery->Entity 'MappedToView', table name. Expected = NormalClasses");
}
public void CheckDatabaseSchemaAgainstEntityFrameworkExpectedSchema()
{
const string databaseName = "SFA.DAS.EmployerFinanceV2.Database";
var configuration = new ConfigurationBuilder().AddAzureTableStorage(EmployerFinanceConfigurationKeys.Base).Build();
var employerFinanceConfiguration = configuration.GetEmployerFinanceSection <EmployerFinanceConfiguration>();
using (var connection = new SqlConnection(employerFinanceConfiguration.DatabaseConnectionString))
{
var optionsBuilder = new DbContextOptionsBuilder <EmployerFinanceDbContext>().UseSqlServer(connection);
using (var context = new EmployerFinanceDbContext(optionsBuilder.Options))
{
var config = new CompareEfSqlConfig
{
TablesToIgnoreCommaDelimited = "ClientOutboxData,OutboxData,SchemaVersions,Users"
};
config.IgnoreTheseErrors(
$"EXTRA IN DATABASE: {databaseName}->Index 'AccountPayeSchemes', index constraint name. Found = AK_AccountPayeSchemes_AccountId_EmployerReferenceNumber{Environment.NewLine}" +
$"EXTRA IN DATABASE: {databaseName}->Index 'LevyDeclarationSagas', index constraint name. Found = UK_LevyDeclarationSagas_PayrollPeriod{Environment.NewLine}" +
$"EXTRA IN DATABASE: {databaseName}->Index 'LevyDeclarationSagas', index constraint name. Found = UK_LevyDeclarationSagas_PayrollPeriod_AccountPayeSchemeId");
var comparer = new CompareEfSql(config);
var hasErrors = comparer.CompareEfWithDb(context);
hasErrors.Should().BeFalse(comparer.GetAllErrors);
}
}
}
public void EmulateAFiveStepMigrationOfContinuousRunningAppOk()
{
//SETUP
var app1Options = this.CreateUniqueClassOptions <App1DbContext>();
var app2Options = this.CreateUniqueClassOptions <App2DbContext>();
//APP1 RUNNING
using (var app1Context = new App1DbContext(app1Options))
{
app1Context.Database.EnsureClean();
//APP1
app1Context.Add(new UserPart1
{
Name = "Added by App1, step1", Street = "App1 street", City = "App1 city"
});
app1Context.SaveChanges();
//APPLY 1st migration
Migration1(app1Context);
//APP2 RUNNING while APP2 is still running
using (var app2Context = new App2DbContext(app2Options))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(app2Context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
}
}
public void CompareBookThenOrderAgainstBookOnlyDatabase()
{
//SETUP
var options1 = GetBookContextOptions();
using (var context = new BookContext(options1))
{
context.Database.EnsureCreated();
}
var options2 = this.CreateUniqueMethodOptions <OrderContext>();
using (var context1 = new BookContext(options1))
using (var context2 = new OrderContext(options2))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context1, context2);
//VERIFY
hasErrors.ShouldBeTrue(comparer.GetAllErrors);
comparer.GetAllErrors.ShouldEqual(@"NOT IN DATABASE: Entity 'LineItem', table name. Expected = LineItem
NOT IN DATABASE: Entity 'Order', table name. Expected = Orders");
}
}
public void TestWriteBooksAsyncNoDataCausesNewDbOk()
{
//SETUP
var connection = this.GetUniqueDatabaseConnectionString();
var options1 = new DbContextOptionsBuilder <BookDbContext>().UseSqlServer(connection).Options;
using var context1 = new BookDbContext(options1);
var options2 = new DbContextOptionsBuilder <OrderDbContext>().UseSqlServer(connection).Options;
using var context2 = new OrderDbContext(options2);
context1.Database.EnsureDeleted();
context1.Database.Migrate();
context2.Database.Migrate();
//ATTEMPT
var comparer = new CompareEfSql();
//ATTEMPT
//Its starts with the connection string/name and then you can have as many contexts as you like
var hasErrors = comparer.CompareEfWithDb(context1, context2);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
public void TestDifferentDefaultValuesReturnErrors()
{
//SETUP
using (var context = new Issue15DbContext(_options))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
//hasErrors.ShouldBeFalse(comparer.GetAllErrors);
hasErrors.ShouldBeTrue();
comparer.GetAllErrors.ShouldEqual(@"DIFFERENT: Message->Property 'BoolRequiredDefaultFalse', default value sql. Expected = False, found = <null>
DIFFERENT: Message->Property 'BoolRequiredDefaultFalse', value generated. Expected = OnAdd, found = Never
DIFFERENT: Message->Property 'BoolRequiredDefaultTrue', default value sql. Expected = True, found = 1
DIFFERENT: Message->Property 'EnumRequiredDefaultOne', default value sql. Expected = One, found = 1
DIFFERENT: Message->Property 'EnumRequiredDefaultZero', default value sql. Expected = Zero, found = <null>
DIFFERENT: Message->Property 'EnumRequiredDefaultZero', value generated. Expected = OnAdd, found = Never
DIFFERENT: Message->Property 'IntRequiredDefault0', default value sql. Expected = 0, found = <null>
DIFFERENT: Message->Property 'IntRequiredDefault0', value generated. Expected = OnAdd, found = Never
DIFFERENT: Message->Property 'StringRequiredDefaultEmpty', default value sql. Expected = , found = N''
DIFFERENT: Message->Property 'StringRequiredDefaultSomething', default value sql. Expected = something, found = N'something'
DIFFERENT: Message->Property 'XmlRequiredDefaultEmpty', default value sql. Expected = , found = N''
DIFFERENT: Message->Property 'XmlRequiredDefaultSomething', default value sql. Expected = <something />, found = N'<something />'");
}
}
public void CompareDatabaseViaConnectionName()
{
//SETUP
const string connectionStringName = "BookOrderConnection"; //#A
//!!!!!!!!!! LEAVE OUT OF BOOK - START
var connectionString = AppSettings.GetConfiguration().GetConnectionString(connectionStringName);
var optionsBuilder = new DbContextOptionsBuilder <BookOrderContext>();
optionsBuilder.UseSqlServer(connectionString);
var options = optionsBuilder.Options;
//!!!!!!!!!! LEAVE OUT OF BOOK - END
//... I left out the option building part to save space
using (var context = new BookOrderContext(options)) //#B
{
var comparer = new CompareEfSql(); //#C
//ATTEMPT
bool hasErrors = comparer.CompareEfWithDb //#D
(connectionStringName, context); //#D
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors); //#E
}
}
public void CompareSuppressViaViaAddIgnoreCompareLog()
{
//SETUP
var options = this.CreateUniqueClassOptions <BookContext>();
using var context = new BookContext(options);
context.Database.EnsureClean();
var filepath = TestData.GetFilePath("AddViewToBookContext.sql");
context.ExecuteScriptFileInTransaction(filepath);
var config = new CompareEfSqlConfig
{
//This tells EfSchemaCompare to look at ALL tables and views
TablesToIgnoreCommaDelimited = ""
};
//This will ignore any Table or View that is not accessed by EF Core
config.AddIgnoreCompareLog(new CompareLog(CompareType.Table, CompareState.ExtraInDatabase, null));
var comparer = new CompareEfSql(config);
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
public void CompareTptContextSuppressViaIgnoreTheseErrors()
{
//SETUP
var options = this.CreateUniqueClassOptions <TptDbContext>();
using var context = new TptDbContext(options);
context.Database.EnsureClean();
var config = new CompareEfSqlConfig();
config.IgnoreTheseErrors(@"DIFFERENT: TptVer1->PrimaryKey 'PK_TptBases', constraint name. Expected = PK_TptBases, found = PK_TptVer1
DIFFERENT: TptVer1->Property 'Id', value generated. Expected = OnAdd, found = Never
DIFFERENT: TptVer1->ForeignKey 'FK_TptVer1_TptBases_Id', delete behavior. Expected = ClientCascade, found = NoAction
DIFFERENT: Entity 'TptVer1', constraint name. Expected = PK_TptBases, found = PK_TptVer1
DIFFERENT: TptVer2->PrimaryKey 'PK_TptBases', constraint name. Expected = PK_TptBases, found = PK_TptVer2
DIFFERENT: TptVer2->Property 'Id', value generated. Expected = OnAdd, found = Never
DIFFERENT: TptVer2->ForeignKey 'FK_TptVer2_TptBases_Id', delete behavior. Expected = ClientCascade, found = NoAction
DIFFERENT: Entity 'TptVer2', constraint name. Expected = PK_TptBases, found = PK_TptVer2");
var comparer = new CompareEfSql(config);
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
public void CompareReadOnlyDbContextMissingView()
{
//SETUP
var options = this.CreateUniqueClassOptions <ReadOnlyDbContext>(
builder => builder.ReplaceService <IModelCacheKeyFactory, ReadOnlyModelCacheKeyFactory>());
using var context = new ReadOnlyDbContext(options);
context.Database.EnsureClean();
context.Database.ExecuteSqlRaw(
"CREATE OR ALTER VIEW MyView AS SELECT Id, MyDateTime FROM NormalClasses");
var config = new CompareEfSqlConfig();
config.IgnoreTheseErrors(
"NOT CHECKED: Entity 'MappedToQuery', not mapped to database. Expected = <null>, found = MappedToQuery");
var comparer = new CompareEfSql(config);
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeTrue();
var errors = CompareLog.ListAllErrors(comparer.Logs).ToList();
(errors.Count == 1).ShouldBeTrue(comparer.GetAllErrors);
errors[0].ShouldEqual(
"NOT IN DATABASE: MappedToView->Property 'MyString', column name. Expected = <null>");
}
public void EfToSchemaTest()
{
using (var dbContext = new FilmDbContext("dbUpTestDb"))
{
var comparer = new CompareEfSql();
var status = comparer.CompareEfWithDb(dbContext);
Assert.True(status.IsValid, status.GetAllErrors());
Assert.False(status.HasWarnings, string.Join("\n", status.Warnings));
}
}
public void CompareLowerCaseWithItself()
{
//SETUP
using (var context = new Issue19LowerCaseDbContext(_lowerCaseOptions))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
}
public void CompareSpecializedDbContext()
{
//SETUP
using (var context = new SpecializedDbContext(_options))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
}
public void CompareViaTypeWithConnection()
{
//SETUP
using (var context = new BookContext(_options))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb <SqlServerDesignTimeServices>(_connectionString, context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
}
public void CompareViaConnection()
{
//SETUP
using (var context = new BookOrderContext(_options))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(_connectionString, context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
}
public void CompareBookAgainstBookOrderDatabaseExtraTablesIgnored()
{
//SETUP
using (var context = new BookContext(GetBookContextOptions()))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(_connectionString, context);
//VERIFY
hasErrors.ShouldBeFalse();
}
}
public void Test11CompareEfWithDbUpSqlTOk()
{
using (var db = new TestEf6SchemaCompareDb())
{
//SETUP
var comparer = new CompareEfSql();
//EXECUTE
var status = comparer.CompareEfWithDb <DataTop>(db, MiscConstants.DbUpDatabaseConfigName);
//VERIFY
status.ShouldBeValid();
status.Warnings.Count.ShouldEqual(0, string.Join("\n", status.Warnings));
}
}
public void TestDbQueryDbContextCompareEfWithDb()
{
//SETUP
using (var context = new DbQueryDbContext(_options))
{
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeTrue();
comparer.GetAllErrors.ShouldEqual("WARNING: Database 'EfSchemaCompare does not check DbQuery types'. Expected = <null>, found = MyEntityReadOnly");
}
}
public void CompareConnectionBadDatabase()
{
//SETUP
var badDatabaseConnection =
"Server=(localdb)\\mssqllocaldb;Database=BadDatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true";
using (var context = new BookContext(_options))
{
var comparer = new CompareEfSql();
//ATTEMPT
#if NETCOREAPP2_1
var ex = Assert.Throws <System.Data.SqlClient.SqlException>(() =>
comparer.CompareEfWithDb(badDatabaseConnection, context));
#elif NETCOREAPP3_0
var ex = Assert.Throws <Microsoft.Data.SqlClient.SqlException>(() =>
comparer.CompareEfWithDb(badDatabaseConnection, context));
#endif
//VERIFY
ex.Message.ShouldStartWith("Cannot open database \"BadDatabaseName\" requested by the login. The login failed");
}
}
public void Test02CompareEfWithSqlTOk()
{
using (var db = new TestEf6SchemaCompareDb())
{
//SETUP
var comparer = new CompareEfSql();
//EXECUTE
var status = comparer.CompareEfWithDb <DataTop>(db);
//VERIFY
status.ShouldBeValid();
Console.WriteLine("WARNINGS:\n {0}", string.Join("\n", status.Warnings));
}
}
public void CompareViaType()
{
//SETUP
var options = this.CreateUniqueClassOptions <BookContext>();
using var context = new BookContext(options);
context.Database.EnsureClean();
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb <SqlServerDesignTimeServices>(context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
public void TestCompareEfSqlPostgreSql()
{
//SETUP
var builder = new DbContextOptionsBuilder <BookContext>()
.UseNpgsql(null);
using var context = new BookContext(builder.Options);
var comparer = new CompareEfSql();
//ATTEMPT
var ex = Assert.Throws <InvalidOperationException>(() => comparer.CompareEfWithDb <NpgsqlDesignTimeServices>(context));
//VERIFY
ex.Message.ShouldEqual("A relational store has been configured without specifying either the DbConnection or connection string to use.");
}
public void CompareBookAgainstBookOrderDatabaseSurpressExtraInDatabaseTables()
{
//SETUP
using (var context = new BookContext(GetBookContextOptions()))
{
var config = new CompareEfSqlConfig();
config.AddIgnoreCompareLog(new CompareLog(CompareType.Table, CompareState.ExtraInDatabase, null));
var comparer = new CompareEfSql(config);
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(_connectionString, context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
}
public void CompareOwnedTypeSeparateTable()
{
//SETUP
var options = this.CreateUniqueClassOptions<OwnedTypeDbContext>(
builder => builder.ReplaceService<IModelCacheKeyFactory, OwnedTypeModelCacheKeyFactory>());
using var context = new OwnedTypeDbContext(options, OwnedTypeDbContext.Configs.SeparateTable);
context.Database.EnsureClean();
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
public void CompareTptContext()
{
//SETUP
var options = this.CreateUniqueClassOptions <TptDbContext>();
using var context = new TptDbContext(options);
context.Database.EnsureClean();
var comparer = new CompareEfSql();
//ATTEMPT
var hasErrors = comparer.CompareEfWithDb(context);
//VERIFY
hasErrors.ShouldBeFalse(comparer.GetAllErrors);
}
public void CompareBadConnection()
{
//SETUP
using (var context = new BookContext(_options))
{
var comparer = new CompareEfSql();
//ATTEMPT
var ex = Assert.Throws <System.ArgumentException>(() =>
comparer.CompareEfWithDb("bad connection string", context));
//VERIFY
ex.Message.ShouldEqual("Format of the initialization string does not conform to specification starting at index 0.");
}
}
public void Test40GetEfDataBad()
{
using (var db = new TestEf6SchemaCompareDb())
{
//SETUP
var comparer = new CompareEfSql();
//EXECUTE
var ex =
Assert.Throws <InvalidOperationException>(
() => comparer.CompareEfWithDb <EfCompare>(db, MiscConstants.DbUpDatabaseConfigName));
//VERIFY
ex.Message.ShouldStartWith("Could not find the EF data class");
}
}