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 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 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_CausesOrderByWithTakeToWork()
{
CheckQuery(
() => Cooks.Where(c => c.FirstName == "Hugo").OrderBy(c => c.ID).Select(c => c.ID).Take(3)
.Union(Cooks.Where(c => c.Name == "Boss").OrderBy(c => c.ID).Select(c => c.ID).Take(2)),
"SELECT [q0].[value] AS [value] "
+ "FROM ("
+ "SELECT TOP (3) [t1].[ID] AS [value] FROM [CookTable] AS [t1] WHERE ([t1].[FirstName] = @1) ORDER BY [t1].[ID] ASC "
+ "UNION (SELECT TOP (2) [t2].[ID] AS [value] FROM [CookTable] AS [t2] WHERE ([t2].[Name] = @2) ORDER BY [t2].[ID] ASC)"
+ ") AS [q0]",
row => (object)row.GetValue <int> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"),
new CommandParameter("@2", "Boss"));
}
public void InformationIsNothingCall()
{
var parameterExpression = Expression.Parameter(typeof(Cook), "c");
var vbCompareStringExpression =
Expression.Call(
typeof(Information).GetMethod("IsNothing"),
Expression.MakeMemberAccess(parameterExpression, typeof(Cook).GetProperty("Name")));
var query = Cooks
.Where(Expression.Lambda <Func <Cook, bool> > (vbCompareStringExpression, parameterExpression))
.Select(c => c.Name);
CheckQuery(
query,
"SELECT [t0].[Name] AS [value] FROM [CookTable] AS [t0] WHERE ([t0].[Name] IS NULL)");
}
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"));
}
public void VBCompareStringExpression()
{
var parameterExpression = Expression.Parameter(typeof(Cook), "c");
var vbCompareStringExpression =
Expression.Equal(
Expression.Call(
typeof(Operators).GetMethod("CompareString"),
Expression.Constant("string1"),
Expression.MakeMemberAccess(parameterExpression, typeof(Cook).GetProperty("Name")),
Expression.Constant(true)),
Expression.Constant(0));
var query = Cooks
.Where(Expression.Lambda <Func <Cook, bool> > (vbCompareStringExpression, parameterExpression))
.Select(c => c.Name);
CheckQuery(
query,
"SELECT [t0].[Name] AS [value] FROM [CookTable] AS [t0] WHERE (@1 = [t0].[Name])",
new CommandParameter("@1", "string1"));
}
public void SetOperation_CausesOrderByToBeIgnored()
{
CheckQuery(
() => Cooks.Where(c => c.FirstName == "Hugo").OrderBy(c => c.ID).Select(c => c.ID)
.Union(Cooks.Where(c => c.Name == "Boss").OrderBy(c => c.ID).Select(c => c.ID)),
"SELECT [t0].[ID] AS [value] FROM [CookTable] AS [t0] WHERE ([t0].[FirstName] = @1) "
+ "UNION (SELECT [t1].[ID] AS [value] FROM [CookTable] 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").OrderBy(c => c.ID).Select(c => c.ID)
.Concat(Cooks.Where(c => c.Name == "Boss").OrderBy(c => c.ID).Select(c => c.ID)),
"SELECT [t0].[ID] AS [value] FROM [CookTable] AS [t0] WHERE ([t0].[FirstName] = @1) "
+ "UNION ALL (SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] WHERE ([t1].[Name] = @2))",
row => (object)row.GetValue <int> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"),
new CommandParameter("@2", "Boss"));
}
public void InvocationExpression_WithCustomExpressions()
{
Expression <Func <Cook, bool> > predicate1 = c => c.ID > 100;
Expression <Func <Cook, bool> > predicate2 = c => c.Name != null;
// c => c.ID > 100 && ((c1 => c1.Name != null) (c))
var combinedPredicate =
Expression.Lambda <Func <Cook, bool> > (
Expression.AndAlso(
predicate1.Body,
Expression.Invoke(predicate2, predicate1.Parameters.Cast <Expression>())
),
predicate1.Parameters);
CheckQuery(
Cooks.Where(combinedPredicate).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 Is()
{
CheckQuery(
Cooks.Where(c => c is 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)"
);
CheckQuery(
#pragma warning disable 183
Cooks.Where(c => c is Cook),
#pragma warning restore 183
"SELECT [t0].[ID],[t0].[FirstName],[t0].[Name],[t0].[IsStarredCook],[t0].[IsFullTimeCook],[t0].[SubstitutedID],[t0].[KitchenID],"
+ "[t0].[KnifeID],[t0].[KnifeClassID] "
+ "FROM [CookTable] AS [t0] WHERE (@1 = 1)",
new CommandParameter("@1", 1)
);
CheckQuery(
#pragma warning disable 183
Chefs.Where(c => c is Cook),
#pragma warning restore 183
"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)",
new CommandParameter("@1", 1));
CheckQuery(
#pragma warning disable 183
Chefs.Where(c => c is Chef),
#pragma warning restore 183
"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)",
new CommandParameter("@1", 1));
}
public void Concat_OnTopLevel()
{
CheckQuery(
() => Cooks.Where(c => c.FirstName == "Hugo").Select(c => c.ID).Concat(Cooks.Where(c => c.Name == "Boss").Select(c => c.ID)),
"SELECT [t0].[ID] AS [value] FROM [CookTable] AS [t0] WHERE ([t0].[FirstName] = @1) "
+ "UNION ALL (SELECT [t1].[ID] AS [value] FROM [CookTable] 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)
.Concat(Cooks.Where(c => c.Name == "Boss").Select(c => c.ID))
.Concat(Cooks.Where(c => c.ID == 100).Select(c => c.ID)),
"SELECT [t0].[ID] AS [value] FROM [CookTable] AS [t0] WHERE ([t0].[FirstName] = @1) "
+ "UNION ALL (SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] WHERE ([t1].[Name] = @2)) "
+ "UNION ALL (SELECT [t2].[ID] AS [value] FROM [CookTable] AS [t2] WHERE ([t2].[ID] = @3))",
row => (object)row.GetValue <int> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"),
new CommandParameter("@2", "Boss"),
new CommandParameter("@3", 100));
CheckQuery(
() => Cooks.Where(c => c.FirstName == "Hugo").Select(c => c.ID)
.Concat(
Cooks.Where(c => c.Name == "Boss").Select(c => c.ID)
.Concat(Cooks.Where(c => c.ID == 100).Select(c => c.ID))),
// The difference is in the parentheses.
"SELECT [t0].[ID] AS [value] FROM [CookTable] AS [t0] WHERE ([t0].[FirstName] = @1) "
+ "UNION ALL (SELECT [t1].[ID] AS [value] FROM [CookTable] AS [t1] WHERE ([t1].[Name] = @2) "
+ "UNION ALL (SELECT [t2].[ID] AS [value] FROM [CookTable] AS [t2] WHERE ([t2].[ID] = @3)))",
row => (object)row.GetValue <int> (new ColumnID("value", 0)),
new CommandParameter("@1", "Hugo"),
new CommandParameter("@2", "Boss"),
new CommandParameter("@3", 100));
}
public void InvocationExpression_Inline()
{
CheckQuery(
Cooks.Where(c => ((Func <Cook, bool>)(c1 => c1.Name != null))(c)).Select(c => c.FirstName),
"SELECT [t0].[FirstName] AS [value] FROM [CookTable] AS [t0] WHERE ([t0].[Name] IS NOT NULL)");
}