public void SQLinqIf_004() { var query = (from d in new SQLinq<Person>() where d.Age >= 18 select d); var @if = new SQLinqIf(SQLinqIfOperator.Not, "1 = 1"); @if.Then = query; var actual = @if.ToSQL(); var actualQuery = actual.ToQuery(); var expectedQuery = @"IF (NOT(1 = 1)) BEGIN SELECT [ID], [FirstName], [LastName], [Age], [Is_Employed] AS [IsEmployed], [ParentID], [Column With Spaces] AS [ColumnWithSpaces] FROM [Person] WHERE [Age] >= @sqlinq_1 END "; Assert.AreEqual(expectedQuery, actualQuery); }
public void SQLinqIf_005() { var @if = new SQLinqIf(SQLinqIfOperator.Exists, (from d in new SQLinq<Person>() where d.Age >= 19 select d)); @if.Then = (from d in new SQLinq<Person>() where d.Age >= 18 select d); var result = @if.ToSQL(); Assert.AreEqual(2, result.Parameters.Count); Assert.AreEqual(19, result.Parameters["@sqlinq_1"]); Assert.AreEqual(18, result.Parameters["@sqlinq_2"]); }
public void SQLinqIf_012() { var personId = Guid.NewGuid(); var @if = new SQLinqIf(SQLinqIfOperator.Exists, from p in new SQLinq<Person>() where p.ID == personId select p.ID); @if.Then = new SQLinqUpdate<Person>(new Person()); @if.Else = new SQLinqInsert<Person>(new Person()); var actual = @if.ToSQL(); var actualQuery = actual.ToQuery(); var expectedQuery = @"IF (EXISTS(SELECT [ID] FROM [Person] WHERE [ID] = @sqlinq_1)) BEGIN UPDATE [Person] SET [ID] = @sqlinq_2, [FirstName] = @sqlinq_3, [LastName] = @sqlinq_4, [Age] = @sqlinq_5, [Is_Employed] = @sqlinq_6, [ParentID] = @sqlinq_7, [Column With Spaces] = @sqlinq_8 END ELSE BEGIN INSERT [Person] ([ID], [FirstName], [LastName], [Age], [Is_Employed], [ParentID], [Column With Spaces]) VALUES (@sqlinq_9, @sqlinq_10, @sqlinq_11, @sqlinq_12, @sqlinq_13, @sqlinq_14, @sqlinq_15) END "; Assert.AreEqual(expectedQuery, actualQuery); Assert.AreEqual(15, actual.Parameters.Count); Assert.AreEqual(personId, actual.Parameters["@sqlinq_1"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_2"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_3"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_4"]); Assert.AreEqual(0, actual.Parameters["@sqlinq_5"]); Assert.AreEqual(false, actual.Parameters["@sqlinq_6"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_7"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_8"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_9"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_10"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_11"]); Assert.AreEqual(0, actual.Parameters["@sqlinq_12"]); Assert.AreEqual(false, actual.Parameters["@sqlinq_13"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_14"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_15"]); }
public void SQLinqIf_009() { var query = (from d in new SQLinq<Person>() where d.Age >= 18 select d); var @if = new SQLinqIf(SQLinqIfOperator.Exists, query); @if.Then = new DynamicSQLinq("Car") .Where("Make = @0", "Ford") .Where("Color = @0 OR Color = @1", 0, 1); var actual = @if.ToSQL(); var actualQuery = actual.ToQuery(); var expectedQuery = @"IF (EXISTS(SELECT [ID], [FirstName], [LastName], [Age], [Is_Employed] AS [IsEmployed], [ParentID], [Column With Spaces] AS [ColumnWithSpaces] FROM [Person] WHERE [Age] >= @sqlinq_1)) BEGIN SELECT * FROM Car WHERE (Make = @sqlinq_2) AND (Color = @sqlinq_3 OR Color = @sqlinq_4) END "; Assert.AreEqual(expectedQuery, actualQuery); Assert.AreEqual(18, actual.Parameters["@sqlinq_1"]); Assert.AreEqual("Ford", actual.Parameters["@sqlinq_2"]); Assert.AreEqual(0, actual.Parameters["@sqlinq_3"]); Assert.AreEqual(1, actual.Parameters["@sqlinq_4"]); }
public void ToSQL_006() { var guid = Guid.NewGuid(); var target = new SQLinqCollection(); target.Add(new SQLinq<ICar>().Where(d => d.WheelDiameter == 21)); target.Add(new SQLinq<ICar>().Where(d => d.WheelDiameter == 14)); target.Add(new SQLinqUpdate<Person>(new Person()).Where(d => d.ID == guid)); target.Add(new SQLinqInsert<Person>(new Person())); var @if = new SQLinqIf(SQLinqIfOperator.Exists, from d in new SQLinq<Person>() where d.ID == guid select 1); @if.Then = from d in new SQLinq<Person>() where d.ID == guid select d; target.Add(@if); target.Add(((Func<SQLinqCollection>)(() => { var target2 = new SQLinqCollection(); target2.Add(new SQLinq<ICar>().Where(d => d.WheelDiameter == 21)); target2.Add(new SQLinq<ICar>().Where(d => d.WheelDiameter == 14)); target2.Add(new SQLinqUpdate<Person>(new Person()).Where(d => d.ID == guid)); target2.Add(new SQLinqInsert<Person>(new Person())); var @if2 = new SQLinqIf(SQLinqIfOperator.Exists, from d in new SQLinq<Person>() where d.ID == guid select 1); @if2.Then = from d in new SQLinq<Person>() where d.ID == guid select d; target2.Add(@if2); return target2; }))()); var actual = target.ToSQL(); var actualQuery = actual.ToQuery(); var expectedQuery = @"SELECT * FROM [ICar] WHERE [WheelDiameter] = @sqlinq_1 SELECT * FROM [ICar] WHERE [WheelDiameter] = @sqlinq_2 UPDATE [Person] SET [ID] = @sqlinq_3, [FirstName] = @sqlinq_4, [LastName] = @sqlinq_5, [Age] = @sqlinq_6, [Is_Employed] = @sqlinq_7, [ParentID] = @sqlinq_8, [Column With Spaces] = @sqlinq_9 WHERE [ID] = @sqlinq_10 INSERT [Person] ([ID], [FirstName], [LastName], [Age], [Is_Employed], [ParentID], [Column With Spaces]) VALUES (@sqlinq_11, @sqlinq_12, @sqlinq_13, @sqlinq_14, @sqlinq_15, @sqlinq_16, @sqlinq_17);SELECT SCOPE_IDENTITY() IF (EXISTS(SELECT @sqlinq_18 FROM [Person] WHERE [ID] = @sqlinq_19)) BEGIN SELECT [ID], [FirstName], [LastName], [Age], [Is_Employed] AS [IsEmployed], [ParentID], [Column With Spaces] AS [ColumnWithSpaces] FROM [Person] WHERE [ID] = @sqlinq_20 END SELECT * FROM [ICar] WHERE [WheelDiameter] = @sqlinq_21 SELECT * FROM [ICar] WHERE [WheelDiameter] = @sqlinq_22 UPDATE [Person] SET [ID] = @sqlinq_23, [FirstName] = @sqlinq_24, [LastName] = @sqlinq_25, [Age] = @sqlinq_26, [Is_Employed] = @sqlinq_27, [ParentID] = @sqlinq_28, [Column With Spaces] = @sqlinq_29 WHERE [ID] = @sqlinq_30 INSERT [Person] ([ID], [FirstName], [LastName], [Age], [Is_Employed], [ParentID], [Column With Spaces]) VALUES (@sqlinq_31, @sqlinq_32, @sqlinq_33, @sqlinq_34, @sqlinq_35, @sqlinq_36, @sqlinq_37);SELECT SCOPE_IDENTITY() IF (EXISTS(SELECT @sqlinq_38 FROM [Person] WHERE [ID] = @sqlinq_39)) BEGIN SELECT [ID], [FirstName], [LastName], [Age], [Is_Employed] AS [IsEmployed], [ParentID], [Column With Spaces] AS [ColumnWithSpaces] FROM [Person] WHERE [ID] = @sqlinq_40 END "; Assert.AreEqual(expectedQuery, actualQuery); Assert.AreEqual(40, actual.Parameters.Count); Assert.AreEqual(21, actual.Parameters["@sqlinq_1"]); Assert.AreEqual(14, actual.Parameters["@sqlinq_2"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_3"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_4"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_5"]); Assert.AreEqual(0, actual.Parameters["@sqlinq_6"]); Assert.AreEqual(false, actual.Parameters["@sqlinq_7"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_8"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_9"]); Assert.AreEqual(guid, actual.Parameters["@sqlinq_10"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_11"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_12"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_13"]); Assert.AreEqual(0, actual.Parameters["@sqlinq_14"]); Assert.AreEqual(false, actual.Parameters["@sqlinq_15"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_16"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_17"]); Assert.AreEqual(1, actual.Parameters["@sqlinq_18"]); Assert.AreEqual(guid, actual.Parameters["@sqlinq_19"]); Assert.AreEqual(guid, actual.Parameters["@sqlinq_20"]); Assert.AreEqual(21, actual.Parameters["@sqlinq_21"]); Assert.AreEqual(14, actual.Parameters["@sqlinq_22"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_23"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_24"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_25"]); Assert.AreEqual(0, actual.Parameters["@sqlinq_26"]); Assert.AreEqual(false, actual.Parameters["@sqlinq_27"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_28"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_29"]); Assert.AreEqual(guid, actual.Parameters["@sqlinq_30"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_31"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_32"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_33"]); Assert.AreEqual(0, actual.Parameters["@sqlinq_34"]); Assert.AreEqual(false, actual.Parameters["@sqlinq_35"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_36"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_37"]); Assert.AreEqual(1, actual.Parameters["@sqlinq_38"]); Assert.AreEqual(guid, actual.Parameters["@sqlinq_39"]); Assert.AreEqual(guid, actual.Parameters["@sqlinq_40"]); }
public void SQLinqIf_011() { var personId = Guid.NewGuid(); var @if = new SQLinqIf(SQLinqIfOperator.Exists, from p in new SQLinq<Person>() where p.ID == personId select p.ID); @if.Then = new SQLinqInsert<Person>(new Person()); var actual = @if.ToSQL(); var actualQuery = actual.ToQuery(); var expectedQuery = @"IF (EXISTS(SELECT [ID] FROM [Person] WHERE [ID] = @sqlinq_1)) BEGIN INSERT [Person] ([ID], [FirstName], [LastName], [Age], [Is_Employed], [ParentID], [Column With Spaces]) VALUES (@sqlinq_2, @sqlinq_3, @sqlinq_4, @sqlinq_5, @sqlinq_6, @sqlinq_7, @sqlinq_8);SELECT SCOPE_IDENTITY() END "; Assert.AreEqual(expectedQuery, actualQuery); Assert.AreEqual(8, actual.Parameters.Count); Assert.AreEqual(personId, actual.Parameters["@sqlinq_1"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_2"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_3"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_4"]); Assert.AreEqual(0, actual.Parameters["@sqlinq_5"]); Assert.AreEqual(false, actual.Parameters["@sqlinq_6"]); Assert.AreEqual(Guid.Empty, actual.Parameters["@sqlinq_7"]); Assert.AreEqual(null, actual.Parameters["@sqlinq_8"]); }