private SqlServerTestStore GetTriggersTestStore()
{
var testStore = SqlServerTestStore.Create("SqlServerTriggers");
using (var context = CreateTriggersContext(testStore))
{
context.Database.EnsureCreated();
}
testStore.ExecuteNonQuery(@"
CREATE TRIGGER TRG_InsertProduct
ON Products
AFTER INSERT AS
BEGIN
IF @@ROWCOUNT = 0
return
SET nocount on;
INSERT INTO ProductBackups
SELECT * FROM INSERTED;
END");
testStore.ExecuteNonQuery(@"
CREATE TRIGGER TRG_UpdateProduct
ON Products
AFTER UPDATE AS
BEGIN
IF @@ROWCOUNT = 0
return
SET nocount on;
UPDATE b
SET b.Name = p.Name, b.Version = p.Version
FROM ProductBackups b
INNER JOIN Products p
ON b.Id = p.Id
WHERE p.Id IN(SELECT INSERTED.Id FROM INSERTED);
END");
testStore.ExecuteNonQuery(@"
CREATE TRIGGER TRG_DeleteProduct
ON Products
AFTER DELETE AS
BEGIN
IF @@ROWCOUNT = 0
return
SET nocount on;
DELETE FROM ProductBackups
WHERE Id IN(SELECT DELETED.Id FROM DELETED);
END");
return(testStore);
}
public void Inserts_are_batched_correctly(bool clientPk, bool clientFk, bool clientOrder)
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
var options = new DbContextOptionsBuilder()
.UseSqlServer(testStore.Connection, b => b.ApplyConfiguration())
.UseInternalServiceProvider(
new ServiceCollection()
.AddEntityFrameworkSqlServer()
.BuildServiceProvider())
.Options;
var expectedBlogs = new List <Blog>();
using (var context = new BloggingContext(options))
{
context.Database.EnsureClean();
var owner1 = new Owner();
var owner2 = new Owner();
context.Owners.Add(owner1);
context.Owners.Add(owner2);
for (var i = 1; i < 500; i++)
{
var blog = new Blog();
if (clientPk)
{
blog.Id = Guid.NewGuid();
}
if (clientFk)
{
blog.Owner = i % 2 == 0 ? owner1 : owner2;
}
if (clientOrder)
{
blog.Order = i;
}
context.Blogs.Add(blog);
expectedBlogs.Add(blog);
}
context.SaveChanges();
}
AssertDatabaseState(clientOrder, expectedBlogs, options);
}
}
public void Insert_with_non_key_default_value()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
using (var context = new BlogContextNonKeyDefaultValue(testStore.Name))
{
context.Database.EnsureCreated();
var blogs = new List <Blog>
{
new Blog {
Name = "One Unicorn"
},
new Blog {
Name = "Two Unicorns", CreatedOn = new DateTime(1969, 8, 3, 0, 10, 0)
}
};
context.AddRange(blogs);
context.SaveChanges();
Assert.NotEqual(new DateTime(), blogs[0].CreatedOn);
Assert.NotEqual(new DateTime(), blogs[1].CreatedOn);
}
using (var context = new BlogContextNonKeyDefaultValue(testStore.Name))
{
var blogs = context.Blogs.OrderBy(e => e.Name).ToList();
Assert.NotEqual(new DateTime(), blogs[0].CreatedOn);
Assert.Equal(new DateTime(1969, 8, 3, 0, 10, 0), blogs[1].CreatedOn);
blogs[0].CreatedOn = new DateTime(1973, 9, 3, 0, 10, 0);
blogs[1].Name = "Zwo Unicorns";
context.SaveChanges();
}
using (var context = new BlogContextNonKeyDefaultValue(testStore.Name))
{
var blogs = context.Blogs.OrderBy(e => e.Name).ToList();
Assert.Equal(new DateTime(1969, 8, 3, 0, 10, 0), blogs[1].CreatedOn);
Assert.Equal(new DateTime(1973, 9, 3, 0, 10, 0), blogs[0].CreatedOn);
}
}
}
public void Insert_with_non_key_default_value_readonly()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
using (var context = new BlogContextNonKeyReadOnlyDefaultValue(testStore.Name))
{
context.Database.EnsureCreated();
context.AddRange(
new Blog {
Name = "One Unicorn"
},
new Blog {
Name = "Two Unicorns"
});
context.SaveChanges();
Assert.NotEqual(new DateTime(), context.Blogs.ToList()[0].CreatedOn);
}
DateTime dateTime0;
using (var context = new BlogContextNonKeyReadOnlyDefaultValue(testStore.Name))
{
var blogs = context.Blogs.OrderBy(e => e.Id).ToList();
dateTime0 = blogs[0].CreatedOn;
Assert.NotEqual(new DateTime(), dateTime0);
Assert.NotEqual(new DateTime(), blogs[1].CreatedOn);
blogs[0].Name = "One Pegasus";
blogs[1].CreatedOn = new DateTime(1973, 9, 3, 0, 10, 0);
context.SaveChanges();
}
using (var context = new BlogContextNonKeyReadOnlyDefaultValue(testStore.Name))
{
var blogs = context.Blogs.OrderBy(e => e.Id).ToList();
Assert.Equal(dateTime0, blogs[0].CreatedOn);
Assert.Equal(new DateTime(1973, 9, 3, 0, 10, 0), blogs[1].CreatedOn);
}
}
}
public void Insert_with_ValueGeneratedOnAdd_GUID_nonkey_property_throws()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
using (var context = new BlogContextClientGuidNonKey(testStore.Name))
{
context.Database.EnsureCreated();
var blog = context.Add(new GuidBlog {
Name = "One Unicorn"
}).Entity;
Assert.Equal(default(Guid), blog.NotId);
// No value set on a required column
Assert.Throws <DbUpdateException>(() => context.SaveChanges());
}
}
}
public void Resolve_concurreny()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
using (var context = new BlogContextConcurrencyWithRowversion(testStore.Name))
{
context.Database.EnsureCreated();
var blog = context.Add(new ConcurrentBlog {
Name = "One Unicorn"
}).Entity;
context.SaveChanges();
using (var innerContext = new BlogContextConcurrencyWithRowversion(testStore.Name))
{
var updatedBlog = innerContext.ConcurrentBlogs.Single();
updatedBlog.Name = "One Pegasus";
innerContext.SaveChanges();
var currentTimestamp = updatedBlog.Timestamp.ToArray();
try
{
blog.Name = "One Earth Pony";
context.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
// Update origianal values (and optionally any current values)
// Would normally do this with just one method call
context.Entry(blog).Property(e => e.Id).OriginalValue = updatedBlog.Id;
context.Entry(blog).Property(e => e.Name).OriginalValue = updatedBlog.Name;
context.Entry(blog).Property(e => e.Timestamp).OriginalValue = updatedBlog.Timestamp;
context.SaveChanges();
Assert.NotEqual(blog.Timestamp, currentTimestamp);
}
}
}
}
}
public void Insert_explicit_value_into_computed_column()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
using (var context = new BlogContextComputedColumn(testStore.Name))
{
context.Database.EnsureCreated();
context.Add(new FullNameBlog {
FirstName = "One", LastName = "Unicorn", FullName = "Gerald"
});
// The property 'FullName' on entity type 'FullNameBlog' is defined to be read-only before it is
// saved, but its value has been set to something other than a temporary or default value.
Assert.Equal(
CoreStrings.PropertyReadOnlyBeforeSave("FullName", "FullNameBlog"),
Assert.Throws <InvalidOperationException>(() => context.SaveChanges()).Message);
}
}
}
public OneToOneQuerySqlServerFixture()
{
_testStore = SqlServerTestStore.Create("OneToOneQueryTest");
_options = new DbContextOptionsBuilder()
.UseSqlServer(_testStore.ConnectionString, b => b.ApplyConfiguration())
.UseInternalServiceProvider(new ServiceCollection()
.AddEntityFrameworkSqlServer()
.AddSingleton(TestModelSource.GetFactory(OnModelCreating))
.AddSingleton <ILoggerFactory>(new TestSqlLoggerFactory())
.BuildServiceProvider())
.Options;
using (var context = new DbContext(_options))
{
context.Database.EnsureCreated();
AddTestData(context);
}
}
public BuiltInDataTypesSqlServerFixture()
{
_testStore = SqlServerTestStore.Create("BuiltInDataTypes");
var serviceProvider = new ServiceCollection()
.AddEntityFrameworkSqlServer()
.AddSingleton(TestSqlServerModelSource.GetFactory(OnModelCreating))
.AddSingleton <ILoggerFactory>(_testSqlLoggerFactory)
.BuildServiceProvider();
_options = new DbContextOptionsBuilder()
.UseSqlServer(_testStore.Connection, b => b.ApplyConfiguration())
.EnableSensitiveDataLogging()
.UseInternalServiceProvider(serviceProvider)
.Options;
using (var context = new DbContext(_options))
{
context.Database.EnsureCreated();
}
}
public void Insert_with_explicit_default_keys()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
using (var context = new BlogContext(testStore.Name))
{
context.Database.EnsureCreated();
context.AddRange(new Blog {
Id = 0, Name = "One Unicorn"
}, new Blog {
Id = 1, Name = "Two Unicorns"
});
// DbUpdateException : An error occurred while updating the entries. See the
// inner exception for details.
// SqlException : Cannot insert explicit value for identity column in table
// 'Blog' when IDENTITY_INSERT is set to OFF.
Assert.Throws <DbUpdateException>(() => context.SaveChanges());
}
}
}
public void It_creates_unique_query_cache_key()
{
using (var testStore = SqlServerTestStore.Create(nameof(CompiledQueryCacheKeyGeneratorTest)))
{
object key1, key2;
Expression query;
using (var context1 = new QueryKeyCacheContext(rowNumberPaging: true, connection: testStore.Connection))
{
var generator = context1.GetService <ICompiledQueryCacheKeyGenerator>();
query = context1.Set <Poco1>().Skip(4).Take(10).Expression;
key1 = generator.GenerateCacheKey(query, false);
}
using (var context2 = new QueryKeyCacheContext(rowNumberPaging: false, connection: testStore.Connection))
{
var generator = context2.GetService <ICompiledQueryCacheKeyGenerator>();
key2 = generator.GenerateCacheKey(query, false);
}
Assert.NotEqual(key1, key2);
}
}
public void Insert_explicit_value_throws_when_readonly_sequence_before_save()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
using (var context = new BlogContextReadOnlySequenceKeyColumnWithDefaultValue(testStore.Name))
{
context.Database.EnsureCreated();
context.AddRange(new Blog {
Id = 1, Name = "One Unicorn"
}, new Blog {
Name = "Two Unicorns"
});
// The property 'Id' on entity type 'Blog' is defined to be read-only before it is
// saved, but its value has been set to something other than a temporary or default value.
Assert.Equal(
CoreStrings.PropertyReadOnlyBeforeSave("Id", "Blog"),
Assert.Throws <InvalidOperationException>(() => context.SaveChanges()).Message);
}
}
}
public void Insert_and_update_with_computed_column_with_function()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
using (var context = new BlogContextComputedColumnWithFunction(testStore.Name))
{
context.Database.ExecuteSqlCommand
(@"CREATE FUNCTION
[dbo].[GetFullName](@First NVARCHAR(MAX), @Second NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) WITH SCHEMABINDING AS BEGIN RETURN @First + @Second END");
context.GetService <IRelationalDatabaseCreator>().CreateTables();
}
using (var context = new BlogContextComputedColumnWithFunction(testStore.Name))
{
var blog = context.Add(new FullNameBlog {
FirstName = "One", LastName = "Unicorn"
}).Entity;
context.SaveChanges();
Assert.Equal("OneUnicorn", blog.FullName);
}
using (var context = new BlogContextComputedColumnWithFunction(testStore.Name))
{
var blog = context.FullNameBlogs.Single();
Assert.Equal("OneUnicorn", blog.FullName);
blog.LastName = "Pegasus";
context.SaveChanges();
Assert.Equal("OnePegasus", blog.FullName);
}
}
}
private SqlServerTestStore GetQueryTriggersTestStore()
{
var testStore = SqlServerTestStore.Create("SqlServerTriggers");
using (var context = CreateQueryTriggersContext(testStore))
{
context.Database.EnsureCreated();
}
testStore.ExecuteNonQuery(@"
CREATE TRIGGER TRG_InsertUpdateProduct
ON UpdatedProducts
AFTER INSERT, UPDATE AS
BEGIN
IF @@ROWCOUNT = 0
return
SET nocount on;
UPDATE UpdatedProducts set StoreUpdated = StoreUpdated + 1
WHERE Id IN(SELECT INSERTED.Id FROM INSERTED);
END");
return(testStore);
}
public void It_creates_unique_query_cache_key()
{
using (var testStore = SqlServerTestStore.Create(nameof(CompiledQueryCacheKeyGeneratorTest)))
{
object key1, key2;
Expression query;
using (var context1 = new QueryKeyCacheContext(rowNumberPaging: true, connection: testStore.Connection))
{
var services = ((IInfrastructure <IServiceProvider>)context1).Instance.GetService <IDbContextServices>().DatabaseProviderServices;
query = context1.Set <Poco1>().Skip(4).Take(10).Expression;
var generator = services.CompiledQueryCacheKeyGenerator;
key1 = generator.GenerateCacheKey(query, false);
}
using (var context2 = new QueryKeyCacheContext(rowNumberPaging: false, connection: testStore.Connection))
{
var services = ((IInfrastructure <IServiceProvider>)context2).Instance.GetService <IDbContextServices>().DatabaseProviderServices;
var generator = services.CompiledQueryCacheKeyGenerator;
key2 = generator.GenerateCacheKey(query, false);
}
Assert.NotEqual(key1, key2);
}
}
public void Insert_with_server_generated_GUID_key()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
Guid afterSave;
using (var context = new BlogContextServerGuidKey(testStore.Name))
{
context.Database.EnsureCreated();
var blog = context.Add(new GuidBlog {
Name = "One Unicorn"
}).Entity;
var beforeSave = blog.Id;
var beforeSaveNotId = blog.NotId;
Assert.NotEqual(default(Guid), beforeSave);
Assert.Equal(default(Guid), beforeSaveNotId);
context.SaveChanges();
afterSave = blog.Id;
var afterSaveNotId = blog.NotId;
Assert.NotEqual(default(Guid), afterSave);
Assert.NotEqual(default(Guid), afterSaveNotId);
Assert.NotEqual(beforeSave, afterSave);
Assert.NotEqual(beforeSaveNotId, afterSaveNotId);
}
using (var context = new BlogContextServerGuidKey(testStore.Name))
{
Assert.Equal(afterSave, context.GuidBlogs.Single().Id);
}
}
}
public DefaultValuesTest()
{
TestStore = SqlServerTestStore.Create("DefaultValuesTest");
}
public SequentialGuidEndToEndTest()
{
TestStore = SqlServerTestStore.Create("SequentialGuidEndToEndTest");
}
public CompositeKeyEndToEndTest()
{
TestStore = SqlServerTestStore.Create("CompositeKeyEndToEndTest");
}
public SequenceEndToEndTest()
{
TestStore = SqlServerTestStore.Create("SequenceEndToEndTest");
}
public void Inserts_and_updates_are_batched_correctly()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
var options = new DbContextOptionsBuilder()
.UseSqlServer(testStore.Connection, b => b.ApplyConfiguration())
.UseInternalServiceProvider(
new ServiceCollection()
.AddEntityFrameworkSqlServer()
.BuildServiceProvider())
.Options;
var expectedBlogs = new List <Blog>();
using (var context = new BloggingContext(options))
{
context.Database.EnsureClean();
var owner1 = new Owner {
Name = "0"
};
var owner2 = new Owner {
Name = "1"
};
context.Owners.Add(owner1);
context.Owners.Add(owner2);
var blog1 = new Blog
{
Id = Guid.NewGuid(),
Owner = owner1,
Order = 1
};
context.Blogs.Add(blog1);
expectedBlogs.Add(blog1);
context.SaveChanges();
owner2.Name = "2";
blog1.Order = 0;
var blog2 = new Blog
{
Id = Guid.NewGuid(),
Owner = owner1,
Order = 1
};
context.Blogs.Add(blog2);
expectedBlogs.Add(blog2);
var blog3 = new Blog
{
Id = Guid.NewGuid(),
Owner = owner2,
Order = 2
};
context.Blogs.Add(blog3);
expectedBlogs.Add(blog3);
context.SaveChanges();
}
AssertDatabaseState(true, expectedBlogs, options);
}
}
public ComputedColumnTest()
{
TestStore = SqlServerTestStore.Create("ComputedColumnTest");
}
public void Insert_and_update_with_computed_column_with_querying_function()
{
using (var testStore = SqlServerTestStore.Create(DatabaseName))
{
using (var context = new BlogContextComputedColumn(testStore.Name))
{
context.GetService <IRelationalDatabaseCreator>().CreateTables();
context.Database.ExecuteSqlCommand("ALTER TABLE dbo.FullNameBlogs DROP COLUMN FullName;");
context.Database.ExecuteSqlCommand(@"CREATE FUNCTION [dbo].[GetFullName](@Id int)
RETURNS NVARCHAR(MAX) WITH SCHEMABINDING AS
BEGIN
DECLARE @FullName NVARCHAR(MAX);
SELECT @FullName = [FirstName] + [LastName] FROM [dbo].[FullNameBlogs] WHERE [Id] = @Id;
RETURN @FullName
END");
context.Database.ExecuteSqlCommand("ALTER TABLE dbo.FullNameBlogs ADD FullName AS [dbo].[GetFullName]([Id]); ");
}
try
{
using (var context = new BlogContextComputedColumn(testStore.Name))
{
var blog = context.Add(new FullNameBlog {
FirstName = "One", LastName = "Unicorn"
}).Entity;
context.SaveChanges();
Assert.Equal("OneUnicorn", blog.FullName);
}
using (var context = new BlogContextComputedColumn(testStore.Name))
{
var blog = context.FullNameBlogs.Single();
Assert.Equal("OneUnicorn", blog.FullName);
blog.LastName = "Pegasus";
context.SaveChanges();
Assert.Equal("OnePegasus", blog.FullName);
}
using (var context = new BlogContextComputedColumn(testStore.Name))
{
var blog1 = context.Add(new FullNameBlog {
FirstName = "Hank", LastName = "Unicorn"
}).Entity;
var blog2 = context.Add(new FullNameBlog {
FirstName = "Jeff", LastName = "Unicorn"
}).Entity;
context.SaveChanges();
Assert.Equal("HankUnicorn", blog1.FullName);
Assert.Equal("JeffUnicorn", blog2.FullName);
}
}
finally
{
using (var context = new BlogContextComputedColumn(testStore.Name))
{
context.Database.ExecuteSqlCommand("ALTER TABLE dbo.FullNameBlogs DROP COLUMN FullName;");
context.Database.ExecuteSqlCommand("DROP FUNCTION [dbo].[GetFullName];");
}
}
}
}