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 Equality_comparison_is_expanded_correctly_for_case_statement() { using (var context = new ABContext()) { var name = "ab"; var query = from a in context.As select a.Name == name; var expectedSql = @"SELECT CASE WHEN (([Extent1].[Name] = @p__linq__0) OR (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL))) THEN cast(1 as bit) WHEN ( NOT (([Extent1].[Name] = @p__linq__0) AND (( CASE WHEN ([Extent1].[Name] 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)))) THEN cast(0 as bit) END AS [C1] FROM [dbo].[A] AS [Extent1]"; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void Query_string_and_results_are_valid_for_column_equals_parameter() { using (var context = new SimpleModelContext()) { SetupContext(context); var parameter = "Bananas"; var query1 = context.Products.Where(p => p.Category.Id == "Fruit" && p.Name == parameter); var query2 = context.Products.Where(p => p.Category.Id == "Fruit" && parameter == p.Name); var expectedSql1 = @"SELECT [Extent1].[Discriminator] AS [Discriminator], [Extent1].[Id] AS [Id], [Extent1].[CategoryId] AS [CategoryId], [Extent1].[Name] AS [Name], [Extent1].[PromotionalCode] AS [PromotionalCode] FROM [dbo].[Products] AS [Extent1] WHERE ([Extent1].[Discriminator] IN (N'FeaturedProduct',N'Product')) AND (N'Fruit' = [Extent1].[CategoryId]) AND (([Extent1].[Name] = @p__linq__0) OR (([Extent1].[Name] IS NULL) AND (@p__linq__0 IS NULL)))"; var expectedSql2 = @"SELECT [Extent1].[Discriminator] AS [Discriminator], [Extent1].[Id] AS [Id], [Extent1].[CategoryId] AS [CategoryId], [Extent1].[Name] AS [Name], [Extent1].[PromotionalCode] AS [PromotionalCode] FROM [dbo].[Products] AS [Extent1] WHERE ([Extent1].[Discriminator] IN (N'FeaturedProduct',N'Product')) AND (N'Fruit' = [Extent1].[CategoryId]) AND ((@p__linq__0 = [Extent1].[Name]) OR ((@p__linq__0 IS NULL) AND ([Extent1].[Name] IS NULL)))"; QueryTestHelpers.VerifyDbQuery(query1, expectedSql1); QueryTestHelpers.VerifyDbQuery(query2, expectedSql2); Assert.Equal(1, query1.Count()); Assert.Equal(1, query2.Count()); } }
public void Query_string_and_results_are_valid_for_column_not_equal_constant() { using (var context = new SimpleModelContext()) { SetupContext(context); var query1 = context.Products.Where(p => p.Category.Id == "Fruit" && p.Name != "Grapes"); var query2 = context.Products.Where(p => p.Category.Id == "Fruit" && "Grapes" != p.Name); var query3 = context.Products.Where(p => p.Category.Id == "Fruit" && !("Grapes" == p.Name)); var expectedSql = @"SELECT [Extent1].[Discriminator] AS [Discriminator], [Extent1].[Id] AS [Id], [Extent1].[CategoryId] AS [CategoryId], [Extent1].[Name] AS [Name], [Extent1].[PromotionalCode] AS [PromotionalCode] FROM [dbo].[Products] AS [Extent1] WHERE ([Extent1].[Discriminator] IN (N'FeaturedProduct',N'Product')) AND (N'Fruit' = [Extent1].[CategoryId]) AND ( NOT ((N'Grapes' = [Extent1].[Name]) AND ([Extent1].[Name] IS NOT NULL)))"; QueryTestHelpers.VerifyDbQuery(query1, expectedSql); QueryTestHelpers.VerifyDbQuery(query2, expectedSql); QueryTestHelpers.VerifyDbQuery(query3, expectedSql); Assert.Equal(2, query1.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 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 void Null_checks_for_non_nullable_columns_are_eliminated_from_case_statement() { using (var context = new ABContext()) { var name = "ab"; var query = from b in context.Bs select b.Name == name; var expectedSql = @"SELECT CASE WHEN ([Extent1].[Name] = @p__linq__0) THEN cast(1 as bit) WHEN ( NOT (([Extent1].[Name] = @p__linq__0) AND (0 = (CASE WHEN (@p__linq__0 IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END)))) THEN cast(0 as bit) END AS [C1] FROM [dbo].[B] AS [Extent1]"; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void Null_checks_for_nullable_parameters_are_not_eliminated() { using (var context = new ABContext()) { int?aId = 1; var query = context.As.Where(a => a.Id != aId); var expectedSql = @"SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[A] AS [Extent1] WHERE NOT (([Extent1].[Id] = @p__linq__0) AND (@p__linq__0 IS NOT NULL))"; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void Null_checks_for_nullable_parameters_are_eliminated_when_other_operand_is_not_nullable() { using (var context = new ABContext()) { int?aId = 1; var query = context.As.Where(a => a.Id == aId); var expectedSql = @"SELECT [Extent1].[Id] AS [Id], [Extent1].[NullableId] AS [NullableId], [Extent1].[Name] AS [Name] FROM [dbo].[A] AS [Extent1] WHERE [Extent1].[Id] = @p__linq__0"; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
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 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 Rule_FilterOverProject_does_promote_to_single_Select_if_custom_function_and_doesnt_opt_in() { var expectedSql = @"SELECT [Extent1].[Id] AS [Id], [SqlServer].[MyCustomFunc]([Extent1].[Name]) AS [C1] FROM [dbo].[Blogs] AS [Extent1] WHERE ([SqlServer].[MyCustomFunc]([Extent1].[Name])) > 10"; using (var context = new BlogContext()) { context.Configuration.UseDatabaseNullSemantics = true; context.Configuration.DisableFilterOverProjectionSimplificationForCustomFunctions = false; // false is default, but using explicit valueto make it obvious var query = context.Blogs.Select(b => new { b.Id, Len = CustomFunctions.MyCustomFunc(b.Name) }).Where(b => b.Len > 10); QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void Rule_FilterOverProject_promotes_to_single_Select_if_builtint_function() { var expectedSql = @"SELECT [Extent1].[Id] AS [Id], CAST(LEN([Extent1].[Name]) AS int) AS [C1] FROM [dbo].[Blogs] AS [Extent1] WHERE (CAST(LEN([Extent1].[Name]) AS int)) > 10"; using (var context = new BlogContext()) { context.Configuration.UseDatabaseNullSemantics = true; var query = context.Blogs.Select(b => new { b.Id, Len = b.Name.Length }).Where(b => b.Len > 10); QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void Duplicate_joins_are_not_created() { using (var context = new ABContext()) { var query = from a in context.As where a.Name == context.Bs.FirstOrDefault().Name select a; var expectedSql = @"SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[A] AS [Extent1] LEFT OUTER JOIN (SELECT TOP (1) [c].[Name] AS [Name] FROM [dbo].[B] AS [c] ) AS [Limit1] ON 1 = 1 WHERE ([Extent1].[Name] = [Limit1].[Name]) OR (([Extent1].[Name] IS NULL) AND ([Limit1].[Name] IS NULL))"; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void Rule_FilterOverLeftOuterJoin_promotes_to_InnerJoin_if_using_database_null_semantics() { var expectedSql = @"SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[Blogs] AS [Extent1] INNER JOIN (SELECT TOP (1) [c].[Name] AS [Name] FROM [dbo].[BlogEntries] AS [c] ) AS [Limit1] ON [Extent1].[Name] = [Limit1].[Name]"; using (var context = new BlogContext()) { context.Configuration.UseDatabaseNullSemantics = true; var query = from b in context.Blogs where b.Name == context.BlogEntries.FirstOrDefault().Name select b; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void EQ_is_not_expanded_when_rewriting_navigation_properties() { using (var context = new Context()) { var query = context.Orders.Where(order => order.Invoice.Order.Group < 10); QueryTestHelpers.VerifyDbQuery( query, @"SELECT [Extent1].[Id] AS [Id], [Extent1].[Group] AS [Group] FROM [dbo].[Orders] AS [Extent1] INNER JOIN (SELECT [Extent2].[Group] AS [Group], [Extent4].[Id] AS [Id1] FROM [dbo].[Orders] AS [Extent2] LEFT OUTER JOIN [dbo].[Invoices] AS [Extent3] ON [Extent2].[Id] = [Extent3].[Id] INNER JOIN [dbo].[Invoices] AS [Extent4] ON [Extent4].[Id] = [Extent3].[Id] ) AS [Join2] ON [Extent1].[Id] = [Join2].[Id1] WHERE [Join2].[Group] < 10"); Assert.Equal(0, query.Count()); } }
public void Rule_FilterOverLeftOuterJoin_does_not_promote_to_InnerJoin_if_filter_predicate_is_expanded_in_null_semantics_phase() { var expectedSql = @"SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[Blogs] AS [Extent1] LEFT OUTER JOIN (SELECT TOP (1) [c].[Name] AS [Name] FROM [dbo].[BlogEntries] AS [c] ) AS [Limit1] ON 1 = 1 WHERE ([Extent1].[Name] = [Limit1].[Name]) OR (([Extent1].[Name] IS NULL) AND ([Limit1].[Name] IS NULL))"; using (var context = new BlogContext()) { context.Configuration.UseDatabaseNullSemantics = false; var query = from b in context.Blogs where b.Name == context.BlogEntries.FirstOrDefault().Name select b; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void Rule_FilterOverProject_does_not_promote_to_single_Select_if_custom_function_and_does_opt_in() { var expectedSql = @"SELECT [Project1].[Id] AS [Id], [Project1].[C1] AS [C1] FROM ( SELECT [Extent1].[Id] AS [Id], [SqlServer].[MyCustomFunc]([Extent1].[Name]) AS [C1] FROM [dbo].[Blogs] AS [Extent1] ) AS [Project1] WHERE ([Project1].[Id] > 10) AND ([Project1].[C1] > 10)"; using (var context = new BlogContext()) { context.Configuration.UseDatabaseNullSemantics = true; context.Configuration.DisableFilterOverProjectionSimplificationForCustomFunctions = true; var query = context.Blogs.Select(b => new { b.Id, Len = CustomFunctions.MyCustomFunc(b.Name) }).Where(b => b.Id > 10 && b.Len > 10); QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void Rule_FilterOverOuterApply_promotes_to_CrossApply_if_using_database_null_semantics() { var expectedSql = @"SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[Blogs] AS [Extent1] CROSS APPLY (SELECT TOP (1) [Extent2].[Name] AS [Name] FROM [dbo].[BlogEntries] AS [Extent2] WHERE [Extent2].[Name] = [Extent1].[Name] ) AS [Limit1] WHERE [Extent1].[Name] = [Limit1].[Name]"; using (var context = new BlogContext()) { context.Configuration.UseDatabaseNullSemantics = true; var query = from b in context.Blogs from e in context.BlogEntries.Where(e => e.Name == b.Name).Take(1).DefaultIfEmpty() where b.Name == e.Name select b; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
public void Rule_FilterOverOuterApply_promotes_to_CrossApply_if_filter_predicate_is_not_expanded_in_null_semantics_phase() { var expectedSql = @"SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name] FROM [dbo].[Blogs] AS [Extent1] CROSS APPLY (SELECT TOP (1) [Extent2].[Id] AS [Id] FROM [dbo].[BlogEntries] AS [Extent2] WHERE ([Extent2].[Name] = [Extent1].[Name]) OR (([Extent2].[Name] IS NULL) AND ([Extent1].[Name] IS NULL)) ) AS [Limit1] WHERE [Extent1].[Id] = [Limit1].[Id]"; using (var context = new BlogContext()) { context.Configuration.UseDatabaseNullSemantics = false; var query = from b in context.Blogs from e in context.BlogEntries.Where(e => e.Name == b.Name).Take(1).DefaultIfEmpty() where b.Id == e.Id select b; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }
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 void Inner_equality_comparisons_are_expanded_correctly() { using (var context = new ABContext()) { var name1 = "ab1"; var name2 = "ab2"; var name3 = "ab3"; var query = from a in context.As where !(a.Name == name1 || a.Name != name2 || !(a.Name == name3)) select a; var expectedSql = @"SELECT [Extent1].[Id] AS [Id], [Extent1].[NullableId] AS [NullableId], [Extent1].[Name] AS [Name] FROM [dbo].[A] AS [Extent1] WHERE NOT ( (([Extent1].[Name] = @p__linq__0) AND ((CASE WHEN ([Extent1].[Name] 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))) OR ( NOT (([Extent1].[Name] = @p__linq__1) OR (([Extent1].[Name] IS NULL) AND (@p__linq__1 IS NULL)))) OR ( NOT (([Extent1].[Name] = @p__linq__2) OR (([Extent1].[Name] IS NULL) AND (@p__linq__2 IS NULL)))))"; QueryTestHelpers.VerifyDbQuery(query, expectedSql); } }