public static Task <int> UpdateAsync <T>(this IDbConnection connection, T data, Expression <Func <T, bool> > predicate, bool setIdentity, params Expression <Func <T, object> >[] properties)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (data == null)
{
throw new ArgumentNullException(nameof(data));
}
if (predicate == null)
{
throw new ArgumentNullException(nameof(predicate));
}
if (properties == null)
{
throw new ArgumentNullException(nameof(properties));
}
var dbKind = connection.GetDbKind();
var update = PrimitiveSql.CreateUpdate(dbKind, setIdentity, properties);
var where = PredicateSql.From(dbKind, predicate);
var param = where.Parameter.Merge(data, properties);
var builder = new StringBuilder();
builder.AppendLine(update);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(connection.ExecuteAsync(builder.ToString(), param));
}
public static async Task <IReadOnlyList <T> > SelectAsync <T>(this IDbConnection connection, Expression <Func <T, bool> > predicate, params Expression <Func <T, object> >[] properties)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (predicate == null)
{
throw new ArgumentNullException(nameof(predicate));
}
if (properties == null)
{
throw new ArgumentNullException(nameof(properties));
}
var select = PrimitiveSql.CreateSelect(properties);
var where = PredicateSql.From(connection.GetDbKind(), predicate);
var builder = new StringBuilder();
builder.AppendLine(select);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
var result = await connection.QueryAsync <T>(builder.ToString(), where.Parameter).ConfigureAwait(false);
return(result as IReadOnlyList <T>);
}
public void Null()
{
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.Name == null);
var expectStatement = "名前 is null";
IDictionary <string, object> expectParameter = new ExpandoObject();
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
/// <summary>
/// 指定されたテーブルから指定の条件に一致するレコードを取得します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="predicate">抽出条件</param>
/// <param name="properties">取得対象の列</param>
/// <returns>取得したレコード</returns>
public virtual IReadOnlyList <T> Select <T>(Expression <Func <T, bool> > predicate, Expression <Func <T, object> > properties)
{
var select = PrimitiveSql.CreateSelect(properties);
var where = PredicateSql.From(this.DbKind, predicate);
var builder = new StringBuilder();
builder.AppendLine(select);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(this.Connection.Query <T>(builder.ToString(), where.Parameter, this.Transaction, true, this.Timeout) as IReadOnlyList <T>);
}
public void 以下()
{
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.Id <= 1);
var expectStatement = "Id <= @p0";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", 1);
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
public void 右辺が静的プロパティ()
{
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.Age == SomeClass.StaticProperty);
var expectStatement = "Age = @p0";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", SomeClass.StaticProperty);
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
public void InverseBoolean()
{
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => !x.HasChildren);
var expectStatement = "HasChildren <> @p0";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", true);
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
public void 右辺がコンストラクタ()
{
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.Name == new string('a', 3));
var expectStatement = "名前 = @p0";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", "aaa");
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
public void 右辺が静的メソッド()
{
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.Name == SomeClass.StaticMethod());
var expectStatement = "名前 = @p0";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", SomeClass.StaticMethod());
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
/// <summary>
/// 指定されたテーブルにおいて指定の条件に一致するレコード数を取得します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="predicate">抽出条件</param>
/// <returns>レコード数</returns>
public virtual ulong Count <T>(Expression <Func <T, bool> > predicate)
{
var count = PrimitiveSql.CreateCount <T>();
var where = PredicateSql.From(this.DbKind, predicate);
var builder = new StringBuilder();
builder.AppendLine(count);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(this.Connection.ExecuteScalar <ulong>(builder.ToString(), where.Parameter, this.Transaction, this.Timeout));
}
/// <summary>
/// 指定されたテーブルから指定の条件に一致するレコードを非同期的に削除します。
/// </summary>
/// <typeparam name="T">テーブルにマッピングされた型</typeparam>
/// <param name="predicate">削除条件</param>
/// <returns>影響した行数</returns>
public virtual Task <int> DeleteAsync <T>(Expression <Func <T, bool> > predicate)
{
var delete = PrimitiveSql.CreateDelete <T>();
var where = PredicateSql.From(this.DbKind, predicate);
var builder = new StringBuilder();
builder.AppendLine(delete);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(this.Connection.ExecuteAsync(builder.ToString(), where.Parameter, this.Transaction, this.Timeout));
}
public void 右辺がインデクサ()
{
var ids = new [] { 1, 2, 3 };
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.Id == ids[0]);
var expectStatement = "Id = @p0";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", ids[0]);
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
public void 右辺がラムダ式()
{
Func <int, string> getName = x => x.ToString();
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.Name == getName(123));
var expectStatement = "名前 = @p0";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", "123");
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
public void Contains()
{
var value = Enumerable.Range(0, 3).Cast <object>().ToArray();
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => value.Contains(x.Id));
var expectStatement = "Id in @p0";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", value);
actual.Parameter.IsStructuralEqual(expectParameter);
actual.Statement.Is(expectStatement);
}
public void Or()
{
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.Id > 1 || x.Name == "xin9le");
var expectStatement = "Id > @p0 or 名前 = @p1";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", 1);
expectParameter.Add("p1", "xin9le");
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
/// <summary>
/// 指定の条件に一致するレコードを指定された情報で非同期的に更新します。
/// </summary>
/// <typeparam name="T">テーブルの型</typeparam>
/// <param name="data">更新するデータ</param>
/// <param name="predicate">更新条件</param>
/// <param name="properties">更新する列にマッピングされるプロパティ式のコレクション</param>
/// <param name="setIdentity">自動採番のID列に値を設定するかどうか</param>
/// <returns>影響した行数</returns>
public virtual Task <int> UpdateAsync <T>(T data, Expression <Func <T, bool> > predicate, Expression <Func <T, object> > properties, bool setIdentity)
{
var update = PrimitiveSql.CreateUpdate(this.DbKind, properties, setIdentity);
var where = PredicateSql.From(this.DbKind, predicate);
var param = where.Parameter.Merge(data, properties);
var builder = new StringBuilder();
builder.AppendLine(update);
builder.AppendLine(nameof(where));
builder.Append($" {where.Statement}");
return(this.Connection.ExecuteAsync(builder.ToString(), param, this.Transaction, this.Timeout));
}
public void AndOr2()
{
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.Id > 1 && (x.Name == "xin9le" || x.Age <= 30));
var expectStatement = "Id > @p0 and (名前 = @p1 or Age <= @p2)";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", 1);
expectParameter.Add("p1", "xin9le");
expectParameter.Add("p2", 30);
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
public void BooleanAndOr()
{
var actual = PredicateSql.From <Person>(DbKind.SqlServer, x => x.HasChildren == true || x.Id != 0 || x.Name == "xin9le" && !x.HasChildren);
var expectStatement = "HasChildren = @p0 or Id <> @p1 or (名前 = @p2 and HasChildren <> @p3)";
IDictionary <string, object> expectParameter = new ExpandoObject();
expectParameter.Add("p0", true);
expectParameter.Add("p1", 0);
expectParameter.Add("p2", "xin9le");
expectParameter.Add("p3", true);
actual.Parameter.Is(expectParameter);
actual.Statement.Is(expectStatement);
}
