Exemplo n.º 1
0
        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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
            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]");
                }
            }
Exemplo n.º 4
0
        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);
        }
Exemplo n.º 5
0
        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());
            }
        }
Exemplo n.º 7
0
            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]");
                }
            }
Exemplo n.º 8
0
        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());
            }
        }
Exemplo n.º 10
0
        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);
        }
Exemplo n.º 11
0
        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);
        }
Exemplo n.º 12
0
        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);
        }
Exemplo n.º 13
0
        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);
        }
Exemplo n.º 14
0
        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);
        }
Exemplo n.º 15
0
            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);
                    }
                }
            }
Exemplo n.º 16
0
        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);
        }
Exemplo n.º 17
0
        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);
        }
Exemplo n.º 18
0
        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");
            }
        }
Exemplo n.º 19
0
        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);
        }
Exemplo n.º 20
0
            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");
                }
            }
Exemplo n.º 21
0
        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);
        }
Exemplo n.º 22
0
        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);
        }
Exemplo n.º 23
0
        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);
        }
Exemplo n.º 24
0
            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);
                    }
                }
            }
Exemplo n.º 25
0
        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);
        }
Exemplo n.º 26
0
        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);
        }
Exemplo n.º 27
0
        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");
        }
Exemplo n.º 28
0
        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);
        }
Exemplo n.º 29
0
        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);
        }
Exemplo n.º 30
0
        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);
        }