public void Test_Or_5()
{
//结果
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.Or <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_From_4()
{
//结果
var result = new Str();
result.AppendLine("Select * ");
result.Append("From ");
result.AppendLine("(Select Count(*) ");
result.AppendLine("From [Test2] ");
result.AppendLine("Where [Name]=@_p_0) As [test] ");
result.Append("Where [Age]=@_p_1");
//执行
_builder.From(builder => builder.Count().From("Test2").Where("Name", "a"), "test").Where("Age", 1);
Output.WriteLine(_builder.ToSql());
//验证
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_Join_3()
{
//结果
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");
//执行
var builder2 = _builder.New().From("Test2").Where("Name", "a");
_builder.From("Test").Join(builder2, "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_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());
}
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_With_2()
{
//结果
var result = new Str();
result.AppendLine("With [Test] ");
result.AppendLine("As (Select [a],[b] ");
result.AppendLine("From [Test2]),");
result.AppendLine("[Test3] ");
result.AppendLine("As (Select [a],[b] ");
result.AppendLine("From [Test3])");
result.AppendLine("Select [a],[b] ");
result.Append("From [Test]");
//执行
var builder2 = _builder.New().Select("a,b").From("Test2");
var builder3 = _builder.New().Select("a,b").From("Test3");
_builder.Select("a,b").From("Test").With("Test", builder2).With("Test3", builder3);
//验证
Assert.Equal(result.ToString(), _builder.ToSql());
}
public void Test_Select_7()
{
//结果
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("*").AppendSelect("(").Select(builder2, "").AppendSelect(") As 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"]);
}