linq关联查询存入IList<实体>
/*
linq关联查询存入IList<实体> ： select new 实体() { 实体属性 }
select new InspecMaster()
{
属性=关联的表.属性
}
*/
//.Skip(pageSize * (pageIndex - 1)).Take(pageSize) 分页
//.ToList() 转list
public IList<InspecMaster> GetInspectionMasterOrOBADetailAll(string facility, string site, string language, string BeginTime, string EndTime, string status, string TestResults, string searchValue, string searchName, int pageIndex, int pageSize, out int totalCount)
{
totalCount = 0;
var whereexpression = Expression(facility, site, status, TestResults, searchName, searchValue, BeginTime, EndTime);
var salesList = (from a in m_InspectionMasterRepository.GetRepository<InspecMaster>().Query().Queryable()
.Where(whereexpression)
join b in m_OBADetailRepository.GetRepository<OBADetail>().Query().Queryable()
on new {a.Facility, a.Site, a.Prod,a.SalesOrder,a.InspectId } equals new { b.Facility, b.Site, b.Prod, b.SalesOrder, b.InspectId }
into temp
from tt in temp.DefaultIfEmpty()
select new InspecMaster()
{
SalesOrder= a.SalesOrder,
Prod = a.Prod,
InspectId = a.InspectId,
Qty = a.Qty,
CreateDate = a.CreateDate,
Comment = a.Comment,
RowId = a.RowId,
Inspector = a.Creator,
InspectionTime = tt.CreateDate,//获取 关联其它数据
TestResults = a.InspectResult, //获取 关联其它数据
AbnormalDes = a.OperationDesc == null ? tt.AbnormalDes : a.OperationDesc,
Improvement_Des = a.Improvement_Des == null ? tt.AbnormalDes : a.Improvement_Des,
PrdShipmentUrl = tt.PrdShipmentUrl == null ? tt.PrdShipmentEdtUrl : tt.PrdShipmentUrl,
});
totalCount= salesList.Count();
return salesList.OrderBy(t => t.RowId).Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList();
}
select a 直接指定关联后的结果集
public static IList<OBAResultsDetail> OBAResultsDetail(this IRepository<OBAResultsDetail> OBADetailRepository, InspecMaster inspecMasters, string facility, string site)
{
var entry = (from a in OBADetailRepository.GetRepository<OBAResultsDetail>().Query().Queryable().
Where(i => i.Facility.Equals(facility) && i.Site.Equals(site) && i.Facility.Equals(facility) && i.Prod.Equals(inspecMasters.Prod) && i.SalesOrder.Equals(inspecMasters.SalesOrder) && i.InspectId.Equals(inspecMasters.InspectId))
select a);
return entry.ToList();
}
查询结果集存入IList<实体>：.Select().ToList()；
public static IList<InspecMaster> GetInspectionMasterBysalesOrderAndLineNo(this IRepository<InspecMaster> InspectionMasterReposity, string facility, string site, string salesOrder, string lineNo)
{
IList<InspecMaster> lst;
lst = InspectionMasterReposity.Query()
.Where(i => i.Facility.Equals(facility) && i.Site.Equals(site) && i.SalesOrder.Equals(salesOrder) && i.Type.Equals(Const.OBA.ToString()))
.Select().ToList();
return lst;
}
查询出结果直接转实体 .Select().FirstOrDefault()
InspectionMasterReposity.Query().Where(.......).Select().FirstOrDefault();
public static InspecMaster GetProdSalesInspect(this IRepository<InspecMaster> InspectionMasterReposity, InspecMaster InspecMaster, string facility, string site)
{
return InspectionMasterReposity.Query()
.Where(i => i.Facility.Equals(facility) && i.Site.Equals(site) && i.Facility.Equals(facility) && i.Prod.Equals(InspecMaster.Prod) && i.SalesOrder.Equals(InspecMaster.SalesOrder) && i.InspectId.Equals(InspecMaster.InspectId))
.Select()
.FirstOrDefault();
}
Task 线程处理
[HttpGet()]
[HttpPost()]
public async Task<IActionResult> UploadFile(参数1, 参数2....)
{
return await Task.Run(() =>
{
try
{
业务逻辑。。。。。
}
catch (Exception ex)
{
var result = new { result = false, message = ex.Message };
return Content(JsonConvert.SerializeObject(result), "text/html;charset=utf-8");
}
});
}
linq 查询条件 组装
Expression 多个条件组装 相当于 sql 中 where a=1 and b=2 and .....n1=n
public Expression<Func<InspecMaster, bool>> Expression(string facility, string site, string status, string TestResults, string searchName, string searchValue, string BeginTime, string EndTime)
{
Expression<Func<InspecMaster, bool>> expression = i => i.Facility.Equals(facility) && i.Site.Equals(site) && i.Type.Equals(Const.OBA.ToString());
//送检时间
if (!string.IsNullOrWhiteSpace(BeginTime))
{
expression = expression.AndAlso(i => i.CreateDate >= DateTime.Parse(BeginTime.Trim()));
if (!string.IsNullOrWhiteSpace(EndTime))
expression = expression.AndAlso(i => i.CreateDate <= DateTime.Parse(EndTime.Trim()));
}
else if (!string.IsNullOrWhiteSpace(EndTime))
{
expression = expression.AndAlso(i => i.CreateDate == DateTime.Parse(EndTime.Trim()));
}
//检测结果 PASS or FAILED
if (!string.IsNullOrWhiteSpace(TestResults))
expression = expression.AndAlso(i => i.InspectResult.Equals(TestResults.Trim()));
//searchbox
if (!string.IsNullOrWhiteSpace(searchName) && !string.IsNullOrWhiteSpace(searchValue))
{
switch (searchName.ToUpper())
{
case "PROD"://品号
expression = expression.AndAlso(i => i.Prod.Equals(searchValue.Trim()));
break;
case "SALESORDER"://销售单号
expression = expression.AndAlso(i => i.SalesOrder.Contains(searchValue.Trim()));
break;
case "INSPECTID"://送检单号
expression = expression.AndAlso(i => i.InspectId.Contains(searchValue.Trim()));
break;
}
}
//状态
if (!string.IsNullOrWhiteSpace(status))
{
switch (status.ToUpper())
{
case "NOTSTARTED": //未检测的
expression = expression.AndAlso(i => String.IsNullOrEmpty(i.InspectResult));
break;
case "CLOSE": //已经检测 且 通过的
expression = expression.AndAlso(i => !String.IsNullOrEmpty(i.InspectResult)).AndAlso(i => i.InspectResult.Equals(Const.PASS));
break;
case "ABNORMAL"://已检测 且 有异常的
expression = expression.AndAlso(i => !String.IsNullOrEmpty(i.InspectResult)).AndAlso(i => i.InspectResult.Equals(Const.FAILED));
break;
}
}
return expression;
}
UnitOfWork() 事务对 list<实体>增删改查的操作
public bool Saveinspection(List<OBADetail> oBADetails, List<OBAResultsDetail> oBAResultsDetails, string facility, string site, string userid)
{
var Results = false;
int expectCount = 0; //操作游标 默认0
var OBAResultsDetaillist = m_OBAResultsDetailRepository.GetRepository<OBAResultsDetail>().GetOBAResultsDetail(facility, site, oBAResultsDetails[0].InspectId, oBAResultsDetails[0].SalesOrder, oBAResultsDetails[0].Prod);
if (OBAResultsDetaillist.Count>0)
{
for (int i = 0; i < OBAResultsDetaillist.Count; i++)
{
OBAResultsDetaillist[i].TheNumber = oBAResultsDetails[i].TheNumber;
OBAResultsDetaillist[i].Version = oBAResultsDetails[i].Version;
OBAResultsDetaillist[i].IndividualResults = oBAResultsDetails[i].IndividualResults;
OBAResultsDetaillist[i].InspectionRecordsDes = oBAResultsDetails[i].InspectionRecordsDes;
OBAResultsDetaillist[i].Modifier = userid;
OBAResultsDetaillist[i].ModifyDate = DateTime.Now;
OBAResultsDetaillist[i].EntityState = EntityState.Modified; //设置操作 修改
expectCount++; //(修改)操作的记录 游标
}
//UpdateRangeGraph(list<修改后的实体>) 修改
m_OBAResultsDetailRepository.UpdateRangeGraph(OBAResultsDetaillist);
}
else
{
for (int i = 0; i < oBAResultsDetails.Count; i++)
{
oBAResultsDetails[i].Facility = facility;
oBAResultsDetails[i].Site = site;
oBAResultsDetails[i].Creator = userid;
oBAResultsDetails[i].CreateDate = DateTime.Now;
oBAResultsDetails[i].EntityState = EntityState.Added; //设置操作 添加
expectCount++; //(添加)操作的记录 游标
}
//InsertRange(list<组装后的实体>) 添加
m_OBAResultsDetailRepository.InsertRange(oBAResultsDetails);
}
try
{
m_UnitOfWork.BeginTransaction(); //BeginTransaction开启事务
int count = m_UnitOfWork.SaveChanges(); //SaveChanges保存事务 count返回影响执行的行数
Results = count == expectCount; //判断行数与游标是否 一致 ；不一致代表缺失被执行的数据
if (Results)
m_UnitOfWork.Commit(); //提交事务
else
m_UnitOfWork.Rollback(); //回滚事务 数据还原
}
catch (Exception ex)
{
iROCKExceptionManager.HandleException(iROCKExceptionPolicy.LoggingAndThrown, Error.DeleteConfigurationPackingFailed, ex, Logger);
}
return Results;
}
//事务配置类
using iROCK.Foundation.Data.Core.UnitOfWork;
using iROCK.Foundation.Interface;
using System;
namespace iROCK.Foundation.Data.Core.DataContext
{
public interface IDataContext : IDisposable
{
string Name { get; }
void ConfigureOptions(Action<DataContextOptions> setupAction);
int SaveChanges();
int SaveChanges<TEntity>(TEntity entity, params string[] propNames) where TEntity : class, IEntity;
void SyncEntitysStatePostCommit();
void SyncEntityState<TEntity>(TEntity entity) where TEntity : class, IEntity;
IUnitOfWorkAsync UnitOfWork();
}
}
