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);
}