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 Average_WithIntValue()
{
CheckQuery(
() => Kitchens.Average(k => k.RoomNumber),
"SELECT AVG(CONVERT(FLOAT, [t0].[RoomNumber])) AS [value] FROM [KitchenTable] AS [t0]",
row => (object)row.GetValue <double> (new ColumnID("value", 0)));
}
public void Average_WithNullableIntValue()
{
CheckQuery(
() => Kitchens.Average(k => k.LastInspectionScore),
"SELECT AVG(CONVERT(FLOAT, [t0].[LastInspectionScore])) AS [value] FROM [KitchenTable] AS [t0]",
row => (object)row.GetValue <double?> (new ColumnID("value", 0)));
}
public void NullableBool_Coalesce_ToTrue_InWhereClause()
{
// COALESCE to true cannot be ignored.
CheckQuery(
from k in Kitchens where k.PassedLastInspection ?? true select k.ID,
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] WHERE ((COALESCE ([t0].[PassedLastInspection], @1)) = 1)",
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.Where(coalescedNullablePredicate).Select(k => k.ID),
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] "
+ "WHERE ((COALESCE (CASE WHEN ([t0].[LastInspectionScore] = @1) THEN 1 WHEN NOT ([t0].[LastInspectionScore] = @1) THEN 0 ELSE NULL END, @2)) "
+ "= 1)",
new CommandParameter("@1", 0),
new CommandParameter("@2", 1));
}
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 NullableBool_InWhereClause()
{
CheckQuery(
from k in Kitchens where (bool)k.PassedLastInspection select k.ID,
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] WHERE ([t0].[PassedLastInspection] = 1)");
bool?nullableValue = true;
CheckQuery(
from k in Kitchens where (bool)nullableValue select k.ID,
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] WHERE (@1 = 1)",
new CommandParameter("@1", 1));
var kitchenParameter = Expression.Parameter(typeof(Kitchen), "k");
var convertedNullablePredicate =
Expression.Lambda <Func <Kitchen, bool> > (
Expression.Convert(
Expression.Equal(Expression.Property(kitchenParameter, "LastInspectionScore"), Expression.Constant(0, typeof(int?)), true, null),
typeof(bool)),
kitchenParameter);
CheckQuery(
Kitchens.Where(convertedNullablePredicate).Select(k => k.ID),
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] WHERE ([t0].[LastInspectionScore] = @1)",
new CommandParameter("@1", 0));
}
public void NullableBool_InOrderByClause()
{
CheckQuery(
from k in Kitchens orderby k.PassedLastInspection select k.ID,
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] ORDER BY [t0].[PassedLastInspection] ASC");
bool?nullableValue = true;
CheckQuery(
from k in Kitchens orderby nullableValue select k.ID,
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] ORDER BY @1 ASC",
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.OrderBy(nullablePredicate).Select(k => k.ID),
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] "
+ "ORDER BY CASE WHEN ([t0].[LastInspectionScore] = @1) THEN 1 WHEN NOT ([t0].[LastInspectionScore] = @1) THEN 0 ELSE NULL END ASC",
new CommandParameter("@1", 0));
}
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 Sum_WithOrderings()
{
CheckQuery(
() => Kitchens.OrderBy(k => k.Name).Sum(k => k.RoomNumber),
"SELECT SUM([t0].[RoomNumber]) AS [value] FROM [KitchenTable] AS [t0]"
);
}
public void Sum_OnTopLevel()
{
CheckQuery(
() => Kitchens.Sum(k => k.RoomNumber),
"SELECT SUM([t0].[RoomNumber]) AS [value] FROM [KitchenTable] AS [t0]",
row => (object)row.GetValue <int> (new ColumnID("value", 0)));
}
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 Sum_WithOrderings2()
{
CheckQuery(
() => Kitchens.OrderBy(k => k.Name).Take(5).Sum(k => k.RoomNumber),
"SELECT SUM([q0].[Key_RoomNumber]) AS [value] FROM (SELECT TOP (5) [t1].[ID] AS [Key_ID],"
+ "[t1].[Name] AS [Key_Name],[t1].[RestaurantID] AS [Key_RestaurantID],"
+ "[t1].[LastCleaningDay] AS [Key_LastCleaningDay],[t1].[PassedLastInspection] AS [Key_PassedLastInspection],"
+ "[t1].[LastInspectionScore] AS [Key_LastInspectionScore],[t1].[Name] AS [Value] "
+ "FROM [KitchenTable] AS [t1] ORDER BY [t1].[Name] ASC) AS [q0]");
}
public void GroupBy_SubqueryUsedAsGroupByKey()
{
var query = (from c in Cooks
group c by Kitchens.Where(k => k != null).Select(k => k.Name).First()).Select(g => g.Key);
CheckQuery(
query,
"SELECT [q1].[key] AS [value] FROM ("
+ "SELECT [t0].[value] AS [key] "
+ "FROM [CookTable] AS [t2] "
+ "CROSS APPLY (SELECT TOP (1) [t3].[Name] AS [value] FROM [KitchenTable] AS [t3] WHERE ([t3].[ID] IS NOT NULL)) AS [t0] "
+ "GROUP BY [t0].[value]) AS [q1]");
}
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 NullableBool_Coalesce_ToFalse_InWhereClause()
{
// COALESCE to false is simply ignored, as SQL behaves "falsey" with NULL values.
CheckQuery(
from k in Kitchens where k.PassedLastInspection ?? false select k.ID,
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] WHERE ([t0].[PassedLastInspection] = 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(false)),
kitchenParameter);
CheckQuery(
Kitchens.Where(coalescedNullablePredicate).Select(k => k.ID),
"SELECT [t0].[ID] AS [value] FROM [KitchenTable] AS [t0] "
+ "WHERE ([t0].[LastInspectionScore] = @1)",
new CommandParameter("@1", 0));
}
public void CreateRoomList()
{
try
{
MyRooms.Clear();
int element = 0;
for (int i = 0; i < Kitchens.Count; i++)
{
if (Kitchens.ContainsKey(Kitchens.ElementAt(i).Key))
{
MyRooms.Add(new SelectedRoom()
{
// Name = Kitchens[Kitchens.ElementAt(i).Key].Name,
Key = Kitchens[Kitchens.ElementAt(i).Key].Key,
Selected = false,
myRoom = Kitchens[Kitchens.ElementAt(i).Key]
});
}
}
element = Kitchens.Count;
for (int i = 0; i < Toilets.Count; i++)
{
if (Toilets.ContainsKey(Toilets.ElementAt(i).Key))
{
MyRooms.Add(new SelectedRoom()
{
// Name = Toilets[Toilets.ElementAt(i).Key].Name,
Key = Toilets[Toilets.ElementAt(i).Key].Key,
Selected = false,
myRoom = Toilets[Toilets.ElementAt(i).Key]
});
}
}
element += Toilets.Count;
for (int i = 0; i < Hallwaies.Count; i++)
{
if (Hallwaies.ContainsKey(Hallwaies.ElementAt(i).Key))
{
MyRooms.Add(new SelectedRoom()
{
// Name = Hallwaies[Hallwaies.ElementAt(i).Key].Name,
Key = Hallwaies[Hallwaies.ElementAt(i).Key].Key,
Selected = false,
myRoom = Hallwaies[Hallwaies.ElementAt(i).Key]
});
}
}
element += Hallwaies.Count;
for (int i = 0; i < LivingRooms.Count; i++)
{
if (LivingRooms.ContainsKey(LivingRooms.ElementAt(i).Key))
{
MyRooms.Add(new SelectedRoom()
{
Key = LivingRooms[LivingRooms.ElementAt(i).Key].Key,
Selected = false,
myRoom = LivingRooms[LivingRooms.ElementAt(i).Key]
});
}
}
element += LivingRooms.Count;
for (int i = 0; i < Bedrooms.Count; i++)
{
if (Bedrooms.ContainsKey(Bedrooms.ElementAt(i).Key))
{
MyRooms.Add(new SelectedRoom()
{
// Name = Сurtains[Сurtains.ElementAt(i).Key].Name,
Key = Bedrooms[Bedrooms.ElementAt(i).Key].Key,
Selected = false,
myRoom = Bedrooms[Bedrooms.ElementAt(i).Key]
});
}
}
element += Bedrooms.Count;
for (int i = 0; i < Balconies.Count; i++)
{
if (Balconies.ContainsKey(Balconies.ElementAt(i).Key))
{
MyRooms.Add(new SelectedRoom()
{
Key = Balconies[Balconies.ElementAt(i).Key].Key,
Selected = false,
myRoom = Balconies[Balconies.ElementAt(i).Key]
});
}
}
element += Balconies.Count;
for (int i = 0; i < Porches.Count; i++)
{
if (Porches.ContainsKey(Porches.ElementAt(i).Key))
{
MyRooms.Add(new SelectedRoom()
{
Key = Porches[Porches.ElementAt(i).Key].Key,
Selected = false,
myRoom = Porches[Porches.ElementAt(i).Key]
});
}
}
element += Porches.Count;
}
catch (Exception)
{
throw;
}
// return devicesList;
}
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]");
}
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 SetOperation_WithDifferentTypes()
{
CheckQuery(
() => Cooks.Where(c => c.FirstName == "Hugo").Select(c => c.ID).Union(Chefs.Where(c => c.Name == "Boss").Select(c => c.ID)),
"SELECT [t0].[ID] AS [value] FROM [CookTable] AS [t0] WHERE ([t0].[FirstName] = @1) "
+ "UNION (SELECT [t1].[ID] AS [value] FROM [dbo].[ChefTable] AS [t1] WHERE ([t1].[Name] = @2))",
row => (object)row.GetValue <int> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"),
new CommandParameter("@2", "Boss"));
CheckQuery(
() => Cooks.Where(c => c.FirstName == "Hugo").Select(c => c.ID).Union(Kitchens.Where(c => c.Name == "Nino's Kitchen").Select(c => c.ID)),
"SELECT [t0].[ID] AS [value] FROM [CookTable] AS [t0] WHERE ([t0].[FirstName] = @1) "
+ "UNION (SELECT [t1].[ID] AS [value] FROM [KitchenTable] AS [t1] WHERE ([t1].[Name] = @2))",
row => (object)row.GetValue <int> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"),
new CommandParameter("@2", "Nino's Kitchen"));
}