public void Query_with_comparison_in_subquery_works_with_clr_semantics()
{
using (var context = new NullSemanticsContext())
{
var query = context.Entities.Where(c => context.Entities.Where(e => e.Foo != e.Bar).Count() == context.Entities.Where(e => e.Foo != e.Bar).FirstOrDefault().Id);
var expectedSql =
@"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Foo] AS [Foo],
[Extent1].[Bar] AS [Bar]
FROM [dbo].[NullSemanticsEntities] AS [Extent1]
LEFT OUTER JOIN (SELECT TOP (1) [Extent2].[Id] AS [Id]
FROM [dbo].[NullSemanticsEntities] AS [Extent2]
WHERE NOT (([Extent2].[Foo] = [Extent2].[Bar]) AND ((CASE WHEN ([Extent2].[Foo] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN ([Extent2].[Bar] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END))) ) AS [Limit1] ON 1 = 1
INNER JOIN (SELECT
COUNT(1) AS [A1]
FROM [dbo].[NullSemanticsEntities] AS [Extent3]
WHERE NOT (([Extent3].[Foo] = [Extent3].[Bar]) AND ((CASE WHEN ([Extent3].[Foo] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN ([Extent3].[Bar] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END))) ) AS [GroupBy1] ON ([GroupBy1].[A1] = [Limit1].[Id]) OR (([GroupBy1].[A1] IS NULL) AND ([Limit1].[Id] IS NULL))";
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
var expected = context.Entities.ToList().Where(c => context.Entities.ToList().Where(e => e.Foo == e.Bar).Count() != context.Entities.ToList().Where(e => e.Foo != e.Bar).FirstOrDefault().Id).ToList();
QueryTestHelpers.VerifyQueryResult(expected, query.ToList(), (o, i) => o == i);
}
}
public override NullSemanticsContext CreateContext(SqliteTestStore testStore, bool useRelationalNulls)
{
var optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder
.UseInternalServiceProvider(_serviceProvider)
.UseSqlite(
testStore.Connection,
b =>
{
if (useRelationalNulls)
{
b.UseRelationalNulls();
}
}
);
var context = new NullSemanticsContext(optionsBuilder.Options);
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
context.Database.UseTransaction(testStore.Transaction);
return(context);
}
public void Query_string_and_results_are_valid_for_column_not_equal_parameter()
{
using (var context = new NullSemanticsContext())
{
var parameter = "Bar";
var query1 = context.Entities.Where(e => e.Foo == "Foo" && e.Bar != parameter);
var query2 = context.Entities.Where(e => e.Foo == "Foo" && parameter != e.Bar);
var query3 = context.Entities.Where(e => e.Foo == "Foo" && !(e.Bar == parameter));
var expectedSql1 =
@"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Foo] AS [Foo],
[Extent1].[Bar] AS [Bar]
FROM [dbo].[NullSemanticsEntities] AS [Extent1]
WHERE (N'Foo' = [Extent1].[Foo]) AND ( NOT (([Extent1].[Bar] = @p__linq__0) AND ((CASE WHEN ([Extent1].[Bar] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN (@p__linq__0 IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END))))";
var expectedSql2 =
@"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Foo] AS [Foo],
[Extent1].[Bar] AS [Bar]
FROM [dbo].[NullSemanticsEntities] AS [Extent1]
WHERE (N'Foo' = [Extent1].[Foo]) AND ( NOT ((@p__linq__0 = [Extent1].[Bar]) AND ((CASE WHEN (@p__linq__0 IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN ([Extent1].[Bar] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END))))";
QueryTestHelpers.VerifyDbQuery(query1, expectedSql1);
QueryTestHelpers.VerifyDbQuery(query2, expectedSql2);
QueryTestHelpers.VerifyDbQuery(query3, expectedSql1);
var expected = context.Entities.ToList().Where(e => e.Foo == "Foo" && e.Bar != parameter);
Assert.Equal(expected.Count(), query1.Count());
Assert.Equal(expected.Count(), query2.Count());
}
}
public void Query_string_and_results_are_valid_for_column_not_equal_constant()
{
using (var context = new NullSemanticsContext())
{
var query1 = context.Entities.Where(e => e.Foo == "Foo" && e.Bar != "Bar");
var query2 = context.Entities.Where(e => e.Foo == "Foo" && "Bar" != e.Bar);
var query3 = context.Entities.Where(e => e.Foo == "Foo" && !("Bar" == e.Bar));
var expectedSql =
@"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Foo] AS [Foo],
[Extent1].[Bar] AS [Bar]
FROM [dbo].[NullSemanticsEntities] AS [Extent1]
WHERE (N'Foo' = [Extent1].[Foo]) AND ( NOT ((N'Bar' = [Extent1].[Bar]) AND ([Extent1].[Bar] IS NOT NULL)))";
QueryTestHelpers.VerifyDbQuery(query1, expectedSql);
QueryTestHelpers.VerifyDbQuery(query2, expectedSql);
QueryTestHelpers.VerifyDbQuery(query3, expectedSql);
var expected = context.Entities.ToList().Where(e => e.Foo == "Foo" && e.Bar != "Bar");
Assert.Equal(expected.Count(), query1.Count());
Assert.Equal(expected.Count(), query2.Count());
Assert.Equal(expected.Count(), query3.Count());
}
}
public static void Seed(NullSemanticsContext context)
{
var entities1 = NullSemanticsData.CreateEntities1();
var entities2 = NullSemanticsData.CreateEntities2();
context.Entities1.AddRange(entities1);
context.Entities2.AddRange(entities2);
context.SaveChanges();
}
public void Negation_counting_does_not_get_propagated_over_unnecessary_operators()
{
using (var context = new NullSemanticsContext())
{
var query1 = context.Entities.Where(e => (e.Foo == e.Bar ? 3 : 4) + (!(e.Foo == e.Bar) ? 4 : 3) != 6).Count();
var expected1 = context.Entities.ToList().Where(e => (e.Foo == e.Bar ? 3 : 4) + (!(e.Foo == e.Bar) ? 4 : 3) != 6).Count();
Assert.Equal(query1, expected1);
}
}
public void Query_with_Count_with_predicate_works_with_clr_semantics()
{
using (var context = new NullSemanticsContext())
{
var query = context.Entities.Count(e => e.Foo == e.Bar);
var expected = context.Entities.ToList().Count(e => e.Foo == e.Bar);
Assert.Equal(query, expected);
}
}
public override NullSemanticsContext CreateContext(SqliteTestStore testStore)
{
var optionsBuilder = new EntityOptionsBuilder();
optionsBuilder.UseSqlite(testStore.Connection);
var context = new NullSemanticsContext(_serviceProvider, optionsBuilder.Options);
context.Database.AsRelational().Connection.UseTransaction(testStore.Transaction);
return(context);
}
public override NullSemanticsContext CreateContext(SqlServerTestStore testStore)
{
var optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder.UseSqlServer(testStore.Connection);
var context = new NullSemanticsContext(_serviceProvider, optionsBuilder.Options);
context.Database.UseTransaction(testStore.Transaction);
return(context);
}
public override JetTestStore CreateTestStore()
{
return(JetTestStore.GetOrCreateShared(DatabaseName, () =>
{
using (var context = new NullSemanticsContext(new DbContextOptionsBuilder(_options)
.UseJet(_connectionString, b => b.ApplyConfiguration()).Options))
{
context.Database.EnsureClean();
NullSemanticsModelInitializer.Seed(context);
}
}));
}
protected override NullSemanticsContext CreateContext(bool useRelationalNulls = false)
{
var options = new DbContextOptionsBuilder(Fixture.CreateOptions());
if (useRelationalNulls)
{
new FbDbContextOptionsBuilder(options).UseRelationalNulls();
}
var context = new NullSemanticsContext(options.Options);
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
return(context);
}
public void Query_with_Any_with_predicate_works_with_clr_semantics()
{
using (var context = new NullSemanticsContext())
{
var query1 = context.Entities.Where(e => e.Id != 1).Any(e => e.Foo == e.Bar);
var query2 = context.Entities.Where(e => e.Id == 5).Any(e => e.Foo != e.Bar);
var expected1 = context.Entities.ToList().Where(e => e.Id != 1).Any(e => e.Foo == e.Bar);
var expected2 = context.Entities.ToList().Where(e => e.Id == 5).Any(e => e.Foo != e.Bar);
Assert.Equal(query1, expected1);
Assert.Equal(query2, expected2);
}
}
public override NullSemanticsContext CreateContext(SqliteTestStore testStore, bool useRelationalNulls)
{
var optionsBuilder = new DbContextOptionsBuilder();
var sqliteOptions = optionsBuilder.UseSqlite(testStore.Connection);
if (useRelationalNulls)
{
sqliteOptions.UseRelationalNulls();
}
var context = new NullSemanticsContext(_serviceProvider, optionsBuilder.Options);
context.Database.UseTransaction(testStore.Transaction);
return(context);
}
public override SqlServerTestStore CreateTestStore()
{
return(SqlServerTestStore.GetOrCreateShared(DatabaseName, () =>
{
using (var context = new NullSemanticsContext(new DbContextOptionsBuilder()
.UseSqlServer(_connectionString, b => b.ApplyConfiguration())
.UseInternalServiceProvider(_serviceProvider).Options))
{
context.Database.EnsureCreated();
NullSemanticsModelInitializer.Seed(context);
TestSqlLoggerFactory.Reset();
}
}));
}
public override SqliteTestStore CreateTestStore()
{
return(SqliteTestStore.GetOrCreateShared(DatabaseName, () =>
{
var optionsBuilder = new DbContextOptionsBuilder()
.UseSqlite(_connectionString)
.UseInternalServiceProvider(_serviceProvider);
using (var context = new NullSemanticsContext(optionsBuilder.Options))
{
// TODO: Delete DB if model changed
context.Database.EnsureClean();
NullSemanticsModelInitializer.Seed(context);
}
}));
}
public void Query_with_comparison_in_projection_works_with_clr_semantics()
{
using (var context = new NullSemanticsContext())
{
var query = context.Entities.Select(e => e.Foo == e.Bar);
var expectedSql =
@"SELECT
CASE WHEN (([Extent1].[Foo] = [Extent1].[Bar]) OR (([Extent1].[Foo] IS NULL) AND ([Extent1].[Bar] IS NULL))) THEN cast(1 as bit) WHEN ( NOT (([Extent1].[Foo] = [Extent1].[Bar]) AND ((CASE WHEN ([Extent1].[Foo] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN ([Extent1].[Bar] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END)))) THEN cast(0 as bit) END AS [C1]
FROM [dbo].[NullSemanticsEntities] AS [Extent1]";
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
var expected = context.Entities.ToList().Select(e => e.Foo == e.Bar).ToList();
QueryTestHelpers.VerifyQueryResult(expected, query.ToList(), (o, i) => o == i);
}
}
public void Query_with_comparison_of_function_result_with_nullable_parameters_works()
{
using (var context = new NullSemanticsContext())
{
var query = context.Entities.Where(e => e.Foo.Length == e.Bar.Length);
var expectedSql =
@"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Foo] AS [Foo],
[Extent1].[Bar] AS [Bar]
FROM [dbo].[NullSemanticsEntities] AS [Extent1]
WHERE (( CAST(LEN([Extent1].[Foo]) AS int)) = ( CAST(LEN([Extent1].[Bar]) AS int))) OR (([Extent1].[Foo] IS NULL) AND ([Extent1].[Bar] IS NULL))";
QueryTestHelpers.VerifyDbQuery(query, expectedSql);
}
}
public override NpgsqlTestStore CreateTestStore()
{
return(NpgsqlTestStore.GetOrCreateShared(DatabaseName, () =>
{
using (var context = new NullSemanticsContext(new DbContextOptionsBuilder()
.UseNpgsql(_connectionString)
.UseInternalServiceProvider(_serviceProvider).Options))
{
// TODO: Delete DB if model changed
if (context.Database.EnsureCreated())
{
NullSemanticsModelInitializer.Seed(context);
}
TestSqlLoggerFactory.SqlStatements.Clear();
}
}));
}
public override SqliteTestStore CreateTestStore()
{
return(SqliteTestStore.GetOrCreateShared(DatabaseName, () =>
{
var optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder.UseSqlite(_connectionString);
using (var context = new NullSemanticsContext(_serviceProvider, optionsBuilder.Options))
{
// TODO: Delete DB if model changed
context.Database.EnsureDeleted();
if (context.Database.EnsureCreated())
{
NullSemanticsModelInitializer.Seed(context);
}
TestSqlLoggerFactory.SqlStatements.Clear();
}
}));
}
public override NullSemanticsContext CreateContext(NpgsqlTestStore testStore, bool useRelationalNulls)
{
var context = new NullSemanticsContext(new DbContextOptionsBuilder()
.EnableSensitiveDataLogging()
.UseInternalServiceProvider(_serviceProvider)
.UseNpgsql(
testStore.Connection,
b =>
{
if (useRelationalNulls)
{
b.UseRelationalNulls();
}
}).Options);
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
context.Database.UseTransaction(testStore.Transaction);
return(context);
}
public override NullSemanticsContext CreateContext(JetTestStore testStore, bool useRelationalNulls)
{
var options = new DbContextOptionsBuilder(_options)
.UseJet(
testStore.Connection,
b =>
{
b.ApplyConfiguration();
if (useRelationalNulls)
{
b.UseRelationalNulls();
}
}).Options;
var context = new NullSemanticsContext(options);
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
context.Database.UseTransaction(testStore.Transaction);
return(context);
}
public override NullSemanticsContext CreateContext(SqlServerTestStore testStore, bool useRelationalNulls)
{
var optionsBuilder = new DbContextOptionsBuilder();
var sqlServerOptions
= optionsBuilder
.EnableSensitiveDataLogging()
.UseSqlServer(testStore.Connection);
if (useRelationalNulls)
{
sqlServerOptions.UseRelationalNulls();
}
var context = new NullSemanticsContext(_serviceProvider, optionsBuilder.Options);
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
context.Database.UseTransaction(testStore.Transaction);
return(context);
}
public void Query_string_and_results_are_valid_for_column_compared_with_other_column()
{
using (var context = new NullSemanticsContext())
{
var query1 = context.Entities.Where(e => e.Foo == e.Bar);
var query2 = context.Entities.Where(e => e.Foo != e.Bar);
var query3 = context.Entities.Where(e => !(e.Foo == e.Bar));
var expectedSql1 =
@"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Foo] AS [Foo],
[Extent1].[Bar] AS [Bar]
FROM [dbo].[NullSemanticsEntities] AS [Extent1]
WHERE ([Extent1].[Foo] = [Extent1].[Bar]) OR (([Extent1].[Foo] IS NULL) AND ([Extent1].[Bar] IS NULL))";
var expectedSql2 =
@"SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Foo] AS [Foo],
[Extent1].[Bar] AS [Bar]
FROM [dbo].[NullSemanticsEntities] AS [Extent1]
WHERE NOT (([Extent1].[Foo] = [Extent1].[Bar]) AND ((CASE WHEN ([Extent1].[Foo] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN ([Extent1].[Bar] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END)))";
QueryTestHelpers.VerifyDbQuery(query1, expectedSql1);
QueryTestHelpers.VerifyDbQuery(query2, expectedSql2);
QueryTestHelpers.VerifyDbQuery(query3, expectedSql2);
var expected1 = context.Entities.ToList().Where(e => e.Foo == e.Bar);
var expected2 = context.Entities.ToList().Where(e => e.Foo != e.Bar);
Assert.Equal(expected1.Count(), query1.Count());
Assert.Equal(expected2.Count(), query2.Count());
Assert.Equal(expected2.Count(), query3.Count());
}
}
public static void Seed(NullSemanticsContext context)
{
var nullableBoolValues = new bool?[] { false, true, null };
var nullableStringValues = new string[] { "Foo", "Bar", null };
var nullableIntValues = new int?[] { 0, 1, null };
var boolValues = new bool[] { false, true, true };
var stringValues = new string[] { "Foo", "Bar", "Bar" };
var intValues = new int[] { 0, 1, 2 };
var entities1 = new List <NullSemanticsEntity1>();
var entities2 = new List <NullSemanticsEntity2>();
int id = 0;
for (int i = 0; i < 3; i++)
{
for (int j = 0; j < 3; j++)
{
for (int k = 0; k < 3; k++)
{
id++;
var entity1 = new NullSemanticsEntity1
{
Id = id,
BoolA = boolValues[i],
BoolB = boolValues[j],
BoolC = boolValues[k],
NullableBoolA = nullableBoolValues[i],
NullableBoolB = nullableBoolValues[j],
NullableBoolC = nullableBoolValues[k],
StringA = stringValues[i],
StringB = stringValues[j],
StringC = stringValues[k],
NullableStringA = nullableStringValues[i],
NullableStringB = nullableStringValues[j],
NullableStringC = nullableStringValues[k],
IntA = intValues[i],
IntB = intValues[j],
IntC = intValues[k],
NullableIntA = nullableIntValues[i],
NullableIntB = nullableIntValues[j],
NullableIntC = nullableIntValues[k],
};
var entity2 = new NullSemanticsEntity2
{
Id = id,
BoolA = boolValues[i],
BoolB = boolValues[j],
BoolC = boolValues[k],
NullableBoolA = nullableBoolValues[i],
NullableBoolB = nullableBoolValues[j],
NullableBoolC = nullableBoolValues[k],
StringA = stringValues[i],
StringB = stringValues[j],
StringC = stringValues[k],
NullableStringA = nullableStringValues[i],
NullableStringB = nullableStringValues[j],
NullableStringC = nullableStringValues[k],
IntA = intValues[i],
IntB = intValues[j],
IntC = intValues[k],
NullableIntA = nullableIntValues[i],
NullableIntB = nullableIntValues[j],
NullableIntC = nullableIntValues[k],
};
entities1.Add(entity1);
entities2.Add(entity2);
}
}
}
context.Entities1.AddRange(entities1);
context.Entities2.AddRange(entities2);
context.SaveChanges();
}
