public void Exception_thrown_for_function_with_no_body() { var query = "ProductModel.F_NoBody()"; var expectedExceptionMessage = Strings.Cqt_UDF_FunctionHasNoDefinition("ProductModel.F_NoBody()"); QueryTestHelpers.VerifyThrows <InvalidOperationException>(query, workspace, expectedExceptionMessage); }
public void Function_returning_collection_of_complex_types() { var query = "select ct from (ProductModel.F_Ret_ColCT()) as ct order by ct.City"; var expectedSql = @"SELECT [Limit1].[C1] AS [C1], [Limit1].[HomeAddress] AS [HomeAddress], [Limit1].[City] AS [City], [Limit1].[Region] AS [Region], [Limit1].[PostalCode] AS [PostalCode], [Limit1].[Country] AS [Country] FROM ( SELECT TOP (5) [Project1].[HomeAddress] AS [HomeAddress], [Project1].[City] AS [City], [Project1].[Region] AS [Region], [Project1].[PostalCode] AS [PostalCode], [Project1].[Country] AS [Country], [Project1].[C1] AS [C1] FROM ( SELECT [Extent1].[CustomerID] AS [CustomerID], [Extent1].[HomeAddress] AS [HomeAddress], [Extent1].[City] AS [City], [Extent1].[Region] AS [Region], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Country] AS [Country], 1 AS [C1] FROM [dbo].[Customers] AS [Extent1] ) AS [Project1] ORDER BY [Project1].[CustomerID] ASC ) AS [Limit1] ORDER BY [Limit1].[City] ASC"; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }
public void Joins_are_not_eliminated_if_parent_navigation_properties_are_not_equivalent() { using (var context = new Context()) { var query = context.MainEntities.Select( x => new { x.Value, Linked1 = x.Link1, Linked2 = x.Link2, Sub1 = x.Link1.Sublink.Value, Sub2 = x.Link2.Sublink.Value }); QueryTestHelpers.VerifyQuery( query, @"SELECT [Extent1].[Link1Id] AS [Link1Id], [Extent1].[Value] AS [Value], [Extent2].[Id] AS [Id], [Extent2].[Value] AS [Value1], [Extent2].[SublinkId] AS [SublinkId], [Extent3].[Id] AS [Id1], [Extent3].[Value] AS [Value2], [Extent3].[SublinkId] AS [SublinkId1], [Extent4].[Value] AS [Value3], [Extent5].[Value] AS [Value4] FROM [dbo].[Mains] AS [Extent1] INNER JOIN [dbo].[Links] AS [Extent2] ON [Extent1].[Link1Id] = [Extent2].[Id] INNER JOIN [dbo].[Links] AS [Extent3] ON [Extent1].[Link2Id] = [Extent3].[Id] INNER JOIN [dbo].[SubLinks] AS [Extent4] ON [Extent2].[SublinkId] = [Extent4].[Id] INNER JOIN [dbo].[SubLinks] AS [Extent5] ON [Extent3].[SublinkId] = [Extent5].[Id]"); } }
public void Overload_resolution_for_function_taking_two_integers_as_arguments_negative() { var query1 = "ProductModel.F_In_ST_1(CAST(1 as Int16), CAST(1 AS Int32))"; var expectedExceptionMessage1 = Strings.AmbiguousFunctionArguments + " Near function 'ProductModel.F_In_ST_1()', line 1, column 14."; QueryTestHelpers.VerifyThrows <EntitySqlException>(query1, workspace, expectedExceptionMessage1); var query2 = "ProductModel.F_In_ST_1(CAST(1 as Int16), CAST(1 AS Int16))"; var expectedExceptionMessage2 = Strings.AmbiguousFunctionArguments + " Near function 'ProductModel.F_In_ST_1()', line 1, column 14."; QueryTestHelpers.VerifyThrows <EntitySqlException>(query2, workspace, expectedExceptionMessage2); var query3 = "ProductModel.F_In_ST_1(CAST(1 as Int64), CAST(1 AS Int16))"; var expectedExceptionMessage3 = Strings.AmbiguousFunctionArguments + " Near function 'ProductModel.F_In_ST_1()', line 1, column 14."; QueryTestHelpers.VerifyThrows <EntitySqlException>(query3, workspace, expectedExceptionMessage3); var query4 = "ProductModel.F_In_ST_1(CAST(1 as Double), CAST(1 AS Double))"; var expectedExceptionMessage4 = Strings.NoFunctionOverloadMatch( "ProductModel", "F_In_ST_1", "F_In_ST_1(Edm.Double, Edm.Double)") + " Near function 'F_In_ST_1()', line 1, column 14."; QueryTestHelpers.VerifyThrows <EntitySqlException>(query4, workspace, expectedExceptionMessage4); }
public void Function_taking_collection_of_complex_types_as_argument_and_returns_collection_of_complex_types() { var query = "SELECT TOP(5) ct FROM ProductModel.F_In_ColCT_Ret_ColCT(select value c.Address from ProductContainer.Customers as c) as ct"; var expectedSql = @"SELECT [Limit1].[C1] AS [C1], [Limit1].[HomeAddress] AS [HomeAddress], [Limit1].[City] AS [City], [Limit1].[Region] AS [Region], [Limit1].[PostalCode] AS [PostalCode], [Limit1].[Country] AS [Country] FROM ( SELECT TOP (5) [Extent1].[HomeAddress] AS [HomeAddress], [Extent1].[City] AS [City], [Extent1].[Region] AS [Region], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Country] AS [Country], 1 AS [C1] FROM [dbo].[Customers] AS [Extent1] WHERE [Extent1].[Country] = 'Mexico' ) AS [Limit1]"; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }
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 Joins_are_not_eliminated_if_parent_navigation_properties_are_not_equivalent_in_query_with_include() { using (var context = new Context()) { var query = context.MainEntities .Include("Link1") .Include("Link1.Sublink") .Include("Link2") .Include("Link2.Sublink"); QueryTestHelpers.VerifyQuery( query, @"SELECT [Extent1].[Id] AS [Id], [Extent1].[Value] AS [Value], [Extent1].[Link1Id] AS [Link1Id], [Extent1].[Link2Id] AS [Link2Id], [Extent2].[Id] AS [Id1], [Extent2].[Value] AS [Value1], [Extent2].[SublinkId] AS [SublinkId], [Extent3].[Id] AS [Id2], [Extent3].[Value] AS [Value2], [Extent4].[Id] AS [Id3], [Extent4].[Value] AS [Value3], [Extent4].[SublinkId] AS [SublinkId1], [Extent5].[Id] AS [Id4], [Extent5].[Value] AS [Value4] FROM [dbo].[Mains] AS [Extent1] INNER JOIN [dbo].[Links] AS [Extent2] ON [Extent1].[Link1Id] = [Extent2].[Id] INNER JOIN [dbo].[SubLinks] AS [Extent3] ON [Extent2].[SublinkId] = [Extent3].[Id] INNER JOIN [dbo].[Links] AS [Extent4] ON [Extent1].[Link2Id] = [Extent4].[Id] INNER JOIN [dbo].[SubLinks] AS [Extent5] ON [Extent4].[SublinkId] = [Extent5].[Id]"); } }
public void Overload_resolution_for_function_taking_two_integers_as_arguments() { var query1 = "ProductModel.F_In_ST_1(CAST(1 as Int16), CAST(1 AS Single))"; var expectedSql1 = "SELECT 1 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query1, workspace, expectedSql1); var query2 = "ProductModel.F_In_ST_1(CAST(1 as Int32), CAST(1 AS Int32))"; var expectedSql2 = "SELECT 2 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query2, workspace, expectedSql2); var query3 = "ProductModel.F_In_ST_1(CAST(1 as Single), CAST(1 AS Int16))"; var expectedSql3 = "SELECT 3 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query3, workspace, expectedSql3); var query4 = "ProductModel.F_In_ST_2(CAST(1 as Byte), CAST(1 AS Int32))"; var expectedSql4 = "SELECT 1 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query4, workspace, expectedSql4); var query5 = "ProductModel.F_In_ST_2(CAST(1 as Int16), CAST(1 AS Int32))"; var expectedSql5 = "SELECT 2 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query5, workspace, expectedSql5); }
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 Inline_function_MaxMinProductId() { var query = @"Function MaxMinProductId(products Collection(ProductModel.Product)) as ( max(select value pp.ProductId from products as pp) - min(select value pp.ProductId from products as pp) ) select gkey, MaxMinProductId(GroupPartition(p)) FROM ProductContainer.Products as P Group By P.ProductName as gkey"; var expectedSql = @"SELECT 1 AS [C1], [GroupBy1].[K1] AS [ProductName], [GroupBy1].[A1] - [GroupBy1].[A2] AS [C2] FROM ( SELECT [Extent1].[ProductName] AS [K1], MAX([Extent1].[ProductID]) AS [A1], MIN([Extent1].[ProductID]) AS [A2] FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[Discontinued] IN (0,1) GROUP BY [Extent1].[ProductName] ) AS [GroupBy1]"; QueryTestHelpers.VerifyQuery(query, _workspace, expectedSql); }
public void Function_taking_collection_of_rows_as_argument_and_returns_collection_of_rows() { var query = "SELECT TOP(5) r FROM ProductModel.F_In_ColRT_Ret_ColRT(SELECT c FROM ProductContainer.Customers as c) as r"; var expectedSql = @"SELECT [Limit1].[C1] AS [C1], [Limit1].[C2] AS [C2], [Limit1].[City] AS [City], [Limit1].[HomeAddress] AS [HomeAddress], [Limit1].[Region] AS [Region], [Limit1].[PostalCode] AS [PostalCode], [Limit1].[Country] AS [Country] FROM ( SELECT TOP (5) [Extent1].[HomeAddress] AS [HomeAddress], [Extent1].[City] AS [City], [Extent1].[Region] AS [Region], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Country] AS [Country], 1 AS [C1], LEN([Extent1].[CustomerID]) AS [C2] FROM [dbo].[Customers] AS [Extent1] ) AS [Limit1]"; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }
public void Inline_function_count_Products() { var query = @"Function CountProducts(products Collection(ProductModel.Product)) as (count(select value 1 from products)) select gkey, CountProducts(GroupPartition(P)) FROM ProductContainer.Products as P Group By P.ProductName as gkey"; var expectedSql = @"SELECT 1 AS [C1], [GroupBy1].[K1] AS [ProductName], [GroupBy1].[A1] AS [C2] FROM ( SELECT [Extent1].[ProductName] AS [K1], COUNT(1) AS [A1] FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[Discontinued] IN (0,1) GROUP BY [Extent1].[ProductName] ) AS [GroupBy1]"; QueryTestHelpers.VerifyQuery(query, _workspace, expectedSql); }
public void Inline_aggregate_funtion_MinProductId() { var query = @"Function MinProductId(products Collection(ProductModel.Product)) as ( anyelement(select value min(pp.ProductId) from products as pp) ) select gkey, MinProductId(GroupPartition(P)) FROM ProductContainer.Products as P Group By P.ProductName as gkey"; var expectedSql = @"SELECT 1 AS [C1], [Project2].[ProductName] AS [ProductName], [Project2].[C1] AS [C2] FROM ( SELECT [Distinct1].[ProductName] AS [ProductName], (SELECT MIN([Extent2].[ProductID]) AS [A1] FROM [dbo].[Products] AS [Extent2] WHERE ([Extent2].[Discontinued] IN (0,1)) AND (([Distinct1].[ProductName] = [Extent2].[ProductName]) OR (([Distinct1].[ProductName] IS NULL) AND ([Extent2].[ProductName] IS NULL)))) AS [C1] FROM ( SELECT DISTINCT [Extent1].[ProductName] AS [ProductName] FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[Discontinued] IN (0,1) ) AS [Distinct1] ) AS [Project2]"; QueryTestHelpers.VerifyQuery(query, _workspace, expectedSql); }
public void Inline_function_one_level_above() { var query = @"Function MaxProductId(products Collection(ProductModel.Product)) as ( max(select value pp.ProductId from products as pp) ) select i.gkey, (MaxProductId(i.groupProducts)) from ( select gkey as gkey, GroupPartition(P) as groupProducts FROM ProductContainer.Products as P Group By P.ProductName as gkey ) as i"; var expectedSql = @"SELECT 1 AS [C1], [GroupBy1].[K1] AS [ProductName], [GroupBy1].[A1] AS [C2] FROM ( SELECT [Extent1].[ProductName] AS [K1], MAX([Extent1].[ProductID]) AS [A1] FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[Discontinued] IN (0,1) GROUP BY [Extent1].[ProductName] ) AS [GroupBy1]"; QueryTestHelpers.VerifyQuery(query, _workspace, expectedSql); }
public void Select_top_distinct() { var query = @" SELECT DISTINCT TOP (2) o.FirstName AS name FROM ArubaContext.Owners as o ORDER BY name DESC"; var expectedSql = @" SELECT TOP (2) [Distinct1].[C1] AS [C1], [Distinct1].[FirstName] AS [FirstName] FROM ( SELECT DISTINCT [Extent1].[FirstName] AS [FirstName], 1 AS [C1] FROM [dbo].[ArubaOwners] AS [Extent1] ) AS [Distinct1] ORDER BY [Distinct1].[FirstName] DESC"; // verifying that there are 2 results returned, that they are sorted in descending order // and they are distinct using (var db = new ArubaContext()) { using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql)) { VerifySortDescAndCountString(reader, 2, distinct: true); } } }
public void Exception_thrown_for_function_with_direct_reference_to_itself_in_definition() { var query = "ProductModel.F_D()"; var expectedExceptionMessage = Strings.Cqt_UDF_FunctionDefinitionWithCircularReference("ProductModel.F_D"); QueryTestHelpers.VerifyThrows <EntityCommandCompilationException>(query, workspace, expectedExceptionMessage); }
public void Overload_resolution_for_function_taking_entity_and_integer_as_arguments() { var query1 = "ProductModel.F_In_ProdNumber(anyelement(ProductContainer.Products), CAST(1 as Int32))"; var expectedSql1 = "SELECT 'Prod-32' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query1, workspace, expectedSql1); var query2 = "ProductModel.F_In_ProdNumber(anyelement(ProductContainer.Products), CAST(1 as Int16))"; var expectedSql2 = "SELECT 'Prod-32' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query2, workspace, expectedSql2); var query3 = "ProductModel.F_In_ProdNumber(anyelement(select value treat(p as ProductModel.DiscontinuedProduct) from ProductContainer.Products as p), CAST(1 as Int32))"; var expectedSql3 = "SELECT 'Prod-32' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query3, workspace, expectedSql3); var query4 = "ProductModel.F_In_ProdNumber(anyelement(select value treat(p as ProductModel.DiscontinuedProduct) from ProductContainer.Products as p), CAST(1 as Int16))"; var expectedSql4 = "SELECT 'DiscProd-16' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query4, workspace, expectedSql4); var query5 = "ProductModel.F_In_ProdNumber2(anyelement(select value treat(p as ProductModel.DiscontinuedProduct) from ProductContainer.Products as p), CAST(1 as Int64))"; var expectedSql5 = "SELECT 'DiscProd-Decimal' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query5, workspace, expectedSql5); }
public void Outer_applies_are_eliminated_complex_case() { using (var context = new BugContext()) { var query = from c in context.As.OrderBy(c => c.Id).Take(100) let rel1 = c.B1 let rel2 = c.B2 select new { c.Id, Rel1 = new { rel1.Id, rel1.Code, }, Rel2 = new { c.B2.Id, c.B2.Code, } }; QueryTestHelpers.VerifyQuery( query, @"SELECT TOP (100) [Extent1].[Id] AS [Id], [Extent2].[Id] AS [Id1], [Extent2].[Code] AS [Code] FROM [dbo].[A] AS [Extent1] LEFT OUTER JOIN [dbo].[B] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id] ORDER BY [Extent1].[Id] ASC"); } }
public void Function_taking_collection_of_scalars_as_argument_and_returns_collection_of_scalars() { var query = "SELECT top(5) s FROM ProductModel.F_In_ColST_Ret_ColST(SELECT VALUE c.CustomerID from ProductContainer.Customers as c) as s"; var expectedSql = @"SELECT TOP (5) 1 AS [C1], [c].[CustomerID] AS [C2] FROM (SELECT [Extent1].[CustomerID] AS [CustomerID] FROM [dbo].[Customers] AS [Extent1] INTERSECT SELECT [UnionAll2].[C1] AS [C1] FROM (SELECT [UnionAll1].[C1] AS [C1] FROM (SELECT 'a' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] UNION ALL SELECT 'b' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1] UNION ALL SELECT 'c' AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]) AS [c]" ; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }
public void Duplicate_joins_with_non_equi_join_predicates_are_eliminated() { using (var context = new Context()) { var query = context.QueryTypes .OrderBy(x => x.Id) .Select( x => new { x.RelatedType.Id, x.RelatedType.RecordDate, x.RelatedType.Name, x.RelatedType.Type, x.RelatedType.Code }) .Take(10); QueryTestHelpers.VerifyQuery( query, @"SELECT TOP (10) [Extent1].[Id] AS [Id], [Extent1].[RelatedTypeId] AS [RelatedTypeId], [Extent2].[RecordDate] AS [RecordDate], [Extent2].[Name] AS [Name], [Extent2].[Type] AS [Type], [Extent2].[Code] AS [Code] FROM [dbo].[QueryTypes] AS [Extent1] LEFT OUTER JOIN [dbo].[BaseTypes] AS [Extent2] ON ([Extent1].[RelatedTypeId] = [Extent2].[Id]) AND ([Extent2].[Discriminator] IN (N'DerivedTypeA',N'DerivedTypeB',N'DerivedTypeC',N'DerivedTypeD',N'DerivedTypeE')) ORDER BY [Extent1].[Id] ASC"); } }
public void Row_field_names_are_ignored_during_function_resolution() { var query = @"select r.A as W, r.B as V from ProductModel.F_RowFieldNamessIgnored( { row(1 as z, '1' as y), row(4 as r, '4' as q) }) as r order by W"; var expectedSql = @"SELECT [Project3].[C1] AS [C1], [Project3].[C2] AS [C2], [Project3].[C3] AS [C3] FROM ( SELECT [UnionAll1].[C1] AS [C1], (CASE WHEN ([UnionAll1].[C1] = 0) THEN 1 ELSE 4 END) + 1 AS [C2], CASE WHEN ([UnionAll1].[C1] = 0) THEN '1' ELSE '4' END + '1' AS [C3] FROM (SELECT 0 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] WHERE ((CASE WHEN (0 = 0) THEN 1 ELSE 4 END) = 1) AND ((CASE WHEN (0 = 0) THEN '1' ELSE '4' END) = '1') UNION ALL SELECT 1 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable2] WHERE ((CASE WHEN (1 = 0) THEN 1 ELSE 4 END) = 1) AND ((CASE WHEN (1 = 0) THEN '1' ELSE '4' END) = '1')) AS [UnionAll1] ) AS [Project3] ORDER BY [Project3].[C2] ASC"; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }
public void Function_taking_collection_of_scalars_as_argument_and_returns_collection_of_entities() { var query = "SELECT top(5) et FROM ProductModel.F_In_ColST_Ret_ColET(SELECT VALUE c.CustomerID from ProductContainer.Customers as c) as et"; var expectedSql = @"SELECT TOP (5) [Project1].[C1] AS [C1], [Project1].[CustomerID] AS [CustomerID], [Project1].[HomeAddress] AS [HomeAddress], [Project1].[City] AS [City], [Project1].[Region] AS [Region], [Project1].[PostalCode] AS [PostalCode], [Project1].[Country] AS [Country] FROM ( SELECT [Extent1].[CustomerID] AS [CustomerID], [Extent1].[HomeAddress] AS [HomeAddress], [Extent1].[City] AS [City], [Extent1].[Region] AS [Region], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Country] AS [Country], 1 AS [C1] FROM [dbo].[Customers] AS [Extent1] ) AS [Project1] WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[Customers] AS [Extent2] WHERE [Project1].[CustomerID] = [Extent2].[CustomerID] )"; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }
public void Exception_thrown_if_invalid_parameter_passed_to_function() { var query = "ProductModel.F_I(10)"; var expectedExceptionMessage = Strings.Cqt_UDF_FunctionDefinitionResultTypeMismatch( "Edm.Int32", "ProductModel.F_I", "Edm.Int16"); QueryTestHelpers.VerifyThrows <InvalidOperationException>(query, workspace, expectedExceptionMessage); }
public void Nested_projections_list() { var query = @" SELECT VALUE ( SELECT VALUE TOP (2) d.Id FROM ArubaContext.Owners AS d WHERE d.Id > c.Id ORDER BY d.Id ) AS top FROM ArubaContext.Owners AS c ORDER BY c.Id skip 5 limit 2"; var expectedSql = @" SELECT [Project2].[Id] AS [Id], [Project2].[C1] AS [C1], [Project2].[Id1] AS [Id1] FROM ( SELECT [Limit1].[Id] AS [Id], [Limit2].[Id] AS [Id1], CASE WHEN ([Limit2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM (SELECT TOP (2) [Extent1].[Id] AS [Id] FROM ( SELECT [Extent1].[Id] AS [Id], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number] FROM [dbo].[ArubaOwners] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 5 ORDER BY [Extent1].[Id] ASC ) AS [Limit1] OUTER APPLY (SELECT TOP (2) [Project1].[Id] AS [Id] FROM ( SELECT [Extent2].[Id] AS [Id] FROM [dbo].[ArubaOwners] AS [Extent2] WHERE [Extent2].[Id] > [Limit1].[Id] ) AS [Project1] ORDER BY [Project1].[Id] ASC ) AS [Limit2] ) AS [Project2] ORDER BY [Project2].[Id] ASC, [Project2].[C1] ASC"; // verifying that there are 4 results returned and they are integers using (var db = new ArubaContext()) { using (var reader = QueryTestHelpers.EntityCommandSetup(db, query, expectedSql)) { var count = 0; while (reader.Read()) { var nestedReader = (DbDataReader)reader.GetValue(0); while (nestedReader.Read()) { Assert.Equal("Int32", ((CollectionType)reader.DataRecordInfo.RecordType.EdmType).TypeUsage.EdmType.Name); count++; } } Assert.Equal(4, count); } } }
public void Simple_scan_with_Enum() { var entitySet = workspace.GetEntityContainer("MessageContainer", DataSpace.CSpace).GetEntitySetByName("MessageSet", false); var query = entitySet.Scan(); var expectedSql = "SELECT [Extent1].[Id] AS [Id], [Extent1].[MessageType] AS [MessageType]FROM [dbo].[Message] AS [Extent1]"; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }
public void Exception_thrown_for_function_that_references_inline_function_in_its_body() { var query = @"using ProductModel; function F_H() as (1) F_G()"; var expectedExceptionMessage = Strings.CannotResolveNameToTypeOrFunction("F_H") + " Near simple identifier, line 3, column 7."; QueryTestHelpers.VerifyThrows <EntitySqlException>(query, workspace, expectedExceptionMessage); }
public void Exception_thrown_for_function_with_indirect_reference_to_itself_in_definition() { var query1 = "ProductModel.F_E()"; QueryTestHelpers.VerifyThrows <EntityCommandCompilationException>(query1, workspace, "Cqt_UDF_FunctionDefinitionWithCircularReference", "ProductModel.F_E"); var query2 = "ProductModel.F_F()"; QueryTestHelpers.VerifyThrows <EntityCommandCompilationException>(query2, workspace, "Cqt_UDF_FunctionDefinitionWithCircularReference", "ProductModel.F_F"); }
public void Project_function_referencing_other_functions() { var query = "ProductModel.F_A() - ProductModel.F_B() - ProductModel.F_C()"; var expectedSql = @"SELECT ((1 - (1 + 2)) - 1) - (1 + 2) AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }
public void Function_that_takes_itself_as_argument() { var query = "ProductModel.F_J(ProductModel.F_J(ProductModel.F_J(1)))"; var expectedSql = @"SELECT 1 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1]"; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }
public void Constant_integer_based_Enum_in_where_clause() { var entitySet = workspace.GetEntityContainer("MessageContainer", DataSpace.CSpace).GetEntitySetByName("MessageSet", false); var query = entitySet.Scan() .Where(c => c.Property("MessageType").Equal(c.Property("MessageType").ResultType.Constant(-5))); var expectedSql = "SELECT [Extent1].[Id] AS [Id], [Extent1].[MessageType] AS [MessageType] FROM [dbo].[Message] AS [Extent1] WHERE [Extent1].[MessageType] = -5"; QueryTestHelpers.VerifyQuery(query, workspace, expectedSql); }