public virtual async Task <IEnumerable <T> > GetAsync(Expression <Func <T, bool> > filter = null, Func <IQueryable <T>, IOrderedQueryable <T> > orderBy = null, string includeProperties = "")
{
// extract the dynamic sql query and parameters from predicate
if (filter != null)
{
QueryResult result = DynamicQuery.GetDynamicQuery(_tableName, filter);
//using (IDbConnection conn = Connection)
{
//conn.Open();
var results = await SqlMapper.QueryAsync <T>(Connection, result.Sql, (object)result.Param);
return(results);
}
}
else
{
//using (IDbConnection conn = Connection)
{
//conn.Open();
var results = await SqlMapper.QueryAsync <T>(Connection, "SELECT * FROM " + _tableName);
return(results);
}
}
}
public void TestQueryEq()
{
var query = DynamicQuery.GetDynamicQuery <User>("User", x => x.Name == "Hello");
Assert.Equal("SELECT * FROM [User] WITH(NOLOCK) WHERE Name = @Name", query.Sql);
Assert.Equal("Hello", query.Param.Name);
}
public void TestQueryLT()
{
var query = DynamicQuery.GetDynamicQuery <User>("User", x => x.Id > 10);
Assert.Equal("SELECT * FROM [User] WITH(NOLOCK) WHERE Id > @Id", query.Sql);
Assert.Equal(10, query.Param.Id);
}
public virtual IEnumerable <T> Get(Expression <Func <T, bool> > filter = null, Func <IQueryable <T>, IOrderedQueryable <T> > orderBy = null, string includeProperties = "")
{
IEnumerable <T> items = null;
// extract the dynamic sql query and parameters from predicate
if (filter != null)
{
QueryResult result = DynamicQuery.GetDynamicQuery(_tableName, filter);
//using (IDbConnection cn = Connection)
{
//cn.Open();
items = SqlMapper.Query <T>(Connection, result.Sql, (object)result.Param);
}
}
else
{
//using (IDbConnection conn = Connection)
{
//conn.Open();
items = SqlMapper.Query <T>(Connection, "SELECT * FROM " + _tableName).ToList();
}
}
return(items);
}
/// <summary>
/// 条件获取记录第一条或者默认
/// </summary>
/// <returns>记录</returns>
/// <param name="predicate">筛选条件.</param>
public T GetFirstOrDefault <T>(Expression <Func <T, bool> > predicate = null)
where T : ModelBase
{
var tableName = GetTableName <T>();
var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);
return(CurrentConnection.QueryFirst <T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction));
}
/// <summary>
/// 条件获取记录集合
/// </summary>
/// <returns>集合</returns>
/// <param name="predicate">筛选条件.</param>
public List <T> GetList <T>(Expression <Func <T, bool> > predicate = null)
where T : ModelBase
{
string tableName = GetTableName <T>();
QueryResult queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);
return(CurrentConnection.Query <T>(queryResult.Sql, (object)queryResult.Param, CurrentTransaction).ToList());
}
public List <T> Get(Expression <Func <T, bool> > predicate = null)
{
QueryResult queryResult = DynamicQuery.GetDynamicQuery(_tableName, predicate);
using (IDbConnection connection = _dapperDbContext.CreateConnection())
{
return(connection.Query <T>(queryResult.Sql, (T)queryResult.Param).ToList());
}
}
public T GetFirstOrDefault(Expression <Func <T, bool> > predicate = null)
{
QueryResult queryResult = DynamicQuery.GetDynamicQuery(_tableName, predicate);
using (IDbConnection connection = _dapperDbContext.CreateConnection())
{
return(connection.QuerySingle <T>(queryResult.Sql, (T)queryResult.Param));
}
}
public static IEnumerable <T> Find <T>(this IDbConnection connection, Expression <Func <T, bool> > predicate) where T : class
{
IEnumerable <T> items = null;
// extract the dynamic sql query and parameters from predicate
QueryResult result = DynamicQuery.GetDynamicQuery(GetTableName(typeof(T)), predicate);
items = connection.Query <T>(result.Sql, (object)result.Param);
return(items);
}
/// <summary>
/// 条件判断是否存在
/// </summary>
/// <returns>是否存在</returns>
/// <param name="predicate">判断条件委托</param>
public bool Exist <T>(Expression <Func <T, bool> > predicate = null)
where T : ModelBase
{
string tableName = GetTableName <T>();
QueryResult queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate);
object result = CurrentConnection.ExecuteScalar(queryResult.Sql, (object)queryResult.Param, CurrentTransaction);
return(result != null);
}
public bool Exist(Expression <Func <T, bool> > predicate = null)
{
QueryResult queryResult = DynamicQuery.GetDynamicQuery(_tableName, predicate);
using (IDbConnection connection = _dapperDbContext.CreateConnection())
{
object result = connection.ExecuteScalar(queryResult.Sql, (object)queryResult.Param);
return(result.ToInt32OrDefault(0) > 0);
}
}
public void DynamicSinglePropertyWhereQueryReturnsExpectedSqlString()
{
// Arrange
string expectedSql = "SELECT * FROM Products WHERE CategoryID = @CategoryID";
// Actrd
QueryResult result = DynamicQuery.GetDynamicQuery <Product>("Products", (p) => p.CategoryID == 1);
// Assert
Assert.AreEqual(expectedSql, result.Sql);
Assert.AreEqual(1, result.Param.CategoryID);
}
public virtual IEnumerable <TEntity> Find(Expression <Func <TEntity, bool> > predicate)
{
IEnumerable <TEntity> items = null;
QueryResult result = DynamicQuery.GetDynamicQuery(_tableName, predicate);
using (var conn = CreateConnection())
{
conn.Open();
items = conn.Query <TEntity>(result.Sql, (object)result.Param);
}
return(items);
}
public void DateTimeQueryExtractsExpectedValue()
{
// Arrange
string expectedSql = "SELECT * FROM Products WHERE ExpiryDate > @ExpiryDate";
DateTime dt = DateTime.Now;
// Actrd
QueryResult result = DynamicQuery.GetDynamicQuery <Product>("Products", (p) => p.ExpiryDate > dt);
// Assert
Assert.AreEqual(expectedSql, result.Sql);
Assert.AreEqual(dt, result.Param.ExpiryDate);
}
public void DynamicTwoORPropertyWhereQueryReturnsExpectedSqlString()
{
// Arrange
string expectedSql = "SELECT * FROM Products WHERE CategoryID = @CategoryID OR Name = @Name";
// Actrd
QueryResult result = DynamicQuery.GetDynamicQuery <Product>("Products", (p) => p.CategoryID == 1 || p.Name == "xbox");
// Assert
Assert.AreEqual(expectedSql, result.Sql);
Assert.AreEqual(1, result.Param.CategoryID);
Assert.AreEqual("xbox", result.Param.Name);
}
public IEnumerable <T> Find(Expression <Func <T, bool> > predicate)
{
IEnumerable <T> items = null;
// extract the dynamic sql query and parameters from predicate
QueryResult result = DynamicQuery.GetDynamicQuery(_tableName, predicate);
using (IDbConnection cn = Connection)
{
cn.Open();
items = cn.Query <T>(result.Sql, (object)result.Param);
}
return(items);
}
public virtual async Task <List <TEntity> > FindAllAsync(Expression <Func <TEntity, bool> > filter = null)
{
if (filter == null)
{
return((await this.FindAllAsync(this.selectDefault)).ToList());
}
var filterSql = DynamicQuery.GetDynamicQuery <TEntity>(this.tableName, this.fieldIdName.First().Key, filter);
IDictionary <string, Object> expando = new ExpandoObject();
expando = filterSql.Param;
return((await this.FindAllAsync(filterSql.Sql, expando)).ToList());
}
public void DynamicMultiANDPropertyWhereQueryReturnsExpectedSqlString()
{
// Arrange
string expectedSql = "SELECT * FROM Products WHERE CategoryID = @CategoryID AND Name = @Name AND Price < @Price";
// Actrd
QueryResult result = DynamicQuery.GetDynamicQuery <Product>("Products", (p) => p.CategoryID == 1 && p.Name == "xbox" && p.Price < 100m);
// Assert
Assert.AreEqual(expectedSql, result.Sql);
Assert.AreEqual(1, result.Param.CategoryID);
Assert.AreEqual("xbox", result.Param.Name);
Assert.AreEqual(100, result.Param.Price);
}
public virtual bool Exists(Expression <Func <TEntity, bool> > filter)
{
QueryResult filterSql = null;
IDictionary <string, Object> expando = new ExpandoObject();
if (filter == null)
{
filterSql = new QueryResult(this.selectDefault, expando);
}
else
{
filterSql = DynamicQuery.GetDynamicQuery <TEntity>(this.tableName, this.fieldIdName.First().Key, filter);
}
expando = filterSql.Param;
return(this.Exists(filterSql.Sql, expando));
}
public virtual async Task <IEnumerable <T> > GetAsync(Expression <Func <T, bool> > filter = null, Func <IQueryable <T>, IOrderedQueryable <T> > orderBy = null, string includeProperties = "")
{
// extract the dynamic sql query and parameters from predicate
if (filter != null)
{
QueryResult result = DynamicQuery.GetDynamicQuery(_tableName, filter);
return(await WithConnection(async c => {
var results = await c.QueryAsync <T>(result.Sql, (object)result.Param);
return results;
}));
}
else
{
return(await WithConnection(async c => {
var results = await c.QueryAsync <T>("SELECT * FROM " + _tableName);
return results;
}));
}
}
/// <summary>
/// Finds the specified predicate.
/// </summary>
/// <param name="predicate">The predicate.</param>
/// <returns>A list of users that match the query.</returns>
public override IEnumerable <Skill> Find(Expression <Func <Skill, bool> > predicate)
{
List <Skill> items = new List <Skill>();
// extract the dynamic sql query and parameters from predicate
QueryResult result = DynamicQuery.GetDynamicQuery("Skill", predicate);
using (IDbConnection cn = Connection)
{
cn.Open();
var results = cn.Query(result.Sql, (object)result.Param);
for (int i = 0; i < results.Count(); i++)
{
items.Add(MapSkill(results.ElementAt(i)));
}
}
return(items);
}
public virtual async Task <IPagination <TEntity> > FindAllAsync(int pageIndex, int pageSize, string fieldOrderBy, bool desc = false, Expression <Func <TEntity, bool> > filter = null)
{
QueryResult filterSql = null;
IDictionary <string, Object> expando = new ExpandoObject();
if (filter == null)
{
filterSql = new QueryResult(this.selectDefault, expando);
}
else
{
filterSql = DynamicQuery.GetDynamicQuery <TEntity>(this.tableName, this.fieldIdName.First().Key, filter);
}
string sql = filterSql.Sql;
expando = filterSql.Param;
return(await FindAllAsync(pageIndex, pageSize, fieldOrderBy, sql, desc, expando));
}
