private static async Task Create_creates_physical_database_but_not_tables_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: false))
{
var creator = GetDatabaseCreator(testDatabase);
Assert.False(creator.Exists());
if (async)
{
await creator.CreateAsync();
}
else
{
creator.Create();
}
Assert.True(creator.Exists());
if (testDatabase.Connection.State != ConnectionState.Open)
{
await testDatabase.Connection.OpenAsync();
}
Assert.Equal(0, (await testDatabase.QueryAsync <string>("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'")).Count());
Assert.True(await testDatabase.ExecuteScalarAsync <bool>(
string.Concat(
"SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name='",
testDatabase.Connection.Database,
"'"),
CancellationToken.None));
}
}
private static async Task EnsureCreated_can_create_schema_in_existing_database_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync())
{
await RunDatabaseCreationTest(testDatabase, async);
}
}
private static async Task EnsuredDeleted_noop_when_database_doesnt_exist_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: false))
{
using (var context = new BloggingContext(testDatabase))
{
var creator = context.GetService <IRelationalDatabaseCreator>();
Assert.False(async ? await creator.ExistsAsync() : creator.Exists());
if (async)
{
Assert.False(await creator.EnsureDeletedAsync());
}
else
{
Assert.False(creator.EnsureDeleted());
}
Assert.Equal(ConnectionState.Closed, context.Database.GetDbConnection().State);
Assert.False(async ? await creator.ExistsAsync() : creator.Exists());
Assert.Equal(ConnectionState.Closed, context.Database.GetDbConnection().State);
}
}
}
private static async Task EnsureCreated_can_create_physical_database_and_schema_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: false))
{
await RunDatabaseCreationTest(testDatabase, async);
}
}
private static async Task EnsureDeleted_will_delete_database_test(bool async, bool openConnection)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: true))
{
if (!openConnection)
{
testDatabase.Connection.Close();
}
using (var context = new BloggingContext(testDatabase))
{
var creator = context.GetService <IRelationalDatabaseCreator>();
Assert.True(async ? await creator.ExistsAsync() : creator.Exists());
if (async)
{
Assert.True(await context.Database.EnsureDeletedAsync());
}
else
{
Assert.True(context.Database.EnsureDeleted());
}
Assert.Equal(ConnectionState.Closed, context.Database.GetDbConnection().State);
Assert.False(async ? await creator.ExistsAsync() : creator.Exists());
Assert.Equal(ConnectionState.Closed, context.Database.GetDbConnection().State);
}
}
}
public SqlServerOneToOneQueryFixture()
{
_serviceProvider
= new ServiceCollection()
.AddEntityFramework()
.AddSqlServer()
.ServiceCollection
.AddInstance <ILoggerFactory>(new TestSqlLoggerFactory())
.BuildServiceProvider();
var model = CreateModel();
var database = SqlServerTestStore.CreateScratchAsync().Result;
_options
= new DbContextOptions()
.UseModel(model)
.UseSqlServer(database.Connection.ConnectionString);
using (var context = new DbContext(_serviceProvider, _options))
{
context.Database.EnsureCreated();
AddTestData(context);
}
}
public async Task Can_save_changes_in_tracked_entities()
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync())
{
var optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder.UseSqlServer(testDatabase.Connection.ConnectionString);
int updatedId;
int deletedId;
int addedId;
using (var db = new BloggingContext(_fixture.ServiceProvider, optionsBuilder.Options))
{
var blogs = await CreateBlogDatabaseAsync <Blog>(db);
var toAdd = db.Blogs.Add(new Blog
{
Name = "Blog to Insert",
George = true,
TheGu = new Guid("0456AEF1-B7FC-47AA-8102-975D6BA3A9BF"),
NotFigTime = new DateTime(1973, 9, 3, 0, 10, 33, 777),
ToEat = 64,
OrNothing = 0.123456789,
Fuse = 777,
WayRound = 9876543210,
Away = 0.12345f,
AndChew = new byte[16]
}).Entity;
db.Entry(toAdd).State = EntityState.Detached;
var toUpdate = blogs[0];
toUpdate.Name = "Blog is Updated";
updatedId = toUpdate.Id;
var toDelete = blogs[1];
toDelete.Name = "Blog to delete";
deletedId = toDelete.Id;
db.Remove(toDelete);
db.Entry(toAdd).State = EntityState.Added;
await db.SaveChangesAsync();
addedId = toAdd.Id;
Assert.NotEqual(0, addedId);
Assert.Equal(EntityState.Unchanged, db.Entry(toUpdate).State);
Assert.Equal(EntityState.Unchanged, db.Entry(toAdd).State);
Assert.DoesNotContain(toDelete, db.ChangeTracker.Entries().Select(e => e.Entity));
}
using (var db = new BloggingContext(_fixture.ServiceProvider, optionsBuilder.Options))
{
var toUpdate = db.Blogs.Single(b => b.Id == updatedId);
Assert.Equal("Blog is Updated", toUpdate.Name);
Assert.Equal(0, db.Blogs.Count(b => b.Id == deletedId));
Assert.Equal("Blog to Insert", db.Blogs.Single(b => b.Id == addedId).Name);
}
}
}
private static async Task HasTables_returns_false_when_database_exists_but_has_no_tables_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: true))
{
var creator = GetDatabaseCreator(testDatabase);
Assert.False(async ? await((TestDatabaseCreator)creator).HasTablesAsyncBase() : ((TestDatabaseCreator)creator).HasTablesBase());
}
}
public BatchingTest()
{
_testStore = SqlServerTestStore.CreateScratchAsync().Result;
_serviceProvider = new ServiceCollection()
.AddEntityFramework()
.AddSqlServer()
.ServiceCollection
.BuildServiceProvider();
}
private static async Task Exists_returns_true_when_database_exists_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: true))
{
var creator = GetDatabaseCreator(testDatabase);
Assert.True(async ? await creator.ExistsAsync() : creator.Exists());
}
}
private static async Task HasTables_returns_true_when_database_exists_and_has_any_tables_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: true))
{
await testDatabase.ExecuteNonQueryAsync("CREATE TABLE SomeTable (Id uniqueidentifier)");
var creator = GetDatabaseCreator(testDatabase);
Assert.True(async ? await((TestDatabaseCreator)creator).HasTablesAsyncBase() : ((TestDatabaseCreator)creator).HasTablesBase());
}
}
public async Task Can_execute_scalar_command()
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync())
{
var commandExecutor = new SqlServerSimpleCommandExecutor(testDatabase.Connection.ConnectionString);
var scalar = await commandExecutor.ExecuteScalarAsync <int>("select 42", CancellationToken.None);
Assert.Equal(42, scalar);
}
}
public async Task Empty_Migration_Creates_Database()
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: false))
{
using (var context = CreateContext(testDatabase))
{
context.Database.AsMigrationsEnabled().ApplyMigrations();
Assert.True(context.Database.AsRelational().Exists());
}
}
}
public async Task Empty_Migration_Creates_Database()
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: false))
{
using (var context = CreateContext(testDatabase))
{
context.Database.Migrate();
Assert.True(context.GetService <IRelationalDatabaseCreator>().Exists());
}
}
}
private static async Task Exists_returns_true_when_database_exists_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: true))
{
using (var context = new BloggingContext(testDatabase))
{
Assert.True(async ? await context.Database.AsRelational().ExistsAsync() : context.Database.AsRelational().Exists());
Assert.Equal(ConnectionState.Closed, context.Database.AsRelational().Connection.DbConnection.State);
}
}
}
private static async Task Create_throws_if_database_already_exists_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: true))
{
var creator = GetDatabaseCreator(testDatabase);
Assert.Equal(
1801, // Database with given name already exists
async
? (await Assert.ThrowsAsync <SqlException>(() => creator.CreateAsync())).Number
: Assert.Throws <SqlException>(() => creator.Create()).Number);
}
}
[Fact] // Issue #931
public async Task Can_save_and_query_with_schema()
{
var serviceProvider
= new ServiceCollection()
.AddEntityFramework()
.AddSqlServer()
.AddDbContext <SchemaContext>()
.ServiceCollection()
.BuildServiceProvider();
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync())
{
await testDatabase.ExecuteNonQueryAsync("CREATE SCHEMA Apple");
await testDatabase.ExecuteNonQueryAsync("CREATE TABLE Apple.Jack (MyKey int)");
await testDatabase.ExecuteNonQueryAsync("CREATE TABLE Apple.Black (MyKey int)");
using (var context = serviceProvider
.GetRequiredService <IServiceScopeFactory>()
.CreateScope()
.ServiceProvider
.GetRequiredService <SchemaContext>())
{
context.Connection = testDatabase.Connection;
context.Add(new Jack {
MyKey = 1
});
context.Add(new Black {
MyKey = 2
});
context.SaveChanges();
}
using (var context = serviceProvider
.GetRequiredService <IServiceScopeFactory>()
.CreateScope()
.ServiceProvider
.GetRequiredService <SchemaContext>())
{
context.Connection = testDatabase.Connection;
Assert.Equal(1, context.Jacks.Count());
Assert.Equal(1, context.Blacks.Count());
}
}
}
private static async Task Delete_throws_when_database_doesnt_exist_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: false))
{
var creator = GetDatabaseCreator(testDatabase);
if (async)
{
await Assert.ThrowsAsync <SqlException>(() => creator.DeleteAsync());
}
else
{
Assert.Throws <SqlException>(() => creator.Delete());
}
}
}
private static async Task CreateTables_creates_schema_in_existing_database_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync())
{
var serviceCollection = new ServiceCollection();
serviceCollection
.AddEntityFramework()
.AddSqlServer();
var serviceProvider = serviceCollection.BuildServiceProvider();
var options = new DbContextOptions();
options.UseSqlServer(testDatabase.Connection.ConnectionString);
using (var context = new BloggingContext(serviceProvider, options))
{
var contextServices = ((IDbContextServices)context).ScopedServiceProvider;
var creator = (RelationalDataStoreCreator)contextServices.GetRequiredService <DbContextService <DataStoreCreator> >().Service;
if (async)
{
await creator.CreateTablesAsync(context.Model);
}
else
{
creator.CreateTables(context.Model);
}
if (testDatabase.Connection.State != ConnectionState.Open)
{
await testDatabase.Connection.OpenAsync();
}
var tables = await testDatabase.QueryAsync <string>("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
Assert.Equal(1, tables.Count());
Assert.Equal("Blog", tables.Single());
var columns = await testDatabase.QueryAsync <string>("SELECT TABLE_NAME + '.' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS");
Assert.Equal(2, columns.Count());
Assert.True(columns.Any(c => c == "Blog.Id"));
Assert.True(columns.Any(c => c == "Blog.Name"));
}
}
}
private static async Task HasTables_throws_when_database_doesnt_exist_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: false))
{
var creator = GetDatabaseCreator(testDatabase);
var errorNumber = async
? (await Assert.ThrowsAsync <SqlException>(() => ((TestDatabaseCreator)creator).HasTablesAsyncBase())).Number
: Assert.Throws <SqlException>(() => ((TestDatabaseCreator)creator).HasTablesBase()).Number;
if (errorNumber != 233) // skip if no-process transient failure
{
Assert.Equal(
4060, // Login failed error number
errorNumber);
}
}
}
private static async Task EnsuredCreated_is_noop_when_database_exists_and_has_schema_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: false))
{
using (var context = new BloggingContext(testDatabase))
{
context.Database.EnsureCreated();
if (async)
{
Assert.False(await context.Database.EnsureCreatedAsync());
}
else
{
Assert.False(context.Database.EnsureCreated());
}
Assert.Equal(ConnectionState.Closed, context.Database.GetDbConnection().State);
}
}
}
private static async Task Delete_will_delete_database_test(bool async)
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync(createDatabase: true))
{
testDatabase.Connection.Close();
var creator = GetDatabaseCreator(testDatabase);
Assert.True(async ? await creator.ExistsAsync() : creator.Exists());
if (async)
{
await creator.DeleteAsync();
}
else
{
creator.Delete();
}
Assert.False(async ? await creator.ExistsAsync() : creator.Exists());
}
}
public async Task Can_save_changes()
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync())
{
var optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder
.EnableSensitiveDataLogging()
.UseSqlServer(testDatabase.Connection.ConnectionString);
using (var db = new BloggingContext(_fixture.ServiceProvider, optionsBuilder.Options))
{
await CreateBlogDatabaseAsync <Blog>(db);
}
var loggingFactory = new TestSqlLoggerFactory();
var serviceProvider = new ServiceCollection()
.AddEntityFramework()
.AddSqlServer()
.ServiceCollection()
.AddInstance <ILoggerFactory>(loggingFactory)
.BuildServiceProvider();
using (var db = new BloggingContext(serviceProvider, optionsBuilder.Options))
{
var toUpdate = db.Blogs.Single(b => b.Name == "Blog1");
toUpdate.Name = "Blog is Updated";
var updatedId = toUpdate.Id;
var toDelete = db.Blogs.Single(b => b.Name == "Blog2");
toDelete.Name = "Blog to delete";
var deletedId = toDelete.Id;
db.Entry(toUpdate).State = EntityState.Modified;
db.Entry(toDelete).State = EntityState.Deleted;
var toAdd = db.Add(new Blog
{
Name = "Blog to Insert",
George = true,
TheGu = new Guid("0456AEF1-B7FC-47AA-8102-975D6BA3A9BF"),
NotFigTime = new DateTime(1973, 9, 3, 0, 10, 33, 777),
ToEat = 64,
OrNothing = 0.123456789,
Fuse = 777,
WayRound = 9876543210,
Away = 0.12345f,
AndChew = new byte[16]
}).Entity;
await db.SaveChangesAsync();
var addedId = toAdd.Id;
Assert.NotEqual(0, addedId);
Assert.Equal(EntityState.Unchanged, db.Entry(toUpdate).State);
Assert.Equal(EntityState.Unchanged, db.Entry(toAdd).State);
Assert.DoesNotContain(toDelete, db.ChangeTracker.Entries().Select(e => e.Entity));
Assert.Equal(3, TestSqlLoggerFactory.SqlStatements.Count);
Assert.Contains("SELECT", TestSqlLoggerFactory.SqlStatements[0]);
Assert.Contains("SELECT", TestSqlLoggerFactory.SqlStatements[1]);
Assert.Contains("@p0: " + deletedId, TestSqlLoggerFactory.SqlStatements[2]);
Assert.Contains("DELETE", TestSqlLoggerFactory.SqlStatements[2]);
Assert.Contains("UPDATE", TestSqlLoggerFactory.SqlStatements[2]);
Assert.Contains("INSERT", TestSqlLoggerFactory.SqlStatements[2]);
var rows = await testDatabase.ExecuteScalarAsync <int>(
$@"SELECT Count(*) FROM [dbo].[Blog] WHERE Id = {updatedId} AND Name = 'Blog is Updated'",
CancellationToken.None);
Assert.Equal(1, rows);
rows = await testDatabase.ExecuteScalarAsync <int>(
$@"SELECT Count(*) FROM [dbo].[Blog] WHERE Id = {deletedId}",
CancellationToken.None);
Assert.Equal(0, rows);
rows = await testDatabase.ExecuteScalarAsync <int>(
$@"SELECT Count(*) FROM [dbo].[Blog] WHERE Id = {addedId} AND Name = 'Blog to Insert'",
CancellationToken.None);
Assert.Equal(1, rows);
}
}
}
private async Task RoundTripChanges <TBlog>() where TBlog : class, IBlog, new()
{
using (var testDatabase = await SqlServerTestStore.CreateScratchAsync())
{
var optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder.UseSqlServer(testDatabase.Connection.ConnectionString);
int blog1Id;
int blog2Id;
int blog3Id;
using (var context = new BloggingContext <TBlog>(_fixture.ServiceProvider, optionsBuilder.Options))
{
var blogs = await CreateBlogDatabaseAsync <TBlog>(context);
blog1Id = blogs[0].Id;
blog2Id = blogs[1].Id;
Assert.NotEqual(0, blog1Id);
Assert.NotEqual(0, blog2Id);
Assert.NotEqual(blog1Id, blog2Id);
}
using (var context = new BloggingContext <TBlog>(_fixture.ServiceProvider, optionsBuilder.Options))
{
var blogs = context.Blogs.ToList();
Assert.Equal(2, blogs.Count);
var blog1 = blogs.Single(b => b.Name == "Blog1");
Assert.Equal(blog1Id, blog1.Id);
Assert.Equal("Blog1", blog1.Name);
Assert.True(blog1.George);
Assert.Equal(new Guid("0456AEF1-B7FC-47AA-8102-975D6BA3A9BF"), blog1.TheGu);
Assert.Equal(new DateTime(1973, 9, 3, 0, 10, 33, 777), blog1.NotFigTime);
Assert.Equal(64, blog1.ToEat);
Assert.Equal(0.123456789, blog1.OrNothing);
Assert.Equal(777, blog1.Fuse);
Assert.Equal(9876543210, blog1.WayRound);
Assert.Equal(0.12345f, blog1.Away);
Assert.Equal(new byte[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }, blog1.AndChew);
blog1.Name = "New Name";
var blog2 = blogs.Single(b => b.Name == "Blog2");
Assert.Equal(blog2Id, blog2.Id);
blog2.Name = null;
blog2.NotFigTime = new DateTime();
blog2.AndChew = null;
var blog3 = context.Add(new TBlog()).Entity;
await context.SaveChangesAsync();
blog3Id = blog3.Id;
Assert.NotEqual(0, blog3Id);
}
using (var context = new BloggingContext <TBlog>(_fixture.ServiceProvider, optionsBuilder.Options))
{
var blogs = context.Blogs.ToList();
Assert.Equal(3, blogs.Count);
Assert.Equal("New Name", blogs.Single(b => b.Id == blog1Id).Name);
var blog2 = blogs.Single(b => b.Id == blog2Id);
Assert.Null(blog2.Name);
Assert.Equal(blog2.NotFigTime, new DateTime());
Assert.Null(blog2.AndChew);
var blog3 = blogs.Single(b => b.Id == blog3Id);
Assert.Null(blog3.Name);
Assert.Equal(blog3.NotFigTime, new DateTime());
Assert.Null(blog3.AndChew);
}
}
}