public void Union_Nested_B_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Union(MockQueryable <Entity> .Create(TableName2).Take(1).Union(MockQueryable <Entity> .Create(TableName3)).Take(3)).Take(5); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.Top.ShouldEqual(5); select.From.Type.ShouldEqual(Data.DataType.Query); select.From.HasQueries.ShouldEqual(true); select.From.Queries.Count.ShouldEqual(2); select.From.Queries[0].From.Type.ShouldEqual(Data.DataType.Query); select.From.Queries[0].From.HasQueries.ShouldEqual(true); select.From.Queries[0].From.Queries.Count.ShouldEqual(2); select.From.Queries[0].Top.ShouldEqual(3); select.From.Queries[0].From.Queries[0].HasConditions.ShouldEqual(false); select.From.Queries[0].From.Queries[0].From.Table.ShouldNotBeNull(); select.From.Queries[0].From.Queries[0].From.Type.ShouldEqual(Data.DataType.Table); select.From.Queries[0].From.Queries[0].From.Table.Name.ShouldEqual(TableName3); select.From.Queries[0].From.Queries[1].From.Table.ShouldNotBeNull(); select.From.Queries[0].From.Queries[1].From.Type.ShouldEqual(Data.DataType.Table); select.From.Queries[0].From.Queries[1].From.Table.Name.ShouldEqual(TableName2); select.From.Queries[0].From.Queries[1].Top.ShouldEqual(1); select.From.Queries[1].HasConditions.ShouldEqual(false); select.From.Queries[1].From.Table.ShouldNotBeNull(); select.From.Queries[1].From.Type.ShouldEqual(Data.DataType.Table); select.From.Queries[1].From.Table.Name.ShouldEqual(TableName1); }
public void Union_Chained_Post_Operations_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Union(MockQueryable <Entity> .Create(TableName2)). Union(MockQueryable <Entity> .Create(TableName3)). Union(MockQueryable <Entity> .Create(TableName4)).Take(5); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.Top.ShouldEqual(5); select.From.Type.ShouldEqual(Data.DataType.Query); select.From.HasQueries.ShouldEqual(true); select.From.Queries.Count.ShouldEqual(4); select.From.Queries[0].From.Table.ShouldNotBeNull(); select.From.Queries[0].From.Table.Name.ShouldEqual(TableName4); select.From.Queries[1].From.Table.ShouldNotBeNull(); select.From.Queries[1].From.Table.Name.ShouldEqual(TableName3); select.From.Queries[2].From.Table.ShouldNotBeNull(); select.From.Queries[2].From.Table.Name.ShouldEqual(TableName2); select.From.Queries[3].From.Table.ShouldNotBeNull(); select.From.Queries[3].From.Table.Name.ShouldEqual(TableName1); }
public void Nested_Union_Projection_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Union(MockQueryable <Entity> .Create(TableName2).Take(1).Union(MockQueryable <Entity> .Create(TableName3)).Take(3)).Take(5); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.Projection = new List <SelectProjection> { new SelectProjection { Projection = Projection.Create.Field("Name") }, new SelectProjection { Projection = Projection.Create.Field("Age") } }; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Parameters.Count().ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT TOP (5) [name], [age] FROM (SELECT TOP (3) [name], [age] FROM (SELECT [name], [age] FROM [XLIST_3] {0} UNION SELECT TOP (1) [name], [age] FROM [XLIST_2] {1}) AS {2} UNION SELECT [name], [age] FROM [XLIST_1] {3}) AS {4}", select.From.Queries[0].From.Queries[0].From.Alias, select.From.Queries[0].From.Queries[1].From.Alias, select.From.Queries[0].From.Alias, select.From.Queries[1].From.Alias, select.From.Alias)); }
public void should_render_sync_include_sql() { var target = MockQueryable <Entity> .Create(TableName1); var source = MockQueryable <Entity> .Create(TableName2); var importId = Guid.NewGuid(); target.Where(x => x.ImportId != importId).SyncWith(source.Where(x => x.ImportId == importId), x => x.Created, SyncFields.Include, x => x.Name, x => x.Flag); var query = QueryVisitor <Entity> .CreateModel(target.Expression, x => ((MockQueryable <Entity>)x).Name); query.Operation.ShouldEqual(Query.OperationType.SyncWith); query.SyncWith.ShouldNotBeNull(); var sync = query.SyncWith; var statement = SyncWriter <Entity> .CreateStatement(sync, Map); statement.Result.ShouldEqual(Statement.ResultType.None); statement.Parameters.Count.ShouldEqual(2); statement.Parameters.First().Value.ShouldEqual(importId); statement.Parameters.Skip(1).First().Value.ShouldEqual(importId); statement.Text.ShouldEqual(string.Format( "UPDATE [{0}] SET [{0}].[name] = [{1}].[name], [{0}].[flag] = [{1}].[flag] FROM [{2}] [{0}] INNER JOIN [{3}] [{1}] ON [{0}].[created] = [{1}].[created] AND " + "([{1}].[import_id] = @{4}) WHERE (([{0}].[import_id] <> @{5}) OR [{0}].[import_id] IS NULL)", sync.Target.From.Alias, sync.Source.From.Alias, TableName1, TableName2, statement.Parameters.First().Key, statement.Parameters.Skip(1).First().Key)); }
public void should_set_single_flag() { var query = MockQueryable <Entity> .Create(TableName1); query.Single(); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.Single.ShouldEqual(true); }
public void First_Or_Default_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.FirstOrDefault(); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.FirstOrDefault.ShouldEqual(true); }
public void Randomize_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Randomize(); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.Randomize.ShouldEqual(true); }
public void No_Where_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Take(10); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.HasWhere.ShouldEqual(false); }
public void Tablename_Test() { var query = MockQueryable <Entity> .Create(TableName1); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.From.Table.Name.ShouldEqual(TableName1); }
public void Skip_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Skip(15); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.HasStart.ShouldEqual(true); select.Start.ShouldEqual(16); }
public void Where_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Where(x => x.Age == 33 && (bool)x.Values["opt_out"] || x.Name.StartsWith("yada")); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.Where.ShouldNotBeNull(); select.HasWhere.ShouldEqual(true); }
public void Columns_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Union(MockQueryable <Entity> .Create(TableName2).Take(5).Union(MockQueryable <Entity> .Create(TableName3).Skip(4).OrderBy(x => x.Active))); var statement = SchemaWriter.CreateUnionColumnsStatement(QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select); statement.Parameters.Count().ShouldEqual(0); statement.Text.ShouldEqual("SELECT [name], CASE [system_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [system_type_id] END AS [system_type_id], CASE [user_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [user_type_id] END AS [user_type_id] FROM [sys].[columns] WHERE [object_id] = OBJECT_ID(N'XLIST_1') INTERSECT " + "SELECT [name], CASE [system_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [system_type_id] END AS [system_type_id], CASE [user_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [user_type_id] END AS [user_type_id] FROM [sys].[columns] WHERE [object_id] = OBJECT_ID(N'XLIST_2') INTERSECT " + "SELECT [name], CASE [system_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [system_type_id] END AS [system_type_id], CASE [user_type_id] WHEN 167 THEN 231 WHEN 175 THEN 239 WHEN 35 THEN 99 ELSE [user_type_id] END AS [user_type_id] FROM [sys].[columns] WHERE [object_id] = OBJECT_ID(N'XLIST_3')"); }
public void Take_Percent_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.TakePercent(5); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.HasTop.ShouldEqual(true); select.TopType.ShouldEqual(Select.TopValueType.Percent); select.Top.ShouldEqual(5); }
public void Select_Test() { var query = MockQueryable <Entity> .Create(TableName1); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT * FROM [{0}] {1}", TableName1, select.From.Alias)); }
public void should_render_sql_with_nolock_hint() { var query = MockQueryable <Entity> .Create(TableName1); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map, noLock : true); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT * FROM [{0}] {1} WITH (NOLOCK)", TableName1, select.From.Alias)); }
public void OrderBy_Descending_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.OrderByDescending(x => x.Name.Substring(0, 5)).OrderByDescending(x => x.Age); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.OrderBy.ShouldNotBeNull(); select.HasOrderBy.ShouldEqual(true); select.OrderBy.Count.ShouldEqual(2); select.OrderBy.All(x => x.Order == Order.Descending).ShouldEqual(true); }
public void should_render_any_sql() { var query = MockQueryable <Entity> .Create(TableName1); query.Any(); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Scalar); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT CAST(CASE WHEN EXISTS (SELECT * FROM [{0}] {1}) THEN 1 ELSE 0 END AS bit)", TableName1, select.From.Alias)); }
public void Select_Order_By_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.OrderBy(y => y.Name).OrderByDescending(y => y.Values["CompanyName"]); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT * FROM [{0}] {1} ORDER BY [name] ASC, [companyname] DESC", TableName1, select.From.Alias)); }
public void Copy_To_Query_Test() { var query1 = MockQueryable <Entity> .Create(TableName1); query1.CopyTo(MockQueryable <Entity> .Create(TableName2)); var query2 = QueryVisitor <Entity> .CreateModel(query1.Expression, x => ((MockQueryable <Entity>)x).Name); query2.ShouldNotBeNull(); query2.CopyTo.Into.ShouldNotBeNull(); query2.CopyTo.Into.Name.ShouldEqual(TableName2); query2.CopyTo.Query.From.Type.ShouldEqual(Data.DataType.Table); query2.CopyTo.Query.From.Table.ShouldNotBeNull(); query2.CopyTo.Query.From.Table.Name.ShouldEqual(TableName1); }
public void should_render_duplicates_with_double_order_descending_sql() { var query = MockQueryable <Entity> .Create(TableName1); query.Duplicates(x => x.Name, x => x.Age, Order.Descending, x => x.Created, Order.Ascending); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY [age] DESC, [created] ASC) AS [__Partition__] FROM [{0}] {1}) AS {1} WHERE [__Partition__] > 1", TableName1, select.From.Alias)); }
public void Select_Skip_And_Take_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Skip(10).Take(10); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [id]) AS [__RowNumber__] FROM [{0}] {1}) AS {1} WHERE [__RowNumber__] BETWEEN 11 AND 20", TableName1, select.From.Alias)); }
public void Select_Where_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Where(y => y.Name == "hello"); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(1); statement.Parameters.First().Value.ShouldEqual("hello"); statement.Text.ShouldEqual(string.Format("SELECT * FROM [{0}] {1} WHERE ([{1}].[name] = @{2})", TableName1, select.From.Alias, statement.Parameters.First().Key)); }
public void Select_Paged_Order_By_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.OrderBy(y => y.Name).OrderByDescending(y => y.Values["CompanyName"]).Skip(10).Take(10); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [name] ASC, [companyname] DESC) AS [__RowNumber__] FROM [{0}] {1}) AS {1} WHERE [__RowNumber__] BETWEEN 11 AND 20", TableName1, select.From.Alias)); }
public void should_render_multi_distinct_sql() { var query = MockQueryable <Entity> .Create(TableName1); query.Distinct(x => x.Name.ToUpper()).Distinct(x => x.Age); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY UPPER([name]), [age] ORDER BY UPPER([name]) ASC, [age] ASC) AS [__Partition__] FROM [{0}] {1}) AS {1} WHERE [__Partition__] = 1", TableName1, select.From.Alias)); }
public void should_render_sql_with_nolock_hint_in_nested_query() { var query = MockQueryable <Entity> .Create(TableName1); query.Skip(10); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map, noLock : true); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [id]) AS [__RowNumber__] FROM [{0}] {1} WITH (NOLOCK)) AS {1} WHERE [__RowNumber__] >= 11", TableName1, select.From.Alias)); }
public void Select_Paged_Where_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Where(y => y.Name == "hello").Skip(10).Take(10); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(1); statement.Parameters.First().Value.ShouldEqual("hello"); statement.Text.ShouldEqual(string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [id]) AS [__RowNumber__] FROM [{0}] {1} WHERE ([{1}].[name] = @{2})) AS {1} WHERE [__RowNumber__] BETWEEN 11 AND 20", TableName1, select.From.Alias, statement.Parameters.First().Key)); }
public void should_render_duplicates_with_precedence_sql() { var query = MockQueryable <Entity> .Create(TableName1); query.Duplicates(x => x.Name, x => x.Age > 50, Order.Ascending); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(1); statement.Parameters.First().Value.ShouldEqual(50); statement.Text.ShouldEqual(string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY CASE WHEN ([{1}].[age] > @{2}) THEN 1 ELSE 0 END ASC) AS [__Partition__] FROM [{0}] {1}) AS {1} WHERE [__Partition__] > 1", TableName1, select.From.Alias, statement.Parameters.First().Key)); }
public void should_render_any_and_where_sql() { var query = MockQueryable <Entity> .Create(TableName1); query.Any(x => x.Age == 44); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Result.ShouldEqual(Statement.ResultType.Scalar); statement.Parameters.Count.ShouldEqual(1); statement.Parameters.First().Value.ShouldEqual(44); statement.Text.ShouldEqual(string.Format("SELECT CAST(CASE WHEN EXISTS (SELECT * FROM [{0}] {1} WHERE ([{1}].[age] = @{2})) THEN 1 ELSE 0 END AS bit)", TableName1, select.From.Alias, statement.Parameters.First().Key)); }
public void Chained_Union_Top_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Union(MockQueryable <Entity> .Create(TableName2).Take(1).Union(MockQueryable <Entity> .Create(TableName3).Take(2))); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; var statement = SelectWriter <Entity> .CreateStatement(select, Map); statement.Parameters.Count().ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT TOP (2) * FROM [XLIST_3] {0} UNION SELECT TOP (1) * FROM [XLIST_2] {1} UNION SELECT * FROM [XLIST_1] {2}", select.From.Queries[0].From.Queries[0].From.Alias, select.From.Queries[0].From.Queries[1].From.Alias, select.From.Queries[1].From.Alias)); }
public void Multiple_Where_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Where(x => x.Age == 33). First(x => x.Age != 5); var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select; select.ShouldNotBeNull(); select.First.ShouldEqual(true); select.Where.ShouldNotBeNull(); select.HasWhere.ShouldEqual(true); select.Where.Type.ShouldEqual(Operator.OperatorType.And); select.Where.LeftOperand.Type.ShouldEqual(Operand.OperandType.Operator); select.Where.LeftOperand.Operator.Type.ShouldEqual(Operator.OperatorType.NotEqual); select.Where.RightOperand.Type.ShouldEqual(Operand.OperandType.Operator); select.Where.RightOperand.Operator.Type.ShouldEqual(Operator.OperatorType.Equal); }