EF 使用SQL查询

public List<UserInfoDTO> UserInfo_SearchList(ref int total, int page, int rows, string userId, string userName)
{
SqlParameter[] sqlParam = new SqlParameter[1];
string sql = "select * from JYsys001 where 1=1 ";
if (!string.IsNullOrEmpty(userId))
{
string str = "%" + userId + "%";
sqlParam[0] = new SqlParameter("@id", (Object)str);
sql += " and UserId like @id ";

////不是模糊查询
//sqlParam[0] = new SqlParameter("@id", userId);
//sql += " and UserId = @id ";
}
var list = _userInfoRepository.ExecuteQuery(ref total, page, rows, sql, !string.IsNullOrEmpty(userId) ? sqlParam : null).ToList();

//Entity转换DTO
return list.ProjectedAsCollection<UserInfoDTO>();
}

 

//下面这个方面在Repository中重写

 

/// <summary>
/// SQL查询
/// 如要用此方法,那建表的字段必须和类的字段相同,也就是建数据表的字段不能用Column另起字段名
/// </summary>
/// <param name="total"></param>
/// <param name="page"></param>
/// <param name="rows"></param>
/// <param name="sql"></param>
/// <param name="sqlParam"></param>
/// <returns></returns>
public virtual List<TEntity> ExecuteQuery(ref int total, int page, int rows, string sql, params SqlParameter[] sqlParam)
{
var query = new List<TEntity>();
if (sqlParam != null)
query = _UnitOfWork.ExecuteQuery<TEntity>(sql, sqlParam).ToList();
else
query = _UnitOfWork.ExecuteQuery<TEntity>(sql).ToList();
total = query.Count();
return query.Skip(rows * (page-1)).Take(rows).ToList();
}

 

//_UnitOfWork:EF核心类(和数据库关连的类)

//例: var query = _UnitOfWork.ExecuteQuery<实体类名>( SQL语句);