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