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