Web APi之EntityFramework【CRUD】(三)

前言

之前我们系统学习了EntityFramework,个人觉得有些东西不能学了就算完了,必须要学以致用,在Web API上也少不了增(C)、删(D)、改(U)、查(R)。鉴于此,我们通过EF来实现Web API上的增删改查。之前对于EF的基本操作都是很零散的,我们应该对于CRUD都是通过完整封装来实现,并且也显得比较专业,接下来首先对EF利用Responsitory仓储模式进行完整封装。

EntityFramework完整封装

我们建立一个Core(核心类库),里面存放有关EF的完成封装。

第一步

建立所有实体的基类,将实体的公共属性放入其中,取为BaseEntity

    public class BaseEntity<T>
{
public T Id { get; set; }
}

第二步

建立仓储接口IRepository,包括基本的增、删、改、查等方法

    public interface IRepository<TEntity> where TEntity : class
{
/// <summary>
/// 获得数据列表
/// </summary>
/// <returns></returns>
IQueryable<TEntity> GetList(); /// <summary>
/// 通过id获得实体
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
TEntity GetById(object id); /// <summary>
/// 添加实体
/// </summary>
/// <param name="entity"></param>
int Insert(TEntity entity); /// <summary>
/// 添加实体集合
/// </summary>
/// <param name="entities"></param>
int Insert(IEnumerable<TEntity> entities); /// <summary>
/// 删除实体
/// </summary>
/// <param name="entity"></param>
int Delete(TEntity entity); /// <summary>
/// 根据条件删除实体
/// </summary>
/// <param name="entities"></param>
int DeleteByRequirement(Expression<Func<TEntity, bool>> func); /// <summary>
/// 更新实体
/// </summary>
/// <param name="entity"></param>
int Update(TEntity entity); /// <summary>
/// 更新实体集合
/// </summary>
/// <param name="entities"></param>
int Update(IEnumerable<TEntity> entities); }

第三步

利用仓储服务RepositoryService实现上述仓储接口IRepository

    public class RepositoryService<TEntity> : IRepository<TEntity> where TEntity : class
{
private IDbContext Context;
private bool IsNoTracking; /// <summary>
/// 获取实体集合
/// </summary>
private IDbSet<TEntity> Entities
{
get
{ return this.Context.Set<TEntity>();
}
} private DbEntityEntry Entry(TEntity entity)
{
return this.Context.Entry<TEntity>(entity);
} public RepositoryService(IDbContext context, bool isNoTracking)
{ this.Context = context;
this.IsNoTracking = isNoTracking;
} /// <summary>
/// 获取所有数据
/// </summary>
/// <returns></returns>
public IQueryable<TEntity> GetList()
{
if (!IsNoTracking)
return this.Entities.AsQueryable();
else
return this.Entities.AsNoTracking().AsQueryable();
} /// <summary>
/// 通过id获取实体
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public TEntity GetById(object id)
{
return Entities.Find(id); } /// <summary>
/// 添加实体
/// </summary>
/// <param name="entity"></param>
public int Insert(TEntity entity)
{
Entities.Add(entity);
return this.Context.SaveChanges();
} public int Insert(IEnumerable<TEntity> entities)
{
if (entities == null)
throw new ArgumentNullException("entities");
foreach (var entity in entities)
{
Entities.Add(entity);
}
return this.Context.SaveChanges();
} /// <summary>
/// 删除实体
/// </summary>
/// <param name="entity"></param>
public int Delete(TEntity entity)
{
if (!IsNoTracking)
this.Entities.Remove(entity);
else
this.Entities.Attach(entity);
this.Entities.Remove(entity);
return this.Context.SaveChanges();
} public int DeleteByRequirement(Expression<Func<TEntity, bool>> func)
{
var list = GetList().Where(func).ToList();
list.ForEach(e =>
{
if (!IsNoTracking)
this.Entities.Remove(e);
else
this.Entities.Attach(e);
this.Entities.Remove(e);
}); return this.Context.SaveChanges();
} /// <summary>
/// 更新实体
/// </summary>
/// <param name="entity"></param>
public int Update(TEntity entity)
{
if (!IsNoTracking)
return this.Context.SaveChanges();
else
this.Context.Entry(entity).State = EntityState.Modified;
return this.Context.SaveChanges();
} public int Update(IEnumerable<TEntity> entities)
{
if (entities == null)
throw new ArgumentNullException("enetities");
if (!IsNoTracking)
return this.Context.SaveChanges();
else
foreach (var t in entities)
{
this.Context.Entry(t).State = EntityState.Modified;
} return this.Context.SaveChanges();
} /// <summary>
/// 释放资源
/// </summary>
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
} protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (Context != null)
{
this.Context.Dispose();
this.Context = null;
}
}
} }

