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