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 ChainedMemberAccess_OnCompoundMember_FromSubquery()
{
CheckQuery(
from m in Cooks.Select(x => x.KnifeID).Distinct() select m.ClassID,
"SELECT [q0].[ClassID] AS [value] "
+ "FROM (SELECT DISTINCT [t1].[KnifeID] AS [Value],[t1].[KnifeClassID] AS [ClassID] FROM [CookTable] AS [t1]) AS [q0]",
row => (object)row.GetValue <string> (new ColumnID("value", 0)));
}
public void MemberAccess_OnSubQuery_WithTransformedMembers()
{
var query = Cooks.Select(c => (from a in c.Assistants select a.Name).First().Length);
CheckQuery(query,
"SELECT [q2].[value] AS [value] FROM [CookTable] AS [t0] CROSS APPLY (SELECT TOP (1) " +
"(LEN(([t1].[Name] + '#')) - 1) AS [value] FROM [CookTable] AS [t1] " +
"WHERE ([t0].[ID] = [t1].[AssistedID])) AS [q2]");
}
public void SelectingCompoundMember_FromSubquery()
{
CheckQuery(
from m in Cooks.Select(x => x.KnifeID).Distinct() select m,
"SELECT [q0].[Value] AS [Value],[q0].[ClassID] AS [ClassID] "
+ "FROM (SELECT DISTINCT [t1].[KnifeID] AS [Value],[t1].[KnifeClassID] AS [ClassID] FROM [CookTable] AS [t1]) AS [q0]",
AddNewMetaIDMemberDecoration(
row => (object)new MetaID(row.GetValue <int> (new ColumnID("Value", 0)), row.GetValue <string> (new ColumnID("ClassID", 1)))));
}
public void MemberAccess_OnCoalesce_WithEntities_InSubQuery()
{
var query = Cooks.Select(c => (from a in c.Assistants select a.Substitution ?? a).First().Name);
CheckQuery(query,
"SELECT [q3].[value] AS [value] FROM [CookTable] AS [t0] CROSS APPLY (SELECT TOP (1) " +
"CASE WHEN ([t2].[ID] IS NOT NULL) THEN [t2].[Name] ELSE [t1].[Name] END AS [value] FROM [CookTable] AS [t1] " +
"LEFT OUTER JOIN [CookTable] AS [t2] ON ([t1].[ID] = [t2].[SubstitutedID]) WHERE ([t0].[ID] = [t1].[AssistedID])) AS [q3]");
}
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 MemberAccess_OnSubQuery_WithColumns()
{
var query = Cooks.Select(c => (from a in c.Assistants select a.KnifeID).First().ClassID);
CheckQuery(query,
"SELECT [q2].[value] AS [value] FROM [CookTable] AS [t0] CROSS APPLY (SELECT TOP (1) " +
"[t1].[KnifeClassID] AS [value] FROM [CookTable] AS [t1] " +
"WHERE ([t0].[ID] = [t1].[AssistedID])) AS [q2]");
}
public void MemberAccess_OnCoalesce_WithColumns_InSubQuery()
{
var query = Cooks.Select(c => (from a in c.Assistants select a.Substitution.Name ?? a.Name).First().Length);
CheckQuery(query,
"SELECT [q3].[value] AS [value] FROM [CookTable] AS [t0] "
+ "CROSS APPLY (SELECT TOP (1) CASE WHEN ([t2].[Name] IS NOT NULL) THEN (LEN(([t2].[Name] + '#')) - 1) "
+ "ELSE (LEN(([t1].[Name] + '#')) - 1) END AS [value] "
+ "FROM [CookTable] AS [t1] LEFT OUTER JOIN [CookTable] AS [t2] ON ([t1].[ID] = [t2].[SubstitutedID]) "
+ "WHERE ([t0].[ID] = [t1].[AssistedID])) AS [q3]");
}
public void CompoundValuesComparison_ValuesComingFromSubquery_NotEqual()
{
CheckQuery(
from x in Cooks.Select(c => new { FirstName = c.FirstName, LastName = c.Name }).Distinct()
where x != new { FirstName = "Hugo", LastName = "Boss" }
select x.FirstName,
"SELECT [q0].[FirstName] AS [value] FROM (SELECT DISTINCT [t1].[FirstName] AS [FirstName],[t1].[Name] AS [LastName] " +
"FROM [CookTable] AS [t1]) AS [q0] WHERE (([q0].[FirstName] <> @1) OR ([q0].[LastName] <> @2))",
new CommandParameter("@1", "Hugo"),
new CommandParameter("@2", "Boss")
);
}
public void UnaryPlus()
{
var parameter = Expression.Parameter(typeof(Cook), "c");
var weight = Expression.MakeMemberAccess(parameter, typeof(Cook).GetProperty("Weight"));
var selector = Expression.Lambda <Func <Cook, double> > (Expression.UnaryPlus(weight), parameter);
var query = Cooks.Select(selector); // from c in Cooks select +c.Weight - C# compiler optimizes '+' away
CheckQuery(
query,
"SELECT +[t0].[Weight] AS [value] FROM [CookTable] AS [t0]",
row => (object)row.GetValue <double> (new ColumnID("value", 0))
);
}
public void Power()
{
var parameter = Expression.Parameter(typeof(Cook), "c");
var weight = Expression.MakeMemberAccess(parameter, typeof(Cook).GetProperty("Weight"));
var selector = Expression.Lambda <Func <Cook, double> > (Expression.Power(weight, Expression.Constant(3.0)), parameter);
var query = Cooks.Select(selector); // from c in Cooks select c.Weight**3
CheckQuery(
query,
"SELECT (POWER ([t0].[Weight], @1)) AS [value] FROM [CookTable] AS [t0]",
row => (object)row.GetValue <double> (new ColumnID("value", 0)),
new CommandParameter("@1", 3.0)
);
}
public void MemberAccess_OnConditional_WithColumns()
{
CheckQuery(Cooks.Select(c => (c.IsStarredCook ? c.Name : c.SpecificInformation).Length),
"SELECT CASE WHEN ([t0].[IsStarredCook] = 1) THEN (LEN(([t0].[Name] + '#')) - 1) ELSE (LEN(([t0].[SpecificInformation] + '#')) - 1) END AS [value] " +
"FROM [CookTable] AS [t0]");
}
public void MemberAccess_OnCoalesce_WithColumns()
{
CheckQuery(Cooks.Select(c => (c.FirstName ?? c.Name).Length),
"SELECT CASE WHEN ([t0].[FirstName] IS NOT NULL) THEN (LEN(([t0].[FirstName] + '#')) - 1) ELSE (LEN(([t0].[Name] + '#')) - 1) END AS [value] "
+ "FROM [CookTable] AS [t0]");
}
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_WithCollection()
{
CheckQuery(
() => Cooks.Select(c => c.ID).Union(new[] { 1, 2, 3 }),
"not supported");
}
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]");
}
