public void Null_Boolean()
{
CheckQuery(
Kitchens.Select <Kitchen, bool?> (k => null),
"SELECT CONVERT(BIT, NULL) AS [value] FROM [KitchenTable] AS [t0]",
row => (object)row.GetValue <bool?> (new ColumnID("value", 0)));
}
public void NullableBool_InSelectClause()
{
CheckQuery(
from k in Kitchens select k.PassedLastInspection,
"SELECT [t0].[PassedLastInspection] AS [value] FROM [KitchenTable] AS [t0]",
row => row.GetValue <bool?> (new ColumnID("value", 0)));
bool?nullableValue = true;
CheckQuery(
from k in Kitchens select nullableValue,
"SELECT CONVERT(BIT, @1) AS [value] FROM [KitchenTable] AS [t0]",
row => row.GetValue <bool?> (new ColumnID("value", 0)),
new CommandParameter("@1", 1));
var kitchenParameter = Expression.Parameter(typeof(Kitchen), "k");
var nullablePredicate =
Expression.Lambda <Func <Kitchen, bool?> > (
Expression.Equal(Expression.Property(kitchenParameter, "LastInspectionScore"), Expression.Constant(0, typeof(int?)), true, null),
kitchenParameter);
CheckQuery(
Kitchens.Select(nullablePredicate),
"SELECT CONVERT(BIT, CASE WHEN ([t0].[LastInspectionScore] = @1) THEN 1 "
+ "WHEN NOT ([t0].[LastInspectionScore] = @1) THEN 0 ELSE NULL END) AS [value] "
+ "FROM [KitchenTable] AS [t0]",
row => row.GetValue <bool?> (new ColumnID("value", 0)),
new CommandParameter("@1", 0));
}
public void Null()
{
CheckQuery(
Kitchens.Select <Kitchen, object> (k => null),
"SELECT NULL AS [value] FROM [KitchenTable] AS [t0]",
row => row.GetValue <object> (new ColumnID("value", 0)));
}
public void NullableBool_Coalesce_ToTrue_InSelectClause()
{
CheckQuery(
from k in Kitchens select k.PassedLastInspection ?? true,
"SELECT CONVERT(BIT, (COALESCE ([t0].[PassedLastInspection], @1))) AS [value] FROM [KitchenTable] AS [t0]",
row => row.GetValue <bool>(new ColumnID("value", 0)),
new CommandParameter("@1", 1));
// Note: Can't coalesce a constant value, this would be replaced by the partial evaluator.
var kitchenParameter = Expression.Parameter(typeof(Kitchen), "k");
var coalescedNullablePredicate =
Expression.Lambda <Func <Kitchen, bool> > (
Expression.Coalesce(
Expression.Equal(Expression.Property(kitchenParameter, "LastInspectionScore"), Expression.Constant(0, typeof(int?)), true, null),
Expression.Constant(true)),
kitchenParameter);
CheckQuery(
Kitchens.Select(coalescedNullablePredicate),
"SELECT CONVERT(BIT, (COALESCE (CASE WHEN ([t0].[LastInspectionScore] = @1) THEN 1 "
+ "WHEN NOT ([t0].[LastInspectionScore] = @1) THEN 0 ELSE NULL END, @2)))"
+ " AS [value] FROM [KitchenTable] AS [t0]",
row => row.GetValue <bool> (new ColumnID("value", 0)),
new CommandParameter("@1", 0),
new CommandParameter("@2", 1));
}
public void SetOperationAfterSetOperation_CausesNoSubQuery()
{
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Union(Restaurants.Select(r => r.ID)),
"SELECT [t0].[ID] AS [value] FROM [CookTable] AS [t0] "
+ "UNION (SELECT [t1].[ID] AS [value] FROM [KitchenTable] AS [t1]) "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [RestaurantTable] AS [t2])");
}
public void SetOperation_WithSubsequentInMemoryProjection()
{
CheckQuery(
() => Cooks.Select(c => c.Name).Union(Kitchens.Select(c => c.Name)).Select(n => InMemoryToUpper(n)),
"SELECT [q0].[value] AS [Arg0] FROM ("
+ "SELECT [t1].[Name] AS [value] FROM [CookTable] AS [t1] UNION (SELECT [t2].[Name] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]",
row => (object)InMemoryToUpper(row.GetValue <string> (new ColumnID("Arg0", 0))));
}
public void SetOperation_WithInMemoryProjectionWithConstantValue()
{
CheckQuery(
() => Cooks.Select(c => new { X = 1, Y = c.ID }).Union(Kitchens.Select(k => new { X = 2, Y = k.ID })),
"SELECT @1 AS [X],[t0].[ID] AS [Y] FROM [CookTable] AS [t0] "
+ "UNION (SELECT @2 AS [X],[t1].[ID] AS [Y] FROM [KitchenTable] AS [t1])",
new CommandParameter("@1", 1),
new CommandParameter("@2", 2));
}
public void NullableBool_Not_Lifted()
{
var kitchenParameter = Expression.Parameter(typeof(Kitchen), "k");
var conjunction = Expression.Not(Expression.Property(kitchenParameter, "PassedLastInspection"));
Assert.That(conjunction.IsLiftedToNull, Is.True);
var selector = Expression.Lambda <Func <Kitchen, bool?> > (conjunction, kitchenParameter);
CheckQuery(
Kitchens.Select(selector),
"SELECT CONVERT(BIT, CASE WHEN NOT ([t0].[PassedLastInspection] = 1) "
+ "THEN 1 WHEN NOT NOT ([t0].[PassedLastInspection] = 1) "
+ "THEN 0 ELSE NULL END) AS [value] FROM [KitchenTable] AS [t0]");
var predicate = Expression.Lambda <Func <Kitchen, bool> > (Expression.Convert(conjunction, typeof(bool)), kitchenParameter);
CheckQuery(
Kitchens.Where(predicate).Select(k => k.ID),
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] WHERE NOT ([t0].[PassedLastInspection] = 1)");
}
public void SetOperation_WithDifferentInMemoryProjections()
{
CheckQuery(
() => Cooks.Select(c => InMemoryToUpper(c.Name)).Union(Kitchens.Select(c => c.Name)),
"SELECT [t0].[Name] AS [Arg0] FROM [CookTable] AS [t0] UNION (SELECT [t1].[Name] AS [value] FROM [KitchenTable] AS [t1])");
}
public void ResultOperatorAfterSetOperation_CausesSubQuery()
{
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).All(i => i > 10),
"SELECT CONVERT(BIT, CASE WHEN NOT EXISTS(("
+ "SELECT [q0].[value] FROM ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0] "
+ "WHERE NOT ([q0].[value] > @1))) THEN 1 ELSE 0 END) AS [value]",
new CommandParameter("@1", 10));
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Any(i => i > 10),
"SELECT CONVERT(BIT, CASE WHEN EXISTS(("
+ "SELECT [q0].[value] FROM ("
+ "SELECT [t1].[ID] AS [value] "
+ "FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0] "
+ "WHERE ([q0].[value] > @1)"
+ ")) THEN 1 ELSE 0 END) AS [value]",
new CommandParameter("@1", 10));
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Average(),
"SELECT AVG(CONVERT(FLOAT, [q0].[value])) AS [value] FROM ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]");
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Contains(10),
"SELECT CONVERT(BIT, CASE WHEN @1 IN ("
+ "SELECT [t0].[ID] FROM [CookTable] AS [t0] "
+ "UNION (SELECT [t1].[ID] AS [value] FROM [KitchenTable] AS [t1])"
+ ") THEN 1 ELSE 0 END) AS [value]",
new CommandParameter("@1", 10));
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Count(),
"SELECT COUNT(*) AS [value] FROM ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]");
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).DefaultIfEmpty(),
"SELECT [q0].[value] AS [value] FROM ("
+ "SELECT NULL AS [Empty]) AS [Empty] "
+ "OUTER APPLY ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]");
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Distinct(),
"SELECT DISTINCT [q0].[value] AS [value] "
+ "FROM (SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])) AS [q0]");
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).First(),
"SELECT TOP (1) [q0].[value] AS [value] FROM ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]");
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).GroupBy(i => i % 3).Select(g => new { g.Key, Count = g.Count() }),
"SELECT [q1].[key] AS [Key],[q1].[a0] AS [Count] FROM (SELECT ([q0].[value] % @1) AS [key], COUNT(*) AS [a0] "
+ "FROM ("
+ "SELECT [t2].[ID] AS [value] FROM [CookTable] AS [t2] "
+ "UNION (SELECT [t3].[ID] AS [value] FROM [KitchenTable] AS [t3])"
+ ") AS [q0] GROUP BY ([q0].[value] % @1)) AS [q1]",
new CommandParameter("@1", 3));
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Max(),
"SELECT MAX([q0].[value]) AS [value] FROM ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]");
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Min(),
"SELECT MIN([q0].[value]) AS [value] FROM ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]");
CheckQuery(
() => Cooks.Select(c => c).Union(Cooks.Select(c => c)).OfType <Chef>().Select(c => c.LetterOfRecommendation),
"SELECT [q1].[LetterOfRecommendation] AS [value] FROM ("
+ "SELECT [q0].[ID],[q0].[FirstName],[q0].[Name],[q0].[IsStarredCook],[q0].[IsFullTimeCook],[q0].[SubstitutedID],[q0].[KitchenID],"
+ "[q0].[KnifeID],[q0].[KnifeClassID] "
+ "FROM ("
+ "SELECT [t2].[ID],[t2].[FirstName],[t2].[Name],[t2].[IsStarredCook],[t2].[IsFullTimeCook],[t2].[SubstitutedID],[t2].[KitchenID],"
+ "[t2].[KnifeID],[t2].[KnifeClassID] FROM [CookTable] AS [t2] "
+ "UNION (SELECT [t3].[ID],[t3].[FirstName],[t3].[Name],[t3].[IsStarredCook],[t3].[IsFullTimeCook],[t3].[SubstitutedID],[t3].[KitchenID],"
+ "[t3].[KnifeID],[t3].[KnifeClassID] FROM [CookTable] AS [t3])"
+ ") AS [q0] WHERE ([q0].[IsStarredCook] = 1)) AS [q1]");
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Single(),
"SELECT TOP (2) [q0].[value] AS [value] FROM ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]");
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Skip(10),
"SELECT [q1].[Key] AS [value] "
+ "FROM ("
+ "SELECT [q0].[value] AS [Key],ROW_NUMBER() OVER (ORDER BY (SELECT @1) ASC) AS [Value] "
+ "FROM ("
+ "SELECT [t2].[ID] AS [value] FROM [CookTable] AS [t2] "
+ "UNION (SELECT [t3].[ID] AS [value] FROM [KitchenTable] AS [t3])"
+ ") AS [q0]) AS [q1] WHERE ([q1].[Value] > @2) ORDER BY [q1].[Value] ASC",
new CommandParameter("@1", 1),
new CommandParameter("@2", 10));
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Sum(),
"SELECT SUM([q0].[value]) AS [value] FROM ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]");
CheckQuery(
() => Cooks.Select(c => c.ID).Union(Kitchens.Select(k => k.ID)).Take(3),
"SELECT TOP (3) [q0].[value] AS [value] FROM ("
+ "SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] "
+ "UNION (SELECT [t2].[ID] AS [value] FROM [KitchenTable] AS [t2])"
+ ") AS [q0]");
}