public void Test_RightJoin_6()
{
//结果
var result = new Str();
result.AppendLine("Select [a] ");
result.AppendLine("From [b] ");
result.Append("Right Join c");
//执行
_builder.Select("a")
.From("b")
.AppendRightJoin("d", false)
.AppendRightJoin("c", true);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_AppendGroupBy_2()
{
//结果
var result = new Str();
result.AppendLine("Select [a].[Email] ");
result.AppendLine("From [Sample] As [a] ");
result.Append("Group By b");
//执行
_builder.Select <Sample>(t => t.Email)
.From <Sample>("a")
.AppendGroupBy("c", false)
.AppendGroupBy("b", true);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Between_9()
{
//结果
var result = new Str();
result.Append("Where [a].[B]>=@_p_0 And [a].[B]<=@_p_1");
//执行
var min = DateTime.Parse("2000-1-1 10:10:10");
var max = DateTime.Parse("2000-1-2 10:10:10");
_clause.Between("a.B", min, max, true, null);
//验证
Assert.Equal(min, _parameterManager.GetParams()["@_p_0"]);
Assert.Equal(max, _parameterManager.GetParams()["@_p_1"]);
Assert.Equal(result.ToString(), GetSql());
}
public void Test_LeftJoin_7()
{
//结果
var result = new Str();
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_With()
{
//结果
var result = new Str();
result.AppendLine("With Recursive `Test` ");
result.AppendLine("As (Select `a`,`b` ");
result.AppendLine("From `Test2`)");
result.AppendLine("Select `a`,`b` ");
result.Append("From `Test`");
//执行
var builder2 = _builder.New().Select("a,b").From("Test2");
_builder.Select("a,b").From("Test").With("Test", builder2);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_IgnoreFilter_2()
{
//结果
var result = new Str();
result.AppendLine("Select [s].[StringValue] ");
result.AppendLine("From [Sample5] As [s] ");
result.Append("Join [Sample6] As [s2] On [s].[IntValue]=[s2].[IntValue]");
//执行
_builder.Select <Sample5>(t => t.StringValue)
.From <Sample5>("s")
.Join <Sample6>("s2").On <Sample5, Sample6>((l, r) => l.IntValue == r.IntValue)
.IgnoreFilter <IsDeletedFilter>();
//验证
Output.WriteLine(_builder.ToSql());
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_On_4()
{
//结果
var result = new Str();
result.AppendLine("Select [a],[b] ");
result.AppendLine("From [Sample] As [s] ");
result.Append("Left Join [Sample2] As [s2] On [s].[IntValue]=[s2].[IntValue] And [s].[StringValue]=@_p_0");
//执行
_builder.Select("a,b")
.From <Sample>("s")
.LeftJoin <Sample2>("s2").On <Sample, Sample2>((l, r) => l.IntValue == r.IntValue && l.StringValue == "a");
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Output.WriteLine(_builder.ToSql());
Assert.Equal("a", _builder.GetParams()["@_p_0"]);
}
public void TestWhere()
{
//结果
var result = new Str();
result.AppendLine("Select \"a\".\"Email\" ");
result.AppendLine("From \"Sample\" \"a\" ");
result.Append("Where \"a\".\"Email\"<>:p_0");
//执行
_builder.Select <Sample>(t => new object[] { t.Email })
.From <Sample>("a")
.Where <Sample>(t => t.Email, "abc", Operator.NotEqual);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Single(_builder.GetParams());
Assert.Equal("abc", _builder.GetParams()["p_0"]);
}
public void Test_On_15()
{
//结果
var result = new Str();
result.Append("Join [Sample] As [t] ");
result.AppendLine("On [a].[id]=@_p_0 ");
result.Append("Join [Sample2] As [t2] ");
result.Append("On [t2].[IntValue]>[t].[ShortValue]");
//操作
_clause.Join <Sample>("t");
_clause.On("a.id", "b.id");
_clause.Join <Sample2>("t2");
_clause.On <Sample, Sample2>((l, r) => r.IntValue > l.ShortValue);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_With_1()
{
//结果
var result = new Str();
result.AppendLine("With [Test] ");
result.AppendLine("As (Select [a],[b] ");
result.AppendLine("From [Test2])");
result.AppendLine("Select [a],[b] ");
result.Append("From [Test]");
//执行
var builder2 = _builder.New().Select("a,b").From("Test2");
_builder.Select("a,b").From("Test").With("Test", builder2);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_On_7()
{
//结果
var result = new Str();
result.Append("Join [Sample] As [t] ");
result.AppendLine("On [a].[id]=@_p_0 ");
result.Append("Join [Sample2] As [t2] ");
result.Append("On [t].[BoolValue]<[t2].[IntValue]");
//操作
_clause.Join <Sample>("t");
_clause.On("a.id", "b");
_clause.Join <Sample2>("t2");
_clause.On <Sample, Sample2>(t => t.BoolValue, t => t.IntValue, Operator.Less);
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_On_4()
{
//结果
var result = new Str();
result.Append("Join [t] ");
result.AppendLine("On [a].[id]=@_p_0 And [c].[Aid]=@_p_1 ");
result.Append("Join [n] ");
result.Append("On [t].[id]=@_p_2 And [t].[Aid]=@_p_3");
//操作
_clause.Join("t");
_clause.On("a.id", "b");
_clause.On("c.Aid", "d");
_clause.Join("n");
_clause.On("t.id", "e");
_clause.On("t.Aid", "f");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_OrIfNotEmpty_4()
{
//结果
var result = new Str();
result.Append("Where (([Email]=@_p_0 Or ");
result.Append("[Email] In (@_p_1,@_p_2)) Or [Url]=@_p_3) ");
result.Append("And [Url]=@_p_4");
//执行
var list = new List <string> {
"a", "b"
};
_clause.Where <Sample>(t => t.Email == "b");
_clause.OrIfNotEmpty <Sample>(t => list.Contains(t.Email), t => t.Url == "a");
_clause.Where <Sample>(t => t.Url == "c");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_AppendOn_2()
{
//结果
var result = new Str();
result.Append("Join [t] ");
result.AppendLine("On [a].[id]=@_p_0 And a.id=b.id ");
result.Append("Join [v] ");
result.Append("On [v].[id]=@_p_1 And v.id=b.id");
//操作
_clause.Join("t");
_clause.On("a.id", "b");
_clause.AppendOn("a.id=b.id");
_clause.Join("v");
_clause.On("v.id", "c");
_clause.AppendOn("v.id=b.id");
//验证
Assert.Equal(result.ToString(), GetSql());
}
public void Test_On_13()
{
//结果
var result = new Str();
result.Append("Join [Sample] As [t] ");
result.AppendLine("On [a].[id]=@_p_0 ");
result.Append("Join [Sample2] As [t2] ");
result.Append("On [t].[ShortValue]>[t2].[IntValue] And [t].[IntValue]=@_p_1");
//操作
_clause.Join <Sample>("t");
_clause.On("a.id", "b");
_clause.Join <Sample2>("t2");
_clause.On <Sample, Sample2>((l, r) => l.ShortValue > r.IntValue && l.IntValue == 1);
//验证
Assert.Equal(result.ToString(), GetSql());
Assert.Equal("b", _parameterManager.GetParams()["@_p_0"]);
Assert.Equal(1, _parameterManager.GetParams()["@_p_1"]);
}
public void Test_Join_4()
{
//结果
var result = new Str();
result.AppendLine("Select * ");
result.AppendLine("From [Test] ");
result.AppendLine("Join (Select * ");
result.AppendLine("From [Test2] ");
result.AppendLine("Where [Name]=@_p_0) As [t] ");
result.Append("Where [Age]=@_p_1");
//执行
_builder.From("Test").Join(builder => builder.From("Test2").Where("Name", "a"), "t").Where("Age", 1);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Equal(2, _builder.GetParams().Count);
Assert.Equal("a", _builder.GetParams()["@_p_0"]);
Assert.Equal(1, _builder.GetParams()["@_p_1"]);
}
public void Test_Page_2()
{
//结果
var result = new Str();
result.AppendLine("Select * ");
result.AppendLine("From [Test] ");
result.AppendLine("Order By [a] ");
result.Append("Offset @_p_0 Rows Fetch Next @_p_1 Rows Only");
//执行
var pager = new QueryParameter {
Order = "a"
};
_builder.From("Test").Page(pager);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Equal(0, _builder.GetParams()["@_p_0"]);
Assert.Equal(20, _builder.GetParams()["@_p_1"]);
}
public void Test_IgnoreFilter_3()
{
//结果
var result = new Str();
result.AppendLine("Select [s5].[StringValue] ");
result.AppendLine("From [Sample5] As [s5] ");
result.AppendLine("Join [Sample6] As [s6] On [s5].[IntValue]=[s6].[IntValue] ");
result.AppendLine("Left Join [Sample7] As [s7] On [s6].[IntValue]=[s7].[IntValue] ");
result.Append("Right Join [Sample8] As [s8] On [s7].[IntValue]=[s8].[IntValue]");
//执行
_builder.Select <Sample5>(t => t.StringValue)
.From <Sample5>("s5")
.Join <Sample6>("s6").On <Sample5, Sample6>((l, r) => l.IntValue == r.IntValue)
.LeftJoin <Sample7>("s7").On <Sample6, Sample7>((l, r) => l.IntValue == r.IntValue)
.RightJoin <Sample8>("s8").On <Sample7, Sample8>((l, r) => l.IntValue == r.IntValue)
.IgnoreFilter <IsDeletedFilter>();
//验证
Output.WriteLine(_builder.ToSql());
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Select_4()
{
//结果
var result = new Str();
result.Append("Select *,");
result.AppendLine("(Select Count(*) ");
result.AppendLine("From [Test2] ");
result.AppendLine("Where [Name]=@_p_0) As [TestCount] ");
result.AppendLine("From [Test] ");
result.Append("Where [Age]=@_p_1");
//执行
var builder2 = _builder.New().Count().From("Test2").Where("Name", "a");
_builder.Select("*").Select(builder2, "TestCount").From("Test").Where("Age", 1);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
Assert.Equal(2, _builder.GetParams().Count);
Assert.Equal("a", _builder.GetParams()["@_p_0"]);
Assert.Equal(1, _builder.GetParams()["@_p_1"]);
}
public void Test_On_12()
{
//结果
var result = new Str();
result.Append("Join [Sample] As [t] ");
result.AppendLine("On [t].[id]=@_p_0 ");
result.Append("Join [Sample2] As [t2] ");
result.Append("On [t2].[id]=@_p_1 ");
result.Append("And ([t].[ShortValue]>[t2].[IntValue] Or [t].[DisplayValue]=[t2].[StringValue]) ");
result.Append("And a.Id=b.Id");
//操作
_clause.Join <Sample>("t");
_clause.On("t.id", "b");
_clause.Join <Sample2>("t2");
_clause.On("t2.id", "b");
_clause.On <Sample, Sample2>((l, r) => l.ShortValue > r.IntValue || l.DisplayValue == r.StringValue);
_clause.AppendOn("a.Id=b.Id");
//验证
Assert.Equal(result.ToString(), GetSql());
}
/// <summary> /// 初始化拼音简码 /// </summary> public void InitPinYin() => PinYin = Str.PinYin(Name);
public void Test_FullPinYin()
{
Output.WriteLine(Str.FullPinYin("隔壁老王"));
}
