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)); }
private static object GetResult(MockQueryable <Entity> query) { var statement = SelectWriter <Entity> .CreateStatement(QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select, Map); var command = Command.Create(statement, Profiler); var loader = new Loader <Entity>(command, Map); return(loader.Execute(new ConnectionManager(Database.Connection, TimeSpan.FromMinutes(5)))); }
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 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 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 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_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 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_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 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 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 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 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_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 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)); }
private TResult ExecuteQuery <TResult>(Select select) { IEnumerable <string> columns = null; if (select.From.HasQueries) { var columnsStatement = SchemaWriter.CreateUnionColumnsStatement(select); columns = Command.Create(columnsStatement, _profiler) .ExecuteEnumerable <string>(_connectionManager); } var selectStatement = SelectWriter <TEntity> .CreateStatement( select, _map, columns, _noLock); return((TResult)(new Loader <TEntity>(Command.Create(selectStatement, _profiler), _map).Execute(_connectionManager))); }
public void Select_Except_Test() { var query1 = MockQueryable <Entity> .Create(TableName1); var query2 = MockQueryable <Entity> .Create(TableName2); query1.Except(query2, x => x.Name, x => x.Age); var select = QueryVisitor <Entity> .CreateModel(query1.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} WHERE NOT EXISTS (SELECT TOP (1) [name], [age] FROM [{2}] {3} WHERE (([name] = [{1}].[name]) AND ([age] = [{1}].[age])))", TableName1, select.From.Alias, TableName2, select.SetOperatons[0].Select.From.Alias)); }
public void Select_Except_Where_Test() { var query1 = MockQueryable <Entity> .Create(TableName1); var query2 = MockQueryable <Entity> .Create(TableName2); query1.Where(x => x.Active).Except(query2, x => x.Name.ToUpper(), x => x.Age); var select = QueryVisitor <Entity> .CreateModel(query1.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(true); statement.Text.ShouldEqual(string.Format("SELECT * FROM [{0}] {1} WHERE ([{1}].[active] = @{3}) AND NOT EXISTS (SELECT TOP (1) [UPPER([name])], [age] FROM [{2}] {4} WHERE ((UPPER([name]) = UPPER([{1}].[name])) AND ([age] = [{1}].[age])))", TableName1, select.From.Alias, TableName2, statement.Parameters.First().Key, select.SetOperatons[0].Select.From.Alias)); }
public void Select_Skip_Projection_Test() { var query = MockQueryable <Entity> .Create(TableName1); query.Skip(10); 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.Result.ShouldEqual(Statement.ResultType.Multiple); statement.Parameters.Count.ShouldEqual(0); statement.Text.ShouldEqual(string.Format("SELECT [name], [age] FROM (SELECT [name], [age], ROW_NUMBER() OVER (ORDER BY [name], [age]) AS [__RowNumber__] FROM [{0}] {1}) AS {1} WHERE [__RowNumber__] >= 11", TableName1, select.From.Alias)); }