第四步

用接口IDbContext封装EF上下文DbContext中的公共方法

    public interface IDbContext
{ /// <summary>
/// 获得实体集合
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
IDbSet<TEntity> Set<TEntity>() where TEntity : class; /// <summary>
/// 执行存储过程
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="commandText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
IList<TEntity> ExecuteStoredProcedureList<TEntity>(string commandText, params object[] parameters)
where TEntity : class; /// <summary>
/// 执行SQL语句查询
/// </summary>
/// <typeparam name="TElement"></typeparam>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
IEnumerable<TElement> SqlQuery<TElement>(string sql, params object[] parameters); DbEntityEntry Entry<TEntity>(TEntity entity) where TEntity : class; /// <summary>
/// 保存数据
/// </summary>
/// <returns></returns>
int SaveChanges(); /// <summary>
/// 变更追踪代码
/// </summary>
bool ProxyCreationEnabled { get; set; } /// <summary>
/// DetectChanges方法自动调用
/// </summary>
bool AutoDetectChangesEnabled { get; set; } /// <summary>
/// 调用Dispose方法
/// </summary>
void Dispose(); }

第五步

实现EF上下文中的数据库连接、模型初始化以及映射等(也可以手动关闭全局变更追踪相对比较灵活)

    public class EFDbContext : DbContext, IDbContext
{
public EFDbContext(string connectionString)
: base(connectionString)
{ } static EFDbContext()
{
Database.SetInitializer<EFDbContext>(new DropCreateDatabaseIfModelChanges<EFDbContext>());
} /// <summary>
/// 一次性加载所有映射
/// </summary>
/// <param name="modelBuilder"></param>
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{ var typesToRegister = Assembly.GetExecutingAssembly().GetTypes()
.Where(type => !String.IsNullOrEmpty(type.Namespace))
.Where(type => type.BaseType != null && type.BaseType.IsGenericType &&
type.BaseType.GetGenericTypeDefinition() == typeof(EntityTypeConfiguration<>));
foreach (var type in typesToRegister)
{
dynamic configurationInstance = Activator.CreateInstance(type);
modelBuilder.Configurations.Add(configurationInstance);
} base.OnModelCreating(modelBuilder);
} /// <summary>
/// 获得实体集合
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public new IDbSet<TEntity> Set<TEntity>() where TEntity : class
{
return base.Set<TEntity>(); } /// <summary>
/// 实体状态
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <returns></returns> public new DbEntityEntry Entry<TEntity>(TEntity entity) where TEntity : class
{
return base.Entry<TEntity>(entity);
} /// <summary>
/// 执行存储过程
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="commandText"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IList<TEntity> ExecuteStoredProcedureList<TEntity>(string commandText, params object[] parameters) where TEntity : class
{ if (parameters != null && parameters.Length > )
{
for (int i = ; i <= parameters.Length - ; i++)
{
var p = parameters[i] as DbParameter;
if (p == null)
throw new Exception("Not support parameter type"); commandText += i == ? " " : ", "; commandText += "@" + p.ParameterName;
if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
{ commandText += " output";
}
}
} var result = this.Database.SqlQuery<TEntity>(commandText, parameters).ToList(); bool acd = this.Configuration.AutoDetectChangesEnabled;
try
{
this.Configuration.AutoDetectChangesEnabled = false; for (int i = ; i < result.Count; i++)
result[i] = this.Set<TEntity>().Attach(result[i]);
}
finally
{
this.Configuration.AutoDetectChangesEnabled = acd;
} return result;
} /// <summary>
/// SQL语句查询
/// </summary>
/// <typeparam name="TElement"></typeparam>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public IEnumerable<TElement> SqlQuery<TElement>(string sql, params object[] parameters)
{
return this.Database.SqlQuery<TElement>(sql, parameters);
} /// <summary>
/// 当查询或者获取值时是否启动创建代理
/// </summary>
public virtual bool ProxyCreationEnabled
{
get
{
return this.Configuration.ProxyCreationEnabled;
}
set
{
this.Configuration.ProxyCreationEnabled = value;
}
} /// <summary>
/// 当查询或者获取值时指定是否开启自动调用DetectChanges方法
/// </summary>
public virtual bool AutoDetectChangesEnabled
{
get
{
return this.Configuration.AutoDetectChangesEnabled;
}
set
{
this.Configuration.AutoDetectChangesEnabled = value;
}
} }

