public override async Task <IReadOnlyList <FilmWithActorsCategories> > Execute()
{
var builder = new WhereClauseBuilder(FilmsSql.SelectFilmsWitActorsAndCategories);
if (!string.IsNullOrEmpty(FilmTitle))
{
builder.AddClause("f.title = @FilmTitle", new { FilmTitle });
}
if (!string.IsNullOrEmpty(SearchFullText))
{
builder.AddClause("f.fulltext @@ to_tsquery(@SearchFullText)", new { SearchFullText });
}
var dic1 = new Dictionary <int, FilmWithActorsCategories>();
var dic2 = new Dictionary <int, Actor>();
var dic3 = new Dictionary <int, Category>();
var result = builder.Build();
var records =
await Connection.QueryAsync <Film, Actor, Category, FilmWithActorsCategories>(
sql : result.BuiltQuery,
param : result.BuiltParams,
map : (f, a, c) => Map(f, a, c, dic1, dic2, dic3),
transaction : Transaction,
splitOn : "actor_id, category_id");
return(records.Distinct().ToList().AsReadOnly());
}
public void Test_LambdaBuilderWhereClause_CombineCondition_QueryValueWithPropertyWithCallMethod()
{
Database db = new Database(EApp.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
PostQueryRequest request = new PostQueryRequest();
request.TopicId = 1000;
request.MiscParams.TopicId = 2000;
request.Content = "足球";
request.MiscParams.Content = "篮球";
request.CreationDateTimeParam.CreationDateTime = DateTimeUtils.ToDateTime("2015-1-22").Value;
WhereClauseBuildResult result = builder.BuildWhereClause(p => (p.TopicId <request.TopicId || p.TopicId> request.MiscParams.TopicId) &&
(p.Content.Contains(request.Content) || p.Content.Contains(request.MiscParams.Content)) ||
(p.CreationDateTime < request.CreationDateTimeParam.CreationDateTime ||
p.CreationDateTime.Equals(request.CreationDateTimeParam.CreationDateTime)));
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual(2000, result.ParameterValues.Values.ToList()[1]);
Assert.AreEqual("%足球%", result.ParameterValues.Values.ToList()[2]);
Assert.AreEqual("%篮球%", result.ParameterValues.Values.ToList()[3]);
Assert.AreEqual(request.CreationDateTimeParam.CreationDateTime, result.ParameterValues.Values.ToList()[4]);
Assert.AreEqual(request.CreationDateTimeParam.CreationDateTime, result.ParameterValues.Values.ToList()[5]);
}
public void Test_LambdaBuilderWhereClause_CombineCondition_QueryValueWithFieldWithCallMethod()
{
Database db = new Database(EApp.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
int topicId = 1000;
int topicId1 = 2000;
string content = "足球";
string content1 = "篮球";
DateTime datetime = DateTimeUtils.ToDateTime("2015-1-22").Value;
WhereClauseBuildResult result = builder.BuildWhereClause(p => (p.TopicId <topicId || p.TopicId> topicId1) &&
(p.Content.Contains(content) || p.Content.Contains(content1)) ||
p.CreationDateTime < datetime);
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual(2000, result.ParameterValues.Values.ToList()[1]);
Assert.AreEqual("%足球%", result.ParameterValues.Values.ToList()[2]);
Assert.AreEqual("%篮球%", result.ParameterValues.Values.ToList()[3]);
Assert.AreEqual(datetime, result.ParameterValues.Values.ToList()[4]);
}
public void Test_LambdaBuilderWhereClause_QueryValueWithPropertyWithCallMethod()
{
Database db = new Database(EApp.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
PostQueryRequest request = new PostQueryRequest();
request.TopicId = 1000;
request.Content = "足球";
request.CreationDateTimeParam.CreationDateTime = DateTimeUtils.ToDateTime("2015-1-22").Value;
List <int> topicIds = new List <int>();
topicIds.Add(1000);
topicIds.Add(2000);
topicIds.Add(3000);
WhereClauseBuildResult result = builder.BuildWhereClause(p => p.TopicId.Equals(request.TopicId) &&
p.Content.Contains(request.Content) &&
p.CreationDateTime < request.CreationDateTimeParam.CreationDateTime);
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual("%足球%", result.ParameterValues.Values.ToList()[1]);
Assert.AreEqual(request.CreationDateTimeParam.CreationDateTime, result.ParameterValues.Values.ToList()[2]);
}
public void Setup()
{
_mock = new Mock <IDbCommand>();
_sut = new WhereClauseBuilder(
_mock.Object,
LogicalOperator.AND,
false);
}
protected override void AddParametersFor(SpecialOwnerQuery input, WhereClauseBuilder builder) =>
builder.ForParameter(input.Id, ID_COLUMN, SqlDbType.BigInt)
.ForParameter(input.CreationDate, CREATION_DATE_COLUMN, SqlDbType.DateTime2)
.ForParameter(input.CreatedBy, CREATED_BY_COLUMN, SqlDbType.VarChar)
.ForParameter(input.ModificationDate, MODIFICATION_DATE_COLUMN, SqlDbType.DateTime2)
.ForParameter(input.ModifiedBy, MODIFIED_BY_COLUMN, SqlDbType.VarChar)
.ForParameter(input.Name, NAME_COLUMN, SqlDbType.VarChar)
.ForParameter(input.Remark, REMARK_COLUMN, SqlDbType.VarChar);
protected override void AddParametersFor(ProductQuery input, WhereClauseBuilder builder) =>
builder.ForParameter(input.Id, ID_COLUMN, SqlDbType.BigInt)
.ForParameter(input.CreationDate, CREATION_DATE_COLUMN, SqlDbType.DateTime2)
.ForParameter(input.CreatedBy, CREATED_BY_COLUMN, SqlDbType.VarChar)
.ForParameter(input.ModificationDate, MODIFICATION_DATE_COLUMN, SqlDbType.DateTime2)
.ForParameter(input.ModifiedBy, MODIFIED_BY_COLUMN, SqlDbType.VarChar)
.ForParameter(input.Brand, BRAND_COLUMN, SqlDbType.VarChar)
.ForParameter(input.Type, TYPE_COLUMN, SqlDbType.VarChar);
/// <summary>
/// 获取给定对象类型的where子句建造器。
/// </summary>
/// <typeparam name="T">给定的对象类型。</typeparam>
/// <returns>where子句建造器实例。</returns>
protected WhereClauseBuilder <T> GetWhereClauseBuilder <T>() where T : class, new()
{
WhereClauseBuilder <T> whereClauseBuilder = null;
if (!_whereClauseBuilders.ContainsKey(typeof(T)))
{
whereClauseBuilder = CreateWhereClauseBuilder <T>();
_whereClauseBuilders.Add(typeof(T), whereClauseBuilder);
}
else
{
whereClauseBuilder = _whereClauseBuilders[typeof(T)] as WhereClauseBuilder <T>;
}
return(whereClauseBuilder);
}
public void Test_LambdaBuilderWhereClause_QueryValueWithoutFieldAndPropertyNoCallMethod()
{
Database db = new Database(EApp.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
WhereClauseBuildResult result = builder.BuildWhereClause(p => p.TopicId == 1000 && p.Content == "足球");
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual("足球", result.ParameterValues.Values.ToList()[1]);
}
public void Test_LambdaBuilderWhereClause_CombineCondition_QueryValueWithoutFieldAndPropertyWithCallMethod()
{
Database db = new Database(EApp.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
WhereClauseBuildResult result = builder.BuildWhereClause(p => (p.TopicId < 1000 || p.TopicId > 2000) &&
(p.Content.Contains("足球") || p.Content.Contains("篮球")));
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual(2000, result.ParameterValues.Values.ToList()[1]);
Assert.AreEqual("%足球%", result.ParameterValues.Values.ToList()[2]);
Assert.AreEqual("%篮球%", result.ParameterValues.Values.ToList()[3]);
}
public void Test_LambdaBuilderWhereClause_QueryValueWithoutFieldAndPropertyWithCallMethod()
{
Database db = new Database(EApp.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
DateTime datetime = DateTimeUtils.ToDateTime("2015-1-22").Value;
WhereClauseBuildResult result = builder.BuildWhereClause(p => p.TopicId.Equals(1000) &&
p.Content.Contains("足球") &&
p.CreationDateTime < datetime);
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual("%足球%", result.ParameterValues.Values.ToList()[1]);
Assert.AreEqual(datetime, result.ParameterValues.Values.ToList()[2]);
}
public override async Task <IReadOnlyList <FilmWithLanguage> > Execute()
{
var query = "select f.*, l.* from film f inner join language l on l.language_id = f.language_id";
var builder = new WhereClauseBuilder(query);
if (!string.IsNullOrEmpty(FilmTitle))
{
builder.AddClause("f.title = @FilmTitle", new { FilmTitle });
}
var result = builder.Build();
var records =
await Connection.QueryAsync <Film, Language, FilmWithLanguage>(
sql : result.BuiltQuery,
param : result.BuiltParams,
map : (f, l) => new FilmWithLanguage(f)
{
Lang = l
},
transaction : Transaction,
splitOn : "language_id");
return(records.Distinct().ToList().AsReadOnly());
}
public override async Task <IReadOnlyList <Actor> > Execute()
{
var builder = new WhereClauseBuilder("select * from actor", criteriaJoinStrategy);
if (Id.HasValue)
{
builder.AddClause("actor_id = @Id", new { Id = Id.Value });
}
if (!string.IsNullOrEmpty(FirstName))
{
builder.AddClause("first_name = @FirstName", new { FirstName });
}
if (!string.IsNullOrEmpty(LastName))
{
builder.AddClause("last_name = @LastName", new { LastName });
}
var result = builder.Build();
var records = await Connection.QueryAsync <Actor>(result.BuiltQuery, result.BuiltParams, Transaction);
return(records.ToList().AsReadOnly());
}
public void Test_LambdaBuilderWhereClause_QueryValueWithFieldNoCallMethod()
{
Database db = new Database(Eagle.Data.DbProviderFactory.Default);
WhereClauseBuilder <Post> builder = db.DBProvider.CreateWhereClauseBuilder <Post>();
int topicId = 1000;
string content = "足球";
DateTime datetime = DateTimeUtils.ToDateTime("2015-1-22").Value;
WhereClauseBuildResult result = builder.BuildWhereClause(p => p.TopicId == topicId &&
p.Content == content &&
p.CreationDateTime < datetime);
string orderBy = builder.BuildOrderByClause(p => p.CreationDateTime);
Assert.AreEqual(true, !string.IsNullOrEmpty(result.WhereClause));
Assert.AreEqual(1000, result.ParameterValues.Values.ToList()[0]);
Assert.AreEqual("足球", result.ParameterValues.Values.ToList()[1]);
Assert.AreEqual(datetime, result.ParameterValues.Values.ToList()[2]);
}
protected override void AddParametersFor(AssetHistoryQuery input, WhereClauseBuilder builder) => builder.ForParameter(input.Id, ID_COLUMN, SqlDbType.BigInt) .ForParameter(input.AssetId, ASSET_ID_COLUMN, SqlDbType.BigInt) .ForParameter(input.Tag, AssetRepository.TAG_COLUMN, SqlDbType.VarChar);
protected abstract void AddParametersFor(TQuery input, WhereClauseBuilder builder);
public void SetUp()
{
builder = new WhereClauseBuilder();
}
