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