以上就是对利用EntityFramework来实现基本操作的完整封装。接下来就是相关类以及映射(场景:一个Student对应一个Flower,而一个Flower对应多个Student)

Student

    public class Student : BaseEntity<int>
{ public string Name { get; set; } public int FlowerId { get; set; }
public virtual Flower Flower { get; set; }
}

Flower

   public class Flower : BaseEntity<int>
{ public string Remark { get; set; } public virtual ICollection<Student> Students { get; set; }
}

相关映射

    public class StudentMap:EntityTypeConfiguration<Student>
{
public StudentMap()
{
ToTable("Student");
HasKey(p => p.Id);
Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
HasRequired(p => p.Flower).WithMany(p => p.Students).HasForeignKey(p => p.FlowerId); }
} public class FlowerMap:EntityTypeConfiguration<Flower>
{
public FlowerMap()
{
ToTable("Flower");
HasKey(p => p.Id);
Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
}
}

CRUD

接下来就是Web API控制器中执行增、删等操作,我们创建一个StudentController控制器,然后首先创建仓储服务。(执行Action方法,依据默认约定,未添加特性)

        public IRepository<Student> _repository;
public EFDbContext _ctx;
public StudentController()
{
_ctx = new EFDbContext("DBByConnectionString");
_repository = new RepositoryService<Student>(_ctx, true); //关闭局部变更追踪
}

执行R操作(即默认请求到HttpGet方法)

        public IEnumerable<Student> GetAllStudent()
{ return _repository.GetList().Select(d => new Student { Name = d.Name, Flower = d.Flower, Id = d.Id }).ToList(); }

当执行此查询操作时却出错了,真遗憾:

Web APi之EntityFramework【CRUD】(三)

上述修改如下即可:

 return _repository.GetList().ToList().Select(d => new Student { Name = d.Name, Flower = d.Flower, Id = d.Id }).ToList();

不知道还有没有更好的解决方案,用ToList直接将所有数据进行加载到内存中,在性能上消耗比较大。(期待你的解决方案)

特此记录

在此感谢园友(_天光云影)给出的解决方案,在GetList之后利用 Linq 进行Select,最后进行ToList即可!!!

执行CUD等操作

       public Student GetStudentById(int id)
{
var student = _repository.GetById(id);
if (student == null)
throw new HttpResponseException(HttpStatusCode.NotFound);
else
return student;
} //添加操作(HttpPost)
public HttpResponseMessage PostStudent(Student stu)
{
var insertStudent = _repository.Insert(stu);
var response = Request.CreateResponse<Student>(HttpStatusCode.Created, stu); string uri = Url.Link("DefaultApi", new { id = stu.Id });
response.Headers.Location = new Uri(uri);
return response;
} //更新操作(HttpPut)
public void PutStudent(int id, Student stu)
{
stu.Id = id;
if (_repository.Update(stu) <= )
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
} //删除操作(HttpDelete)
public void DeleteStudent(int id)
{
Student stu = _repository.GetById(id);
if (stu == null)
{
throw new HttpResponseException(HttpStatusCode.NotFound);
} _repository.Delete(stu);
}

总结

这节主要介绍了利用仓储模式完整封装EF来进行Web API基本操作,基本操作中关于返回状态码等信息,无非就是以下几个对象

HttpResponseException  返回异常

HttpResponseMessage   返回信息(诸如状态码等)

HttpStatusCode       状态码枚举(如页面未找到等)

源代码下载

WebAPI之EntityFramework

上一篇:Quartus signal tapii 的使用


下一篇:json:There is a cycle in the hierarchy!