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