public void Select_Count_Test()
{
var query = MockQueryable <Entity> .Create(TableName1);
query.Count();
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 COUNT(*) 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_Randomize_Test()
{
var query = MockQueryable <Entity> .Create(TableName1);
query.Randomize();
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 NEWID()", 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 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 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 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 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));
}
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 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);
}
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 Union_Nested_A_Test()
{
var query = MockQueryable <Entity> .Create(TableName1);
query.Where(x => x.Age == 33).Union(MockQueryable <Entity> .Create(TableName2).
Union(MockQueryable <Entity> .Create(TableName3).Where(x => x.Active))).
Union(MockQueryable <Entity> .Create(TableName4).Take(5)).Take(6);
var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select;
select.ShouldNotBeNull();
select.Top.ShouldEqual(6);
select.From.Type.ShouldEqual(Data.DataType.Query);
select.From.HasQueries.ShouldEqual(true);
select.From.Queries.Count.ShouldEqual(3);
select.From.Queries[0].From.Table.ShouldNotBeNull();
select.From.Queries[0].From.Type.ShouldEqual(Data.DataType.Table);
select.From.Queries[0].From.Table.Name.ShouldEqual(TableName4);
select.From.Queries[0].Top.ShouldEqual(5);
select.From.Queries[1].From.Type.ShouldEqual(Data.DataType.Query);
select.From.Queries[1].From.HasQueries.ShouldEqual(true);
select.From.Queries[1].From.Queries.Count.ShouldEqual(2);
select.From.Queries[1].From.Queries[0].From.Table.ShouldNotBeNull();
select.From.Queries[1].From.Queries[0].From.Type.ShouldEqual(Data.DataType.Table);
select.From.Queries[1].From.Queries[0].From.Table.Name.ShouldEqual(TableName3);
select.From.Queries[1].From.Queries[0].Where.Type.ShouldEqual(Operator.OperatorType.Equal);
select.From.Queries[1].From.Queries[1].HasConditions.ShouldEqual(false);
select.From.Queries[1].From.Queries[1].From.Table.ShouldNotBeNull();
select.From.Queries[1].From.Queries[1].From.Type.ShouldEqual(Data.DataType.Table);
select.From.Queries[1].From.Queries[1].From.Table.Name.ShouldEqual(TableName2);
select.From.Queries[2].From.Table.ShouldNotBeNull();
select.From.Queries[2].From.Type.ShouldEqual(Data.DataType.Table);
select.From.Queries[2].From.Table.Name.ShouldEqual(TableName1);
select.From.Queries[2].Where.Type.ShouldEqual(Operator.OperatorType.Equal);
}
public void Insert_Into_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))).CopyTo(MockQueryable <Entity> .Create(TableName4));
var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name);
var statement = SchemaWriter.CreateSharedColumnsStatement(select.CopyTo.Query, select.CopyTo.Into);
statement.Parameters.Count().ShouldEqual(0);
statement.Text.ShouldEqual("SELECT [SC].[name], [SC].[system_type_id], CAST(CASE WHEN [SC].[system_type_id] IN (239, 231, 99) THEN [SC].[max_length] / 2 ELSE [SC].[max_length] END AS smallint) AS [max_length], [SC].[is_nullable], [SC].[is_identity], " +
"CAST((CASE OBJECT_DEFINITION([SC].[default_object_id]) WHEN '(GETDATE())' THEN 1 WHEN '(NEWID())' THEN 1 WHEN '(NEWSEQUENTIALID())' THEN 1 ELSE 0 END) AS bit) AS [is_auto_generated], CASE OBJECT_DEFINITION([SC].[default_object_id]) WHEN '(GETDATE())' " +
"THEN NULL WHEN '(NEWID())' THEN NULL WHEN '(NEWSEQUENTIALID())' THEN NULL ELSE REPLACE(REPLACE(OBJECT_DEFINITION([SC].[default_object_id]), '(', ''), ')', '') END AS [default_value], ISNULL([SI].[is_primary_key], 0) AS [is_primary_key], CAST((CASE [SI].[type] " +
"WHEN 1 THEN 1 ELSE 0 END) AS bit) AS [is_primary_key_clustered], [SC].[precision], [SC].[scale], [SCC].[definition] AS [computation], [SCC].[is_persisted] AS [persisted_computation], CAST(CASE WHEN [SC].[system_type_id] < (SELECT MAX([system_type_id]) FROM [sys].[columns] " +
"WHERE [name] = [__SubQuery__].[name] AND [system_type_id] IN (175, 167, 35, 239, 231, 99) AND [object_id] IN (OBJECT_ID(N'XLIST_1'), OBJECT_ID(N'XLIST_2'), OBJECT_ID(N'XLIST_3'), OBJECT_ID(N'XLIST_4'))) THEN 1 ELSE 0 END AS bit) AS [is_narrowing] FROM ( ( ( [sys].[columns] [SC] " +
"LEFT JOIN [sys].[index_columns] [SIC] ON [SC].[column_id] = [SIC].[column_id] AND [SC].[object_id] = [SIC].[object_id] ) LEFT JOIN [sys].[indexes] [SI] ON [SI].[index_id] = [SIC].[index_id] AND [SI].[object_id] = [SIC].[object_id] ) LEFT JOIN [sys].[computed_columns] [SCC] ON " +
"[SC].[column_id] = [SCC].[column_id] AND [SC].[object_id] = [SCC].[object_id] ) JOIN " +
"(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') 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_4')) [__SubQuery__] ON [__SubQuery__].[name] = [SC].[name] AND [SC].[object_id] = OBJECT_ID(N'XLIST_4')");
}
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));
}
public async Task GetMessages_ReturnUserMessages(bool hasSent, bool hasReceived,
int expectedMessageCount)
{
var userId = "13";
var messageList = new List <Message>
{
new Message {
Id = 3, SenderId = string.Empty, RecipientId = string.Empty
}
};
if (hasSent)
{
messageList.Add(
new Message {
Id = 1, SenderId = userId, RecipientId = string.Empty
});
}
if (hasReceived)
{
messageList.Add(
new Message {
Id = 2, SenderId = string.Empty, RecipientId = userId
});
}
var queryable = new MockQueryable <Message>(messageList.AsQueryable());
_messageRepo.TableNoTracking.Returns(queryable);
var messageService = new MessageService(_mapper, _messageRepo);
var response = await messageService.GetMessages(userId);
Assert.Equal(expectedMessageCount, response.Count);
}
public void Distinct_Test()
{
var query = MockQueryable <Entity> .Create(TableName1);
query.Distinct(x => x.Name.Substring(0, 5)).Distinct(x => x.Age);
var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select;
select.ShouldNotBeNull();
select.Distinct.ShouldNotBeNull();
select.HasDistinct.ShouldEqual(true);
select.Distinct.Count.ShouldEqual(2);
select.Distinct[0].Projection.Type.ShouldEqual(Projection.ProjectionType.Function);
select.Distinct[0].Projection.Function.ShouldNotBeNull();
select.Distinct[0].Projection.Function.Type.ShouldEqual(Function.FunctionType.SubstringFixed);
select.Distinct[0].Projection.Function.SubstringFixed.Text.ShouldNotBeNull();
select.Distinct[0].Projection.Function.SubstringFixed.Text.Type.ShouldEqual(Projection.ProjectionType.Field);
select.Distinct[0].Projection.Function.SubstringFixed.Text.Field.ShouldNotBeNull();
select.Distinct[0].Projection.Function.SubstringFixed.Text.Field.Name.ShouldEqual("Name");
select.Distinct[0].Projection.Function.SubstringFixed.Start.ShouldNotBeNull();
select.Distinct[0].Projection.Function.SubstringFixed.Start.Type.ShouldEqual(Projection.ProjectionType.Constant);
select.Distinct[0].Projection.Function.SubstringFixed.Start.Constant.ShouldNotBeNull();
select.Distinct[0].Projection.Function.SubstringFixed.Start.Constant.Value.ShouldEqual(0);
select.Distinct[0].Projection.Function.SubstringFixed.Length.ShouldNotBeNull();
select.Distinct[0].Projection.Function.SubstringFixed.Length.Type.ShouldEqual(Projection.ProjectionType.Constant);
select.Distinct[0].Projection.Function.SubstringFixed.Length.Constant.ShouldNotBeNull();
select.Distinct[0].Projection.Function.SubstringFixed.Length.Constant.Value.ShouldEqual(5);
select.Distinct[1].ShouldNotBeNull();
select.Distinct[1].Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.Distinct[1].Projection.Field.ShouldNotBeNull();
select.Distinct[1].Projection.Field.Name.ShouldEqual("Age");
}
public void Except_Test()
{
var query = MockQueryable <Entity> .Create(TableName1);
query.Where(x => x.Age == 33).Except(MockQueryable <Entity> .Create(TableName2).
Except(MockQueryable <Entity> .Create(TableName3).Where(x => x.Active), x => x.Id), x => x.Name, x => x.Length).
Except(MockQueryable <Entity> .Create(TableName4).Take(5), x => x.Age);
var select = QueryVisitor <Entity> .CreateModel(query.Expression, x => ((MockQueryable <Entity>)x).Name).Select;
select.ShouldNotBeNull();
select.From.Type.ShouldEqual(Data.DataType.Table);
select.From.Table.Name.ShouldEqual(TableName1);
select.HasCompliments.ShouldEqual(true);
select.SetOperatons.Count.ShouldEqual(2);
select.SetOperatons[0].Type.ShouldEqual(SetOperation.OperationType.Compliment);
select.SetOperatons[0].Select.From.Table.ShouldNotBeNull();
select.SetOperatons[0].Select.From.Table.Name.ShouldEqual(TableName4);
select.SetOperatons[0].Select.HasWhere.ShouldEqual(true);
select.SetOperatons[0].Select.Where.LeftOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[0].Select.Where.LeftOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.SetOperatons[0].Select.Where.LeftOperand.Projection.Field.Name.ShouldEqual("Age");
select.SetOperatons[0].Select.Where.LeftOperand.Projection.Field.TableAlias.ShouldEqual(null);
select.SetOperatons[0].Select.Where.Type.ShouldEqual(Operator.OperatorType.Equal);
select.SetOperatons[0].Select.Where.RightOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[0].Select.Where.RightOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.SetOperatons[0].Select.Where.RightOperand.Projection.Field.Name.ShouldEqual("Age");
select.SetOperatons[0].Select.Where.RightOperand.Projection.Field.TableAlias.ShouldEqual(select.From.Alias);
select.SetOperatons[1].Type.ShouldEqual(SetOperation.OperationType.Compliment);
select.SetOperatons[1].Select.From.Table.ShouldNotBeNull();
select.SetOperatons[1].Select.From.Table.Name.ShouldEqual(TableName2);
select.SetOperatons[1].Select.HasWhere.ShouldEqual(true);
select.SetOperatons[1].Select.Where.LeftOperand.Type.ShouldEqual(Operand.OperandType.Operator);
select.SetOperatons[1].Select.Where.LeftOperand.Operator.LeftOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[1].Select.Where.LeftOperand.Operator.LeftOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.SetOperatons[1].Select.Where.LeftOperand.Operator.LeftOperand.Projection.Field.Name.ShouldEqual("Name");
select.SetOperatons[1].Select.Where.LeftOperand.Operator.LeftOperand.Projection.Field.TableAlias.ShouldEqual(null);
select.SetOperatons[1].Select.Where.LeftOperand.Operator.Type.ShouldEqual(Operator.OperatorType.Equal);
select.SetOperatons[1].Select.Where.LeftOperand.Operator.RightOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[1].Select.Where.LeftOperand.Operator.RightOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.SetOperatons[1].Select.Where.LeftOperand.Operator.RightOperand.Projection.Field.Name.ShouldEqual("Name");
select.SetOperatons[1].Select.Where.LeftOperand.Operator.RightOperand.Projection.Field.TableAlias.ShouldEqual(select.From.Alias);
select.SetOperatons[1].Select.Where.Type.ShouldEqual(Operator.OperatorType.And);
select.SetOperatons[1].Select.Where.RightOperand.Type.ShouldEqual(Operand.OperandType.Operator);
select.SetOperatons[1].Select.Where.RightOperand.Operator.LeftOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[1].Select.Where.RightOperand.Operator.LeftOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.SetOperatons[1].Select.Where.RightOperand.Operator.LeftOperand.Projection.Field.Name.ShouldEqual("Length");
select.SetOperatons[1].Select.Where.RightOperand.Operator.LeftOperand.Projection.Field.TableAlias.ShouldEqual(null);
select.SetOperatons[1].Select.Where.RightOperand.Operator.Type.ShouldEqual(Operator.OperatorType.Equal);
select.SetOperatons[1].Select.Where.RightOperand.Operator.RightOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[1].Select.Where.RightOperand.Operator.RightOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.SetOperatons[1].Select.Where.RightOperand.Operator.RightOperand.Projection.Field.Name.ShouldEqual("Length");
select.SetOperatons[1].Select.Where.RightOperand.Operator.RightOperand.Projection.Field.TableAlias.ShouldEqual(select.From.Alias);
select.SetOperatons[1].Select.HasCompliments.ShouldEqual(true);
select.SetOperatons[1].Select.SetOperatons.Count.ShouldEqual(1);
select.SetOperatons[1].Select.SetOperatons[0].Type.ShouldEqual(SetOperation.OperationType.Compliment);
select.SetOperatons[1].Select.SetOperatons[0].Select.From.Table.ShouldNotBeNull();
select.SetOperatons[1].Select.SetOperatons[0].Select.From.Table.Name.ShouldEqual(TableName3);
select.SetOperatons[1].Select.SetOperatons[0].Select.HasWhere.ShouldEqual(true);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.LeftOperand.Type.ShouldEqual(Operand.OperandType.Operator);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.LeftOperand.Operator.LeftOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.LeftOperand.Operator.LeftOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.LeftOperand.Operator.LeftOperand.Projection.Field.Name.ShouldEqual("Active");
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.LeftOperand.Operator.LeftOperand.Projection.Field.TableAlias.ShouldEqual(select.SetOperatons[1].Select.SetOperatons[0].Select.From.Alias);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.LeftOperand.Operator.Type.ShouldEqual(Operator.OperatorType.Equal);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.LeftOperand.Operator.RightOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.LeftOperand.Operator.RightOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Constant);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.LeftOperand.Operator.RightOperand.Projection.Constant.Value.ShouldEqual(true);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.Type.ShouldEqual(Operator.OperatorType.And);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Type.ShouldEqual(Operand.OperandType.Operator);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Operator.LeftOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Operator.LeftOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Operator.LeftOperand.Projection.Field.Name.ShouldEqual("Id");
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Operator.LeftOperand.Projection.Field.TableAlias.ShouldBeNull();
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Operator.Type.ShouldEqual(Operator.OperatorType.Equal);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Operator.RightOperand.Type.ShouldEqual(Operand.OperandType.Projection);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Operator.RightOperand.Projection.Type.ShouldEqual(Projection.ProjectionType.Field);
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Operator.RightOperand.Projection.Field.Name.ShouldEqual("Id");
select.SetOperatons[1].Select.SetOperatons[0].Select.Where.RightOperand.Operator.RightOperand.Projection.Field.TableAlias.ShouldEqual(select.SetOperatons[1].Select.From.Alias);
}
public void should_render_sync_exclude_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.Exclude, x => x.Id, x => x.Created);
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;
sync.Target.Projection.ShouldBeNull();
sync.Source.Projection.ShouldBeNull();
sync.Target.Projection = new List <SelectProjection>
{
new SelectProjection {
Projection = new Projection {
Type = Projection.ProjectionType.Field, Field = new Field {
Name = "Id", TableAlias = sync.Target.From.Alias
}
}
},
new SelectProjection {
Projection = new Projection {
Type = Projection.ProjectionType.Field, Field = new Field {
Name = "Created", TableAlias = sync.Target.From.Alias
}
}
},
new SelectProjection {
Projection = new Projection {
Type = Projection.ProjectionType.Field, Field = new Field {
Name = "Name", TableAlias = sync.Target.From.Alias
}
}
},
new SelectProjection {
Projection = new Projection {
Type = Projection.ProjectionType.Field, Field = new Field {
Name = "Flag", TableAlias = sync.Target.From.Alias
}
}
}
};
sync.Source.Projection = new List <SelectProjection>
{
new SelectProjection {
Projection = new Projection {
Type = Projection.ProjectionType.Field, Field = new Field {
Name = "Id", TableAlias = sync.Source.From.Alias
}
}
},
new SelectProjection {
Projection = new Projection {
Type = Projection.ProjectionType.Field, Field = new Field {
Name = "Created", TableAlias = sync.Source.From.Alias
}
}
},
new SelectProjection {
Projection = new Projection {
Type = Projection.ProjectionType.Field, Field = new Field {
Name = "Name", TableAlias = sync.Source.From.Alias
}
}
},
new SelectProjection {
Projection = new Projection {
Type = Projection.ProjectionType.Field, Field = new Field {
Name = "Flag", TableAlias = sync.Source.From.Alias
}
}
}
};
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 Setup()
{
this.queryable = new MockQueryable<Contact>();
this.translator = new ContentQueryTranslator (this.queryable.Provider, new ContactTableFinder { UseRawContacts = false });
}
