public void TestSelect_9()
{
//结果
var result = new String();
result.Append("Select [a].[b] As [c],[a1],[b2],");
result.Append("d=(select a from t),");
result.Append("[a].[Email],[a].[IntValue],");
result.Append("e=(select b from t),");
result.Append("[b].[Description],[b].[Display],");
result.AppendLine("f=(select c from t) ");
result.Append("From (select * from t1) as o");
//执行
_builder.Select("a.b as c,a1,b2")
.AppendSelect("d=(select a from t)")
.Select <Sample>(t => new object[] { t.Email, t.IntValue }, "a")
.AppendSelect("e=(select b from t)")
.Select <Sample2>(t => new object[] { t.Description, t.Display }, "b")
.AppendSelect("f=(select c from t)")
.From("b")
.AppendFrom("(select * from t1) ")
.AppendFrom("as o");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_1()
{
//结果
var result = new String();
result.AppendLine("Select [a3].[a],[a1].[b1],[a2].[b2] ");
result.AppendLine("From [b] As [a2] ");
result.Append("Join [c] As [a3] On [a2].[d]=[a3].[e]");
//执行
_builder.Select("a,a1.b1,[a2].[b2]", "a3")
.From("b", "a2")
.Join("c", "a3").On("a2.d", "a3.[e]");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_34()
{
//结果
var result = new String();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Group By [a].[B],[c].[D]");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.GroupBy("a.B,c.[D]");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_30()
{
//结果
var result = new String();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Where [a].[Email] Is Not Null And [a].[Email]<>''");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.IsNotEmpty <Sample>(t => t.Email);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_27()
{
//结果
var result = new String();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Where ([a].[Email] Is Null Or [a].[Email]='')");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.IsEmpty("a.Email");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestWhere_19()
{
//结果
var result = new String();
result.AppendLine("Select * ");
result.AppendLine("From [Sample] As [k] ");
result.Append("Where [k].[Email]=@_p__0 And [k].[StringValue] Like @_p__1");
//执行
_builder.From <Sample>("k").Where <Sample>(t => t.Email == "a" && t.StringValue.Contains("b"));
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Equal(2, _builder.GetParams().Count);
Assert.Equal("a", _builder.GetParams()["@_p__0"]);
Assert.Equal("%b%", _builder.GetParams()["@_p__1"]);
}
public void TestWhere_21()
{
//结果
var result = new String();
result.AppendLine("Select * ");
result.AppendLine("From [Sample] As [k] ");
result.Append("Where ([o].[Email]=@_p__0 And [o].[StringValue] Like @_p__1 Or [o].[IntValue]=@_p__2) ");
result.Append("And ([p].[Email]=@_p__3 Or [p].[IntValue]=@_p__4)");
//执行
_builder.From <Sample>("k")
.Where <Sample>(t => t.Email == "a" && t.StringValue.Contains("b") || t.IntValue == 1, "o")
.Where <Sample>(t => t.Email == "c" || t.IntValue == 2, "p");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_2()
{
//结果
var result = new String();
result.AppendLine("Select [a].[Email],[a].[BoolValue],[b].[Description],[b].[IntValue] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Left Join [Sample2] As [b] On [a].[Email]=[b].[StringValue] And [a].[IntValue]<>[b].[IntValue]");
//执行
_builder.Select <Sample>(t => new object[] { t.Email, t.BoolValue })
.Select <Sample2>(t => new object[] { t.Description, t.IntValue })
.From <Sample>("a")
.LeftJoin <Sample2>("b").On <Sample, Sample2>((l, r) => l.Email == r.StringValue && l.IntValue != r.IntValue);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_22()
{
//结果
var result = new String();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Where [a].[Email] Like @_p__0");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.Ends <Sample>(t => t.Email, "abc");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Single(_builder.GetParams());
Assert.Equal("%abc", _builder.GetParams()["@_p__0"]);
}
public void Test_6()
{
//结果
var result = new String();
result.AppendLine("Select [a].[Email],[a].[BoolValue] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Where [a].[Email]=@_p__0");
//执行
_builder.Select <Sample>(t => new object[] { t.Email, t.BoolValue })
.From <Sample>("a")
.Equal <Sample>(t => t.Email, "abc");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Single(_builder.GetParams());
Assert.Equal("abc", _builder.GetParams()["@_p__0"]);
}
public void Test_3()
{
//结果
var result = new String();
result.AppendLine("Select [a3].[a],[a1].[b1],[a2].[b2] ");
result.AppendLine("From [b] As [a2] ");
result.AppendLine("Join [c] As [a3] On [a2].[d]=[a3].[e] ");
result.Append("Where [b].[Name]=@_p__0");
//执行
_builder.Select("a,a1.b1,[a2].[b2]", "a3")
.From("b", "a2")
.Join("c", "a3").On("a2.d", "a3.[e]")
.Where("b.Name", "abc");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Single(_builder.GetParams());
Assert.Equal("abc", _builder.GetParams()["@_p__0"]);
}
public void TestSelect_7()
{
//结果
var result = new String();
result.AppendLine("Select [a].*,[b].* ");
result.Append("From [c]");
//执行
_builder.Select("a.*,b.*").From("c");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestSelect_5()
{
//结果
var result = new String();
result.AppendLine("Select [Email],[IntValue] ");
result.Append("From [b]");
//执行
_builder.Select <Sample>(t => new object[] { t.Email, t.IntValue }).From("b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestSelect_4()
{
//结果
var result = new String();
result.AppendLine("Select [t].[a],[b] ");
result.Append("From [c] As [d]");
//执行
_builder.Select("t.a,[b]").From("c", "d");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestSelect_6()
{
//结果
var result = new String();
result.AppendLine("Select [j].[a],[j].[b],[k].[c],[k].[d] ");
result.Append("From [e]");
//执行
_builder.Select("a,b", "j").Select("c,d", "k").From("e");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestFrom_5()
{
//结果
var result = new String();
result.AppendLine("Select * ");
result.Append("From [b].[Sample] As [a]");
//执行
_builder.From <Sample>("a", "b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestSelect_2()
{
//结果
var result = new String();
result.AppendLine("Select [a] ");
result.Append("From [b] As [c]");
//执行
_builder.Select("a").From("b", "c");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestFrom_4()
{
//结果
var result = new String();
result.AppendLine("Select * ");
result.Append("From [a].[b] As [t]");
//执行
_builder.From("a.b as t");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestFrom_3()
{
//结果
var result = new String();
result.AppendLine("Select * ");
result.Append("From [c].[a] As [b]");
//执行
_builder.From("c.a", "b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestOr_4()
{
//结果
var result = new String();
result.AppendLine("Select * ");
result.AppendLine("From [Test] As [t] ");
result.Append("Where (((([t].[a]=@_p__0 And [t].[b]=@_p__1 ");
result.Append("Or [t].[c]=@_p_1_0 And [t].[d]=@_p_1_1) ");
result.Append("Or [t].[e]=@_p_2_0 And [t].[f]=@_p_2_1) ");
result.Append("Or [t].[c1]=@_p_11_0 And [t].[d1]=@_p_11_1) ");
result.Append("Or [t].[e1]=@_p_21_0 And [t].[f1]=@_p_21_1)");
//操作
var builder1 = _builder.New().Where("c", 3).Where("d", 4);
var builder11 = builder1.New().Where("c1", 31).Where("d1", 41);
var builder2 = _builder.New().Where("e", 5).Where("f", 6);
var builder21 = builder2.New().Where("e1", 51).Where("f1", 61);
_builder.From("Test", "").Where("a", 1).Where("b", 2).Or(builder1).Or(builder2).Or(builder11).Or(builder21);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_33()
{
//结果
var result = new String();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Where [a].[Email] In (@_p__0,@_p__1)");
//执行
var list = new List <string> {
"a", "b"
};
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.In <Sample>(t => t.Email, list);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Equal(2, _builder.GetParams().Count);
Assert.Equal("a", _builder.GetParams()["@_p__0"]);
Assert.Equal("b", _builder.GetParams()["@_p__1"]);
}
public void TestSelect_10()
{
//结果
var result = new String();
result.AppendLine("Select [Sample_Email],[Sample_IntValue] ");
result.Append("From [b]");
//执行
_builder = new SqlServerBuilder(new TestEntityMatedata());
_builder.Select <Sample>(t => new object[] { t.Email, t.IntValue }).From("b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void TestSelect_8()
{
//结果
var result = new String();
result.AppendLine("Select [a].[Email],[a].[IntValue],[b].[Description],[b].[Display] ");
result.Append("From [b]");
//执行
_builder.Select <Sample>(t => new object[] { t.Email, t.IntValue }, "a")
.Select <Sample2>(t => new object[] { t.Description, t.Display }, "b")
.From("b");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
