public void Average_OnTopLevel()
{
CheckQuery(
() => Cooks.Average(c => c.Weight),
"SELECT AVG([t0].[Weight]) AS [value] FROM [CookTable] AS [t0]",
row => (object)row.GetValue <double> (new ColumnID("value", 0)));
}
public void Any_OnTopLevel_WithoutPredicate()
{
CheckQuery(
() => Cooks.Any(),
"SELECT CONVERT(BIT, CASE WHEN EXISTS((SELECT [t0].[ID] FROM [CookTable] AS [t0])) THEN 1 ELSE 0 END) AS [value]",
row => (object)row.GetValue <bool> (new ColumnID("value", 0)));
}
public void SetOperation_InSubQuery()
{
CheckQuery(
() => from k in Cooks
from x in (Cooks.Where(c => c.FirstName == "Hugo").Select(c => c.ID).Union(Cooks.Where(c => c.Name == "Boss").Select(c => c.ID)))
where k.ID == x
select x,
"SELECT [q0].[value] AS [value] "
+ "FROM [CookTable] AS [t1] "
+ "CROSS APPLY ("
+ "SELECT [t2].[ID] AS [value] FROM [CookTable] AS [t2] WHERE ([t2].[FirstName] = @1) "
+ "UNION (SELECT [t3].[ID] AS [value] FROM [CookTable] AS [t3] WHERE ([t3].[Name] = @2))) "
+ "AS [q0] "
+ "WHERE ([t1].[ID] = [q0].[value])",
row => (object)row.GetValue <int> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"),
new CommandParameter("@2", "Boss"));
CheckQuery(
() => from k in Cooks
from x in (Cooks.Where(c => c.FirstName == "Hugo").Select(c => c.ID).Concat(Cooks.Where(c => c.Name == "Boss").Select(c => c.ID)))
where k.ID == x
select x,
"SELECT [q0].[value] AS [value] "
+ "FROM [CookTable] AS [t1] "
+ "CROSS APPLY ("
+ "SELECT [t2].[ID] AS [value] FROM [CookTable] AS [t2] WHERE ([t2].[FirstName] = @1) "
+ "UNION ALL (SELECT [t3].[ID] AS [value] FROM [CookTable] AS [t3] WHERE ([t3].[Name] = @2))) "
+ "AS [q0] "
+ "WHERE ([t1].[ID] = [q0].[value])",
row => (object)row.GetValue <int> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"),
new CommandParameter("@2", "Boss"));
}
public void AssignOrder(Order order)
{
Orders.Add(order);
foreach (Recipe recipe in order.Recipes)
{
if (!Cooks.Any())
{
return;
}
Cook minimumRecipeCook = null;
foreach (Cook cook in Cooks)
{
if (minimumRecipeCook == null)
{
minimumRecipeCook = cook;
}
else
{
if (cook.Recipes.Count < minimumRecipeCook.Recipes.Count)
{
minimumRecipeCook = cook;
}
}
}
if (minimumRecipeCook != null)
{
minimumRecipeCook.Recipes.Add(recipe);
Console.WriteLine("A recipe has been assigned to the cook : {0}", minimumRecipeCook.GetHashCode().ToString());
}
}
}
public void GroupBy_WithResultSelector_AndElementSelector()
{
CheckQuery(
Cooks.GroupBy(c => c.Name, c => c.ID, (key, group) => new { Name = key }),
"SELECT [q0].[key] AS [Name] "
+ "FROM (SELECT [t1].[Name] AS [key] FROM [CookTable] AS [t1] GROUP BY [t1].[Name]) AS [q0]",
row => (object)new { Name = row.GetValue <string> (new ColumnID("Name", 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 GroupBy_WithConstantKey_GetsReplacedBySubquery()
{
CheckQuery(Cooks.GroupBy(c => 0).Select(c => c.Key),
"SELECT [q1].[key] AS [value] FROM ("
+ "SELECT [t0].[value] AS [key] FROM [CookTable] AS [t2] CROSS APPLY (SELECT @1 AS [value]) AS [t0] GROUP BY [t0].[value]"
+ ") AS [q1]",
new CommandParameter("@1", 0));
}
public void Any_OrderingsRemoved()
{
CheckQuery(
() => Cooks.OrderBy(c => c.FirstName).Any(),
"SELECT CONVERT(BIT, CASE WHEN EXISTS((SELECT [t0].[ID] FROM [CookTable] AS [t0])) THEN 1 ELSE 0 END) AS [value]",
row => (object)row.GetValue <bool> (new ColumnID("value", 0))
);
}
public void TakeAndCast()
{
CheckQuery(
() => Cooks.Take(1).Cast <object>(),
"SELECT TOP (1) [t0].[ID],[t0].[FirstName],[t0].[Name],[t0].[IsStarredCook],[t0].[IsFullTimeCook],[t0].[SubstitutedID],[t0].[KitchenID],"
+ "[t0].[KnifeID],[t0].[KnifeClassID] "
+ "FROM [CookTable] AS [t0]");
}
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 TakeAndOfType()
{
CheckQuery(
() => Cooks.Take(1).OfType <Chef>(),
"SELECT TOP (1) [t0].[ID],[t0].[FirstName],[t0].[Name],[t0].[IsStarredCook],[t0].[IsFullTimeCook],[t0].[SubstitutedID],[t0].[KitchenID],"
+ "[t0].[KnifeID],[t0].[KnifeClassID] "
+ "FROM [CookTable] AS [t0] WHERE ([t0].[IsStarredCook] = 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 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_OnCoealesce_WithEntities_InSubQuery_InWhereClause()
{
var query = Cooks.Where(c => (from a in c.Assistants select a.Substitution ?? a).First().Name == "Hugo").Select(c => c.Name);
CheckQuery(query, "SELECT [t0].[Name] AS [value] FROM [CookTable] AS [t0] WHERE ((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])) = @1)",
new CommandParameter("@1", "Hugo"));
}
public void Any_OnTopLevel_WithPredicate()
{
CheckQuery(
() => Cooks.Any(c => c.FirstName == "Hugo"),
"SELECT CONVERT(BIT, CASE WHEN EXISTS((SELECT [t0].[ID] FROM [CookTable] AS [t0] WHERE ([t0].[FirstName] = @1))) "
+ "THEN 1 ELSE 0 END) AS [value]",
row => (object)row.GetValue <bool> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"));
}
public void All_OrderingsRemoved()
{
CheckQuery(
() => Cooks.OrderBy(c => c.FirstName).All(c => c.Name == "Hugo"),
"SELECT CONVERT(BIT, CASE WHEN NOT EXISTS((SELECT [t0].[ID] FROM [CookTable] AS [t0] WHERE NOT ([t0].[Name] = @1))) "
+ "THEN 1 ELSE 0 END) AS [value]",
row => (object)row.GetValue <bool> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"));
}
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_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 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 AllAfterGroupBy_WithResultSelector()
{
CheckQuery(
() => Cooks.GroupBy(c => c.Name, c => c.ID, (key, group) => new { Name = key }).All(x => x.Name != null),
"SELECT CONVERT(BIT, CASE WHEN NOT EXISTS(("
+ "SELECT [q0].[key] AS [Name] "
+ "FROM (SELECT [t1].[Name] AS [key] FROM [CookTable] AS [t1] GROUP BY [t1].[Name]) AS [q0] "
+ "WHERE NOT ([q0].[key] IS NOT NULL))) "
+ "THEN 1 ELSE 0 END) AS [value]");
}
public void OfType()
{
CheckQuery(
Cooks.OfType <Chef> (),
"SELECT [t0].[ID],[t0].[FirstName],[t0].[Name],[t0].[IsStarredCook],[t0].[IsFullTimeCook],[t0].[SubstitutedID],[t0].[KitchenID],"
+ "[t0].[KnifeID],[t0].[KnifeClassID] "
+ "FROM [CookTable] AS [t0] WHERE ([t0].[IsStarredCook] = 1)",
row => (object)(Chef)row.GetEntity <Cook> (
new ColumnID("ID", 0),
new ColumnID("FirstName", 1),
new ColumnID("Name", 2),
new ColumnID("IsStarredCook", 3),
new ColumnID("IsFullTimeCook", 4),
new ColumnID("SubstitutedID", 5),
new ColumnID("KitchenID", 6),
new ColumnID("KnifeID", 7),
new ColumnID("KnifeClassID", 8))
);
CheckQuery(
Chefs.OfType <Chef>(),
"SELECT [t0].[ID],[t0].[FirstName],[t0].[Name],[t0].[IsStarredCook],[t0].[IsFullTimeCook],[t0].[SubstitutedID]," +
"[t0].[KitchenID],[t0].[KnifeID],[t0].[KnifeClassID],[t0].[LetterOfRecommendation] FROM [dbo].[ChefTable] AS [t0] WHERE (@1 = 1)",
// ReSharper disable RedundantCast
row => (object)(Chef)row.GetEntity <Chef> (
// ReSharper restore RedundantCast
new ColumnID("ID", 0),
new ColumnID("FirstName", 1),
new ColumnID("Name", 2),
new ColumnID("IsStarredCook", 3),
new ColumnID("IsFullTimeCook", 4),
new ColumnID("SubstitutedID", 5),
new ColumnID("KitchenID", 6),
new ColumnID("KnifeID", 7),
new ColumnID("KnifeClassID", 8),
new ColumnID("LetterOfRecommendation", 9)),
new CommandParameter("@1", 1));
CheckQuery(
Chefs.OfType <Cook> (),
"SELECT [t0].[ID],[t0].[FirstName],[t0].[Name],[t0].[IsStarredCook],[t0].[IsFullTimeCook],[t0].[SubstitutedID]," +
"[t0].[KitchenID],[t0].[KnifeID],[t0].[KnifeClassID],[t0].[LetterOfRecommendation] FROM [dbo].[ChefTable] AS [t0] WHERE (@1 = 1)",
row => (object)(Cook)row.GetEntity <Chef> (
new ColumnID("ID", 0),
new ColumnID("FirstName", 1),
new ColumnID("Name", 2),
new ColumnID("IsStarredCook", 3),
new ColumnID("IsFullTimeCook", 4),
new ColumnID("SubstitutedID", 5),
new ColumnID("KitchenID", 6),
new ColumnID("KnifeID", 7),
new ColumnID("KnifeClassID", 8),
new ColumnID("LetterOfRecommendation", 9)),
new CommandParameter("@1", 1));
}
public void CompileInvokeCombination()
{
Expression <Func <Cook, bool> > predicate1 = c => c.ID > 100;
Expression <Func <Cook, bool> > predicate2 = c => c.Name != null;
CheckQuery(
Cooks.Where(c => predicate1.Compile() (c) && predicate2.Compile() (c)).Select(c => c.FirstName),
"SELECT [t0].[FirstName] AS [value] FROM [CookTable] AS [t0] WHERE (([t0].[ID] > @1) AND ([t0].[Name] IS NOT NULL))",
new CommandParameter("@1", 100)
);
}
public void SetOperation_WithDifferentColumnLists()
{
CheckQuery(
() => Cooks.Union(Chefs.Select(c => c)),
"SELECT [t0].[ID],[t0].[FirstName],[t0].[Name],[t0].[IsStarredCook],[t0].[IsFullTimeCook],[t0].[SubstitutedID],[t0].[KitchenID],"
+ "[t0].[KnifeID],[t0].[KnifeClassID] "
+ "FROM [CookTable] AS [t0] "
+ "UNION (SELECT [t1].[ID],[t1].[FirstName],[t1].[Name],[t1].[IsStarredCook],[t1].[IsFullTimeCook],[t1].[SubstitutedID],[t1].[KitchenID],"
+ "[t1].[KnifeID],[t1].[KnifeClassID],[t1].[LetterOfRecommendation] "
+ "FROM [dbo].[ChefTable] AS [t1])");
}
public void TakeAndContains()
{
Cook cook = new Cook {
ID = 5, FirstName = "Hugo", Name = "Hanser"
};
CheckQuery(
() => Cooks.Take(1).Contains(cook),
"SELECT CONVERT(BIT, CASE WHEN @1 IN (SELECT TOP (1) [t0].[ID] FROM [CookTable] AS [t0]) THEN 1 ELSE 0 END) AS [value]",
new CommandParameter("@1", cook.ID));
}
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 GroupBy_AfterTake()
{
CheckQuery(
Cooks.Take(10).GroupBy(c => c.Kitchen.ID).Select(g => g.Key),
"SELECT [q1].[key] AS [value] "
+ "FROM (SELECT [q0].[KitchenID] AS [key] "
+ "FROM (SELECT TOP (10) [t2].[ID],[t2].[FirstName],[t2].[Name],[t2].[IsStarredCook],[t2].[IsFullTimeCook],[t2].[SubstitutedID],"
+ "[t2].[KitchenID],[t2].[KnifeID],[t2].[KnifeClassID] "
+ "FROM [CookTable] AS [t2]) AS [q0] "
+ "GROUP BY [q0].[KitchenID]) AS [q1]");
}
public void GroupBy_WithResultSelector_AndElementSelector_AndAggregate()
{
CheckQuery(
Cooks.GroupBy(c => c.Name, c => c.ID, (key, group) => new { Name = key, Count = group.Min() }),
"SELECT [q0].[key] AS [Name], [q0].[a0] AS [Count] "
+ "FROM (SELECT [t1].[Name] AS [key],MIN([t1].[ID]) AS [a0] FROM [CookTable] [t1] GROUP BY [t1].[Name]) AS [q0]",
row => (object)new
{
Name = row.GetValue <string> (new ColumnID("Name_key", 0)),
Count = row.GetValue <int> (new ColumnID("Count", 1))
});
}
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))
);
}