EF 多表查询及分页的使用

public class YlyShoufeiNrService : RepositoryFactory

{

EFCoreContext ef = new EFCoreContext();

private IQueryable<YlyShoufeiNrEntity> ResultList(YlyShoufeiNrParam param)

{

Expression<Func<YlyShoufeiNrEntity, long?>> orderBy = t => t.Id;

Expression<Func<YlyShoufeiNrEntity, bool>> where = ListFilter(param);

//left join

var resultList = (from YlyShoufeiNr in ef.YlyShoufeiNrEntitys

join YlyHulirenyuan in ef.YlyHulirenyuanEntitys

on YlyShoufeiNr.hlryId equals YlyHulirenyuan.Id into DD

from YlyHulirenyuanLeft in DD.DefaultIfEmpty()

select new YlyShoufeiNrEntity

{

Id = YlyShoufeiNr.Id,

danhao = YlyShoufeiNr.danhao,

riqi = YlyShoufeiNr.riqi,

amount = YlyShoufeiNr.amount,

hlryName = YlyHulirenyuanLeft.xingming

}).OrderBy(orderBy).Where(where);

// inner join

//var resultList = (from YlyShoufeiNr in ef.YlyShoufeiNrEntitys

// join YlyHulirenyuan in ef.YlyHulirenyuanEntitys

// on YlyShoufeiNr.hlryId equals YlyHulirenyuan.Id

// select new YlyShoufeiNrEntity

// {

// Id = YlyShoufeiNr.Id,

// danhao = YlyShoufeiNr.danhao,

// riqi = YlyShoufeiNr.riqi,

// beizhu = YlyShoufeiNr.beizhu,

// hlryId = YlyShoufeiNr.hlryId,

// amount = YlyShoufeiNr.amount,

// amount_jianmian = YlyShoufeiNr.amount_jianmian,

// if_jiesuan = YlyShoufeiNr.if_jiesuan,

// hlryName = YlyHulirenyuan.xingming

// }).OrderBy(orderBy).Where(where);

return resultList;

}

public List<YlyShoufeiNrEntity> GetPageList(YlyShoufeiNrParam param, Pagination pagination)

{

pagination.TotalCount = ResultList(param).Count();

return ResultList(param).Skip((pagination.PageIndex - 1) * pagination.PageSize).Take(pagination.PageSize).ToList();

}

private Expression<Func<YlyShoufeiNrEntity, bool>> ListFilter(YlyShoufeiNrParam param)

{

var expression = LinqExtensions.True<YlyShoufeiNrEntity>();

if (!string.IsNullOrEmpty(param.hlryName))

{

expression = expression.And(t => t.hlryName.Contains(param.hlryName));

}

if (param.Id != null)

{

expression = expression.And(t => t.Id == param.Id);

}

return expression;

}

public List<YlyShoufeiNrEntity> GetList(YlyShoufeiNrParam param)

{

return ResultList(param).ToList();

}

//获取单条数据

public YlyShoufeiNrEntity GetObject(long id)

{

YlyShoufeiNrEntity entity = new YlyShoufeiNrEntity();

YlyShoufeiNrParam param = new YlyShoufeiNrParam();

param.Id = id;

List<YlyShoufeiNrEntity> list = GetList(param);

if (list.Count > 0)

{

entity = list[0];

}

return entity;

}

public async Task<long> SaveFormObject(YlyShoufeiNrEntity entity)

{

long id = 0;

if (entity.Id.IsNullOrZero())//新增

{

if (entity.Id == 0)

{

entity.Id = null;

}

await BaseRepository().Insert_ReturnNewId(entity);

}

else//修改

{

await BaseRepository().UpdateAllField(entity);

}

return entity.Id.Value;

}

public long SaveForm(YlyShoufeiNrEntity entity)

{

long id = 0;

var tran = ef.Database.BeginTransaction();

try

{

if (entity.Id.IsNullOrZero())//新增

{

if (entity.Id == 0)

{

entity.Id = null;

}

ef.Entry(entity).State = EntityState.Added;

ef.SaveChanges();

//保存明细

List<YlyShoufeiMxEntity> list = JsonConvert.DeserializeObject<List<YlyShoufeiMxEntity>>(entity.YlyShoufeiMxEntityListJson);

for (int i = 0; i < list.Count; i++)

{

YlyShoufeiMxEntity mxEntity = list[i];

mxEntity = YlyShoufeiMxEntityUtil(mxEntity, entity);

if (mxEntity.Id != -1)

{

if (mxEntity.Id != null)

{

mxEntity.Id = null;

}

ef.Entry(mxEntity).State = EntityState.Added;

ef.SaveChanges();

}

}

}

else //修改

{

//修改单据内容

ef.Set<YlyShoufeiNrEntity>().Attach(entity);

ef.Entry(entity).State = EntityState.Modified;

ef.SaveChanges();

//删除单据明细

ef.Database.ExecuteSqlRaw("delete from YlyShoufeiMx where nrId=" + entity.Id);

ef.SaveChanges();

//保存明细

List<YlyShoufeiMxEntity> list = JsonConvert.DeserializeObject<List<YlyShoufeiMxEntity>>(entity.YlyShoufeiMxEntityListJson);

for (int i = 0; i < list.Count; i++)

{

YlyShoufeiMxEntity mxEntity = list[i];

mxEntity = YlyShoufeiMxEntityUtil(mxEntity, entity);

if (mxEntity.Id != -1)

{

if (mxEntity.Id != null)

{

mxEntity.Id = null;

}

ef.Entry(mxEntity).State = EntityState.Added;

ef.SaveChanges();

}

}

}

tran.Commit();

id = entity.Id.Value;

}

catch (Exception ex)

{

tran.Rollback();

LogHelper.Error("结算单保存异常：" + ex.Message);

throw ex;

}

return id;

}

//明细公共部分处理

private YlyShoufeiMxEntity YlyShoufeiMxEntityUtil(YlyShoufeiMxEntity mxEntity, YlyShoufeiNrEntity entity)

{

mxEntity.nrId = entity.Id;

mxEntity.hlryId = entity.hlryId;

return mxEntity;

}

}