【NET】EF总结

关于Entity Framework在MVC架构以及在项目中使用小结。

创建sqlproj项目

在项目中增加sqlproj项目，增加相关表或视图后，发布到数据库；

创建Entity项目

新建一个Entity项目，创建实体数据模型，会自动把发布的数据库表映射到实体类中；

Context类

创建一个在项目中可以通用的BaseDBContext类

public class BaseDBContext : DbContext
{
    public BaseDBContext()
        : base("name=HPDBEntities")
    {
        this.Configuration.AutoDetectChangesEnabled = false;
        this.Configuration.ValidateOnSaveEnabled = false;
        this.Configuration.LazyLoadingEnabled = false;
        this.Configuration.ProxyCreationEnabled = false;
    }
}

全局通用的操作类

有了Context类后，我们需要一个对全局通用的操作类EFHelper

public class EFHelper<TEntity> where TEntity : class
{
    private BaseDBContext dbContext = new BaseDBContext();

    public int Insert(TEntity entity)
    {
        dbContext.Entry<TEntity>(entity).State = EntityState.Added;
        return dbContext.SaveChanges();
    }

    public int Insert(List<TEntity> entitys)
    {
        foreach (var entity in entitys)
        {
            dbContext.Entry<TEntity>(entity).State = EntityState.Added;
        }
        return dbContext.SaveChanges();
    }

    public int Update(TEntity entity)
    {
        dbContext.Set<TEntity>().Attach(entity);
        PropertyInfo[] props = entity.GetType().GetProperties();
        foreach (PropertyInfo prop in props)
        {
            if (prop.GetValue(entity, null) != null)
            {
                if (prop.GetValue(entity, null).ToString() == "&nbsp;")
                    dbContext.Entry(entity).Property(prop.Name).CurrentValue = null;
                dbContext.Entry(entity).Property(prop.Name).IsModified = true;
            }
        }
        return dbContext.SaveChanges();
    }

    public int Delete(TEntity entity)
    {
        dbContext.Set<TEntity>().Attach(entity);
        dbContext.Entry<TEntity>(entity).State = EntityState.Deleted;
        return dbContext.SaveChanges();
    }

    public int Delete(Expression<Func<TEntity, bool>> predicate)
    {
        var entitys = dbContext.Set<TEntity>().Where(predicate).ToList();
        entitys.ForEach(m => dbContext.Entry<TEntity>(m).State = EntityState.Deleted);
        return dbContext.SaveChanges();
    }

    public TEntity FindEntity(object keyValue)
    {
        return dbContext.Set<TEntity>().Find(keyValue);
    }

    public TEntity FindEntity(Expression<Func<TEntity, bool>> where)
    {
        return dbContext.Set<TEntity>().FirstOrDefault(where);
    }

    public List<TEntity> FindList()
    {
        return dbContext.Set<TEntity>().ToList<TEntity>();
    }

    public IQueryable<TEntity> IQueryable()
    {
        return dbContext.Set<TEntity>();
    }

    public IEnumerable<TEntity> IQueryable(Expression<Func<TEntity, bool>> where)
    {
        return dbContext.Set<TEntity>().Where(where);
    }

    public IEnumerable<TEntity> IQueryable<TKey>(Expression<Func<TEntity, TKey>> orderBy, Pagination pagination)
    {
        var tempData = dbContext.Set<TEntity>().AsQueryable();
        if (!pagination.sort.IsEmpty() && pagination.sort.Equals("asc"))
        {
            tempData = tempData.OrderBy(orderBy);
        }
        else
        {
            tempData = tempData.OrderByDescending(orderBy);
        }
        pagination.records = tempData.Count();
        tempData = tempData.Skip<TEntity>(pagination.offset).Take<TEntity>(pagination.rows).AsQueryable();
        return tempData.ToList();
    }

    public IEnumerable<TEntity> IQueryable<TKey>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TKey>> orderBy, Pagination pagination)
    {
        var tempData = dbContext.Set<TEntity>().AsQueryable();
        if (!pagination.sort.IsEmpty() && pagination.sort.Equals("asc"))
        {
            tempData = tempData.OrderBy(orderBy);
        }
        else
        {
            tempData = tempData.OrderByDescending(orderBy);
        }
        tempData = tempData.Where(where).AsQueryable();
        pagination.records = tempData.Count();
        tempData = tempData.Skip<TEntity>(pagination.offset).Take<TEntity>(pagination.rows).AsQueryable();
        return tempData.ToList();
    }
}

以上可以通用在整个项目的基本操作类

实际使用

创建了操作类后，就可以直接在项目中进行操作

private EFHelper<Entity.T_User> helper = new EFHelper<Entity.T_User>();

// 获取
var en = helper.FindEntity(id); // 主键直接获取
var en = helper.FindEntity(m => m.Name.Equals(name, StringComparison.OrdinalIgnoreCase)); // 不为主键进行linq进行where

// 新增
var en = new Entity.T_User() { Id = Guid.NewGuid(), Name = name };
helper.Insert(en);

// 修改
var result = helper.FindEntity(id);
if (result != null)
{
    result.Name = name;
    helper.Update(result);
};


// 删除
var result = helper.FindEntity(userId);
if (result != null)
{
    helper.Delete(result);
};

// 查询
var list = helper.IQueryable(m => m.Name.Contains(keyword));

如果需要在复杂的地方进行操作，如下

var query = helper.IQueryable(m => m.HasDelete.HasValue & !m.HasDelete.Value);
if (!keyword.IsEmpty())
{
    query = query.Where(m => m.Name.Contains(keyword)).AsQueryable();
}
var tempData = query.OrderBy(m => m.Sorting).ToList()
                    .Join(helper_role.IQueryable(), m => m.RoleId, c => c.Id, (m, c) => new
                    {
                        m.Id,
                        m.Name
                        RoleId = c.Id
                        RoleName = c.Name
                    })
                    .AsQueryable();
pagination.records = tempData.Count();
var list = tempData.Limit(pagination);

如果，表连接或条件较复杂，推荐直接写视图，通过操作视图得到数据。