最近在学习和使用Entity Framework,同样,在业务层的开发中会把经常使用的方法写在基类中。在这里把写好的基类发表出来,目的是为了共同学习,因为使用EF时有很多问题还是不太理解,一是自己能力不足,二是EF还存在很多问题吧。代码如下,欢迎指出其中问题,真诚感谢!
Repository
public
class
Repository
<
TEntity
>
where
TEntity :
class
{
#region objectContext
/// <summary>
/// 获得提供用于查询和使用对象形式的实体数据功能
/// </summary>
/// <returns></returns>
public virtual ObjectContext GetObjectContext()
{
return new OAEntities();
}
#endregion
#region Find 条件表达式查询
/// <summary>
/// 所有数据的查询列表
/// </summary>
/// <returns></returns>
public IQueryable < TEntity > FindAll()
{
return GetObjectContext().CreateObjectSet < TEntity > ().AsQueryable();
}
/// <summary>
/// 根据指定条件表达式得到数据查询列表
/// </summary>
/// <param name="exp"> 条件表达式 </param>
/// <returns></returns>
public IQueryable < TEntity > FindAll(Expression < Func < TEntity, bool >> exp)
{
return GetObjectContext().CreateObjectSet < TEntity > ().Where(exp);
}
/// <summary>
/// 根据指定条件表达式得到数据实体
/// </summary>
/// <param name="exp"> 条件表达式 </param>
/// <returns></returns>
public TEntity Find(Expression < Func < TEntity, bool >> exp)
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.CreateObjectSet < TEntity > ().FirstOrDefault(exp);
}
}
#endregion
#region GetQuery ESQL查询
/// <summary>
/// ESQL查询
/// </summary>
/// <param name="query"> ESQL语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public ObjectQuery < TEntity > GetQuery( string query, params ObjectParameter[] parameter)
{
return GetObjectContext().CreateQuery < TEntity > (query, parameter);
}
/// <summary>
/// ESQL查询列表
/// </summary>
/// <param name="query"> ESQL语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public IList < TEntity > GetByQuery( string query, params ObjectParameter[] parameter)
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.CreateQuery < TEntity > (query, parameter).ToList();
}
}
#endregion
#region GetList 返回IList列表
/// <summary>
/// 所有数据列表
/// </summary>
/// <returns></returns>
public IList < TEntity > GetAll()
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.CreateObjectSet < TEntity > ().AsQueryable().ToList();
}
}
/// <summary>
/// 根据指定表达式得到数据列表
/// </summary>
/// <returns></returns>
public IList < TEntity > GetAll(Expression < Func < TEntity, bool >> exp)
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.CreateObjectSet < TEntity > ().Where(exp).ToList();
}
}
#endregion
#region Add 添加实体数据
/// <summary>
/// 添加实体数据
/// </summary>
/// <param name="entity"> 实体 </param>
public int Add(TEntity entity)
{
using (ObjectContext objectContext = GetObjectContext())
{
objectContext.CreateObjectSet < TEntity > ().AddObject(entity);
return objectContext.SaveChanges();
}
}
/// <summary>
/// 批量添加实体数据
/// </summary>
/// <param name="entity"> 实体列表 </param>
public int AddAll(IEnumerable < TEntity > entitys)
{
using (ObjectContext objectContext = GetObjectContext())
{
foreach (var entity in entitys)
{
objectContext.CreateObjectSet < TEntity > ().AddObject(entity);
}
return objectContext.SaveChanges();
}
}
#endregion
#region Delete 删除实体数据
/// <summary>
/// 删除实体数据
/// </summary>
/// <param name="entity"> 实体 </param>
public int Delete(TEntity entity)
{
using (ObjectContext objectContext = GetObjectContext())
{
objectContext.CreateObjectSet < TEntity > ().DeleteObject(entity);
return objectContext.SaveChanges();
}
}
/// <summary>
/// 批量删除实体数据
/// </summary>
/// <param name="entity"> 实体列表 </param>
public int DeleteAll(IEnumerable < TEntity > entitys)
{
using (ObjectContext objectContext = GetObjectContext())
{
foreach (var entity in entitys)
{
objectContext.CreateObjectSet < TEntity > ().DeleteObject(entity);
}
return objectContext.SaveChanges();
}
}
#endregion
#region Save 保存实体
/// <summary>
/// 保存指定的实体变动
/// </summary>
/// <param name="entity"> 实体列表 </param>
/// <returns></returns>
public int Save(IEntityWithKey entity)
{
using (ObjectContext objectContext = GetObjectContext())
{
objectContext.Attach(entity);
objectContext.SetAllModified(entity);
return objectContext.SaveChanges();
}
}
#endregion
#region ExecuteforStore 直接执行数据源语句,如SQL
/// <summary>
/// 执行数据源语句(如SQL),返回影响的行数
/// </summary>
/// <param name="commandText"> 查询语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public int ExecuteStoreCommand( string commandText, params ObjectParameter[] paramete)
{
using (ObjectContext objectContext = GetObjectContext())
{
if ( string .IsNullOrEmpty(commandText))
{
return 0 ;
}
return objectContext.ExecuteStoreCommand(commandText, paramete);
}
}
/// <summary>
/// 执行数据源查询语句(如SQL),获得数据查询列表
/// </summary>
/// <param name="commandText"> 查询语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public ObjectResult < TEntity > ExecuteStoreQuery( string commandText, params ObjectParameter[] paramete)
{
return GetObjectContext().ExecuteStoreQuery < TEntity > (commandText, paramete);
}
/// <summary>
/// 执行数据源的函数或存储过程,返回影响的行数
/// </summary>
/// <param name="functionName"> 函数或存储过程 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public int ExecuteFunction( string functionName, params ObjectParameter[] paramete)
{
using (ObjectContext objectContext = GetObjectContext())
{
if ( string .IsNullOrEmpty(functionName))
{
return 0 ;
}
return objectContext.ExecuteFunction(functionName, paramete);
}
}
/// <summary>
/// 执行数据源的查询函数或存储过程,获得数据查询列表
/// </summary>
/// <param name="functionName"> 函数或存储过程 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public ObjectResult < TEntity > ExecuteFunctionQuery( string functionName, params ObjectParameter[] paramete)
{
return GetObjectContext().ExecuteFunction < TEntity > (functionName, paramete);
}
/// <summary>
/// 执行数据源语句(如SQL),获得得数据列表
/// </summary>
/// <param name="commandText"> 查询语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public IList < TEntity > GetByStoreQuery( string commandText, params ObjectParameter[] paramete)
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.ExecuteStoreQuery < TEntity > (commandText, paramete).ToList();
}
}
/// <summary>
/// 执行数据源的查询函数或存储过程,获得数据列表
/// </summary>
/// <param name="functionName"> 函数或存储过程 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public IList < TEntity > GetByFunction( string functionName, params ObjectParameter[] paramete)
{
return GetObjectContext().ExecuteFunction < TEntity > (functionName, paramete).ToList();
}
#endregion
#region ByID 对主键相关操作,如果主键名字不为ID是需要重写
/// <summary>
/// 获得指定ID的实体
/// </summary>
/// <param name="id"> ID值 </param>
/// <returns></returns>
public virtual TEntity GetByID( object id)
{
using (ObjectContext objectContext = GetObjectContext())
{
EntityKey ek = new EntityKey(objectContext.DefaultContainerName + " . " + typeof (TEntity).Name, " ID " , id);
object entity = null ;
objectContext.TryGetObjectByKey(ek, out entity);
return (entity as TEntity);
// string query = "select value it from [" + typeof(TEntity).Name + "] as it where it.ID=@id";
// ObjectParameter p1 = new ObjectParameter("id", id);
// return objectContext.CreateQuery<TEntity>(query, p1).FirstOrDefault();
}
}
/// <summary>
/// 获得指定ID的实体列表
/// </summary>
/// <param name="ids"> 用逗号(,)分开ID </param>
/// <returns></returns>
public virtual IList < TEntity > GetByIDs( string ids)
{
using (ObjectContext objectContext = GetObjectContext())
{
string query = " select value it from [ " + typeof (TEntity).Name + " ] as it where it.ID in { " + ids + " } " ;
return objectContext.CreateQuery < TEntity > (query).ToList();
}
}
/// <summary>
/// 删除指定ID的实体。
/// 注意:此处直接执行数据源语句
/// </summary>
/// <param name="ids"> 用逗号(,)分开ID </param>
/// <returns></returns>
public virtual int DeleteByIDs( string ids)
{
if ( string .IsNullOrEmpty(ids))
{
return 0 ;
}
string sql = " delete [ " + typeof (TEntity).Name + " ] where ID in( " + ids + " ) " ;
return ExecuteStoreCommand(sql);
// return DeleteAll(GetByIDs(ids));
}
/// <summary>
/// 删除指定ID组的实体
/// </summary>
/// <param name="ids"> 多个ID数组 </param>
/// <returns></returns>
public virtual int DeleteByIDs( object [] ids)
{
string strIDs = string .Empty;
foreach ( int id in ids)
{
strIDs += " , " + id.ToString();
}
if (strIDs.Length > 1 )
{
strIDs = strIDs.Substring( 1 );
}
return DeleteByIDs(strIDs);
}
#endregion
}
{
#region objectContext
/// <summary>
/// 获得提供用于查询和使用对象形式的实体数据功能
/// </summary>
/// <returns></returns>
public virtual ObjectContext GetObjectContext()
{
return new OAEntities();
}
#endregion
#region Find 条件表达式查询
/// <summary>
/// 所有数据的查询列表
/// </summary>
/// <returns></returns>
public IQueryable < TEntity > FindAll()
{
return GetObjectContext().CreateObjectSet < TEntity > ().AsQueryable();
}
/// <summary>
/// 根据指定条件表达式得到数据查询列表
/// </summary>
/// <param name="exp"> 条件表达式 </param>
/// <returns></returns>
public IQueryable < TEntity > FindAll(Expression < Func < TEntity, bool >> exp)
{
return GetObjectContext().CreateObjectSet < TEntity > ().Where(exp);
}
/// <summary>
/// 根据指定条件表达式得到数据实体
/// </summary>
/// <param name="exp"> 条件表达式 </param>
/// <returns></returns>
public TEntity Find(Expression < Func < TEntity, bool >> exp)
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.CreateObjectSet < TEntity > ().FirstOrDefault(exp);
}
}
#endregion
#region GetQuery ESQL查询
/// <summary>
/// ESQL查询
/// </summary>
/// <param name="query"> ESQL语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public ObjectQuery < TEntity > GetQuery( string query, params ObjectParameter[] parameter)
{
return GetObjectContext().CreateQuery < TEntity > (query, parameter);
}
/// <summary>
/// ESQL查询列表
/// </summary>
/// <param name="query"> ESQL语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public IList < TEntity > GetByQuery( string query, params ObjectParameter[] parameter)
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.CreateQuery < TEntity > (query, parameter).ToList();
}
}
#endregion
#region GetList 返回IList列表
/// <summary>
/// 所有数据列表
/// </summary>
/// <returns></returns>
public IList < TEntity > GetAll()
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.CreateObjectSet < TEntity > ().AsQueryable().ToList();
}
}
/// <summary>
/// 根据指定表达式得到数据列表
/// </summary>
/// <returns></returns>
public IList < TEntity > GetAll(Expression < Func < TEntity, bool >> exp)
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.CreateObjectSet < TEntity > ().Where(exp).ToList();
}
}
#endregion
#region Add 添加实体数据
/// <summary>
/// 添加实体数据
/// </summary>
/// <param name="entity"> 实体 </param>
public int Add(TEntity entity)
{
using (ObjectContext objectContext = GetObjectContext())
{
objectContext.CreateObjectSet < TEntity > ().AddObject(entity);
return objectContext.SaveChanges();
}
}
/// <summary>
/// 批量添加实体数据
/// </summary>
/// <param name="entity"> 实体列表 </param>
public int AddAll(IEnumerable < TEntity > entitys)
{
using (ObjectContext objectContext = GetObjectContext())
{
foreach (var entity in entitys)
{
objectContext.CreateObjectSet < TEntity > ().AddObject(entity);
}
return objectContext.SaveChanges();
}
}
#endregion
#region Delete 删除实体数据
/// <summary>
/// 删除实体数据
/// </summary>
/// <param name="entity"> 实体 </param>
public int Delete(TEntity entity)
{
using (ObjectContext objectContext = GetObjectContext())
{
objectContext.CreateObjectSet < TEntity > ().DeleteObject(entity);
return objectContext.SaveChanges();
}
}
/// <summary>
/// 批量删除实体数据
/// </summary>
/// <param name="entity"> 实体列表 </param>
public int DeleteAll(IEnumerable < TEntity > entitys)
{
using (ObjectContext objectContext = GetObjectContext())
{
foreach (var entity in entitys)
{
objectContext.CreateObjectSet < TEntity > ().DeleteObject(entity);
}
return objectContext.SaveChanges();
}
}
#endregion
#region Save 保存实体
/// <summary>
/// 保存指定的实体变动
/// </summary>
/// <param name="entity"> 实体列表 </param>
/// <returns></returns>
public int Save(IEntityWithKey entity)
{
using (ObjectContext objectContext = GetObjectContext())
{
objectContext.Attach(entity);
objectContext.SetAllModified(entity);
return objectContext.SaveChanges();
}
}
#endregion
#region ExecuteforStore 直接执行数据源语句,如SQL
/// <summary>
/// 执行数据源语句(如SQL),返回影响的行数
/// </summary>
/// <param name="commandText"> 查询语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public int ExecuteStoreCommand( string commandText, params ObjectParameter[] paramete)
{
using (ObjectContext objectContext = GetObjectContext())
{
if ( string .IsNullOrEmpty(commandText))
{
return 0 ;
}
return objectContext.ExecuteStoreCommand(commandText, paramete);
}
}
/// <summary>
/// 执行数据源查询语句(如SQL),获得数据查询列表
/// </summary>
/// <param name="commandText"> 查询语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public ObjectResult < TEntity > ExecuteStoreQuery( string commandText, params ObjectParameter[] paramete)
{
return GetObjectContext().ExecuteStoreQuery < TEntity > (commandText, paramete);
}
/// <summary>
/// 执行数据源的函数或存储过程,返回影响的行数
/// </summary>
/// <param name="functionName"> 函数或存储过程 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public int ExecuteFunction( string functionName, params ObjectParameter[] paramete)
{
using (ObjectContext objectContext = GetObjectContext())
{
if ( string .IsNullOrEmpty(functionName))
{
return 0 ;
}
return objectContext.ExecuteFunction(functionName, paramete);
}
}
/// <summary>
/// 执行数据源的查询函数或存储过程,获得数据查询列表
/// </summary>
/// <param name="functionName"> 函数或存储过程 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public ObjectResult < TEntity > ExecuteFunctionQuery( string functionName, params ObjectParameter[] paramete)
{
return GetObjectContext().ExecuteFunction < TEntity > (functionName, paramete);
}
/// <summary>
/// 执行数据源语句(如SQL),获得得数据列表
/// </summary>
/// <param name="commandText"> 查询语句 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public IList < TEntity > GetByStoreQuery( string commandText, params ObjectParameter[] paramete)
{
using (ObjectContext objectContext = GetObjectContext())
{
return objectContext.ExecuteStoreQuery < TEntity > (commandText, paramete).ToList();
}
}
/// <summary>
/// 执行数据源的查询函数或存储过程,获得数据列表
/// </summary>
/// <param name="functionName"> 函数或存储过程 </param>
/// <param name="parameter"> 参数(可选) </param>
/// <returns></returns>
public IList < TEntity > GetByFunction( string functionName, params ObjectParameter[] paramete)
{
return GetObjectContext().ExecuteFunction < TEntity > (functionName, paramete).ToList();
}
#endregion
#region ByID 对主键相关操作,如果主键名字不为ID是需要重写
/// <summary>
/// 获得指定ID的实体
/// </summary>
/// <param name="id"> ID值 </param>
/// <returns></returns>
public virtual TEntity GetByID( object id)
{
using (ObjectContext objectContext = GetObjectContext())
{
EntityKey ek = new EntityKey(objectContext.DefaultContainerName + " . " + typeof (TEntity).Name, " ID " , id);
object entity = null ;
objectContext.TryGetObjectByKey(ek, out entity);
return (entity as TEntity);
// string query = "select value it from [" + typeof(TEntity).Name + "] as it where it.ID=@id";
// ObjectParameter p1 = new ObjectParameter("id", id);
// return objectContext.CreateQuery<TEntity>(query, p1).FirstOrDefault();
}
}
/// <summary>
/// 获得指定ID的实体列表
/// </summary>
/// <param name="ids"> 用逗号(,)分开ID </param>
/// <returns></returns>
public virtual IList < TEntity > GetByIDs( string ids)
{
using (ObjectContext objectContext = GetObjectContext())
{
string query = " select value it from [ " + typeof (TEntity).Name + " ] as it where it.ID in { " + ids + " } " ;
return objectContext.CreateQuery < TEntity > (query).ToList();
}
}
/// <summary>
/// 删除指定ID的实体。
/// 注意:此处直接执行数据源语句
/// </summary>
/// <param name="ids"> 用逗号(,)分开ID </param>
/// <returns></returns>
public virtual int DeleteByIDs( string ids)
{
if ( string .IsNullOrEmpty(ids))
{
return 0 ;
}
string sql = " delete [ " + typeof (TEntity).Name + " ] where ID in( " + ids + " ) " ;
return ExecuteStoreCommand(sql);
// return DeleteAll(GetByIDs(ids));
}
/// <summary>
/// 删除指定ID组的实体
/// </summary>
/// <param name="ids"> 多个ID数组 </param>
/// <returns></returns>
public virtual int DeleteByIDs( object [] ids)
{
string strIDs = string .Empty;
foreach ( int id in ids)
{
strIDs += " , " + id.ToString();
}
if (strIDs.Length > 1 )
{
strIDs = strIDs.Substring( 1 );
}
return DeleteByIDs(strIDs);
}
#endregion
}
这里对需要对ObjectContext扩展一个方法:
ObjectContextExtension
///
<summary>
/// ObjectContext扩展
/// </summary>
public static class ObjectContextExtension
{
/// <summary>
/// 把所有属性都标为已修改
/// </summary>
/// <param name="objectContext"></param>
/// <param name="item"></param>
public static void SetAllModified( this ObjectContext objectContext, IEntityWithKey item)
{
ObjectStateEntry stateEntry = objectContext.ObjectStateManager.GetObjectStateEntry(item) as ObjectStateEntry;
IEnumerable propertyNameList = stateEntry.CurrentValues.DataRecordInfo.FieldMetadata.Select(pn => pn.FieldType.Name);
foreach ( string propName in propertyNameList)
{
stateEntry.SetModifiedProperty(propName);
}
stateEntry.SetModified();
}
}
/// ObjectContext扩展
/// </summary>
public static class ObjectContextExtension
{
/// <summary>
/// 把所有属性都标为已修改
/// </summary>
/// <param name="objectContext"></param>
/// <param name="item"></param>
public static void SetAllModified( this ObjectContext objectContext, IEntityWithKey item)
{
ObjectStateEntry stateEntry = objectContext.ObjectStateManager.GetObjectStateEntry(item) as ObjectStateEntry;
IEnumerable propertyNameList = stateEntry.CurrentValues.DataRecordInfo.FieldMetadata.Select(pn => pn.FieldType.Name);
foreach ( string propName in propertyNameList)
{
stateEntry.SetModifiedProperty(propName);
}
stateEntry.SetModified();
}
}
当实体主键不为ID时需要重写后面四个ByID方法,如:
RightsService
public
class
RightsService : Repository
<
Rights
>
{
#region 重写基类方法
/// <summary>
/// 权限编号对应的实体
/// </summary>
public override Rights GetByID( object rightNo)
{
using (ObjectContext objectContext = GetObjectContext())
{
System.Data.EntityKey ek = new System.Data.EntityKey(objectContext.DefaultContainerName + " .Rights " , " RightNO " , rightNo);
object entity = null ;
objectContext.TryGetObjectByKey(ek, out entity);
return (entity as Rights);
}
}
/// <summary>
/// 获得指定ID的实体列表
/// </summary>
/// <param name="ids"> 用逗号(,)分开ID </param>
/// <returns></returns>
public override IList < Rights > GetByIDs( string ids)
{
ids = " ' " + ids.Replace( " , " , " ',' " ) + " ' " ;
string query = " select value it from [Rights] as it where it.RightNO in { " + ids + " } " ;
return GetQuery(query).ToList();
}
/// <summary>
/// 删除指定ID的实体
/// </summary>
/// <param name="ids"> 用逗号(,)分开ID </param>
/// <returns></returns>
public override int DeleteByIDs( string ids)
{
ids = " ' " + ids.Replace( " , " , " ',' " ) + " ' " ;
string sql = " delete [Rights] where RightNO in( " + ids + " ) " ;
return ExecuteStoreCommand(sql);
}
#endregion
}
{
#region 重写基类方法
/// <summary>
/// 权限编号对应的实体
/// </summary>
public override Rights GetByID( object rightNo)
{
using (ObjectContext objectContext = GetObjectContext())
{
System.Data.EntityKey ek = new System.Data.EntityKey(objectContext.DefaultContainerName + " .Rights " , " RightNO " , rightNo);
object entity = null ;
objectContext.TryGetObjectByKey(ek, out entity);
return (entity as Rights);
}
}
/// <summary>
/// 获得指定ID的实体列表
/// </summary>
/// <param name="ids"> 用逗号(,)分开ID </param>
/// <returns></returns>
public override IList < Rights > GetByIDs( string ids)
{
ids = " ' " + ids.Replace( " , " , " ',' " ) + " ' " ;
string query = " select value it from [Rights] as it where it.RightNO in { " + ids + " } " ;
return GetQuery(query).ToList();
}
/// <summary>
/// 删除指定ID的实体
/// </summary>
/// <param name="ids"> 用逗号(,)分开ID </param>
/// <returns></returns>
public override int DeleteByIDs( string ids)
{
ids = " ' " + ids.Replace( " , " , " ',' " ) + " ' " ;
string sql = " delete [Rights] where RightNO in( " + ids + " ) " ;
return ExecuteStoreCommand(sql);
}
#endregion
}
这里存在不少问题:
1. 当返回查询时,怎么销毁ObjectContext?
2. 使用全局的ObjectContext还是在每个方法都用using来new一个ObjectContext并销毁?
3. 怎样把GetByID\GetByIDs\DeleteByIDs写得通用,不再是定死为ID,而是动态使用实体的EntityKey?
4. ESQL支持update、delete语句吗?
5. ......