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)"); }