EF批量更新删除（linq篇）
刚开始⽤EF很多东西都不会⽤，事后想想都很简单的东西总是⽤很⿇烦的⽅式实现
1： EF的联合查询 inner join
很久很久以前我是这么写⼀个列表展⽰的，其中有两个字段Contractor和Maintaniner是需要inner join 链接查询，
但是当时太年轻不会⽤ef的链接，所以⽤了最笨的⽅法，代码如下
[Permission("L_Business")]
public ActionResult Index(BusinessRequest request)
{
//合作商户列表展⽰
var result = this.MMService.GetBusinessList(request);
result = this.GetContractorName(result);
result = this.GetMaintainerName(result);
return View(result);
}
///<summary>
///获取创建者姓名
///</summary>
///<param name="result"></param>
///<returns></returns>
private IEnumerable<Business> GetContractorName(IEnumerable<Business> result)
{
foreach (var item in result)
{
var staff = this.OAService.GetStaff(item.ContractorID);
if (staff != null) item.ConractorName = staff.Name;
}
return result;
}
///<summary>
///获取创建者姓名
///</summary>
///<param name="result"></param>
///<returns></returns>
private IEnumerable<Business> GetMaintainerName(IEnumerable<Business> result)
{
foreach (var item in result)
{
var staff = this.OAService.GetStaff(item.MaintainerID);
if (staff != null) item.MaintainerName = staff.Name;
}
return result;
}
代码很傻，需要循环多次去查出来每⾏中的Contractor和Maintaniner，这样是很影响效率，同时显得技术⽐较low的，下⾯的⽅式就好多了
⾸先：在model中设置链接外键
public class Business : ModelBase
{
//签约⼈ID
public int ContractorID { get; set; }
//外键指明要连接的表
[ForeignKey("ContractorID")]
public Staff Contractor { get; set; }
//签约⽇期
public DateTime ContractDate { get; set; }
批量更新sql语句//维护⼈员ID
public int MaintainerID { get; set; }
[ForeignKey("MaintainerID")]
public Staff Maintainer { get; set; }
}
然后：在数据层中查询时使⽤Include查询
public IEnumerable<Business> GetBusinessList(BusinessRequest request = null)
{
request = request ?? new BusinessRequest();
using (var dbContext = new MMDbContext())
{
//include可以实现在model中设置的外检链接
IQueryable<Business> business = dbContext.Business.Include("Contractor").Include("Maintainer");
business = business.Where(b => b.IsDelete == 0);
if (!string.IsNullOrEmpty(request.Name))
{
business = business.Where(b => b.Name.Contains(request.Name));
}
return business.OrderByDescending(b => b.ID).ToPagedList(request.PageIndex, request.PageSize);
}
}
最后：在页⾯展⽰的时候
[Permission("L_Business")]
public ActionResult Index(BusinessRequest request)
{
//合作商户列表展⽰
var result = this.MMService.GetBusinessList(request);
return View(result);
}
<td>
@(m.Contractor!=null?m.Contractor.Name:"")
</td>
<td>
@(m.Maintainer!=null?m.Maintainer.Name:"")
</td>
但是⽤include 的时候⽤ SQL Server Profiler查看语句会发现第⼀个include和第⼆个不⼀样
SELECT TOP (10) *
FROM ( SELECT [Extent1].[ID] AS [ID1], [Extent1].[Name] AS [Name1], [Extent1].[Address] AS [Address], [Extent1].[Contact] AS [Contact], [Extent1].[Mobile] AS [Mobile1], [Extent1].[BankID] AS [BankID], [Extent1].[BankAccount] AS [BankA FROM [dbo].[MM_Business] AS [Extent1]
INNER JOIN [dbo].[OA_Staff] AS [Extent2] ON [Extent1].[ContractorID] = [Extent2].[ID]
LEFT OUTER JOIN [dbo].[OA_Staff] AS [Extent3] ON [Extent1].[MaintainerID] = [Extent3].[ID]
WHERE 0 = [Extent1].[IsDelete]
) AS [Filter1]
WHERE [Filter1].[row_number] > 0
ORDER BY [Filter1].[ID1] DESC
使⽤ SQL Server Profiler 检测的native sql 显⽰第⼀次的include 为 inner join 第⼆个为left outer join，
这⾥是⼀个知识盲点，以后还要在做研究2：EF 多次更新和删除
图样图森破，直接上代码
public void SaveBillAndOrder(Bill model, List<Order> orderList)
{
using (var dbContext = new WebSiteDbContext())
{
model = dbContext.Insert(model);
//添加Order
if (orderList.Count > 0)
{
//增加订单，使⽤循环⽅式添加⼀个
foreach (var item in orderList)
{
var entity = dbContext.Insert(item);
}
}
dbContext.SaveChanges();
}
}
这⾥展⽰⼀个code，是我在⼀个多次插⼊的例⼦，还有很多是批量删除，批量更新都是⽤了foreach的⽅式
后来想，ef这么强⼤的⼯具不能就这样吧，所以开始百度
没有包的同学可以直接nuget
我的程序中⽤的是 EntityFramework.Extensions ⾃带的，就没有下载了
public void CancelRechargeCardPrepaymentByBillID(int BillID)
{
using (var dbContext = new WebSiteDbContext())
{
dbContext.RechargeCardPrepayments.Where(r => r.BillID == BillID).Update(r => new RechargeCardPrepayment { IsValid = (int)EnumRechargePrepaymentIsValid.WX }); dbContext.SaveChanges();
}
}
实验发现效率还是可以没有出现每⼀条写⼀个insert或者update 的情况（ps：删除的话直接把Update换成Delete()就可以了）
Native sql 如下
exec sp_executesql N'UPDATE [dbo].[CRM_CardAndBalancePrepayment] SET
[IsValid] = @p__update__0
FROM [dbo].[CRM_CardAndBalancePrepayment] AS j0 INNER JOIN (
SELECT
[Extent1].[ID] AS [ID]
FROM [dbo].[CRM_CardAndBalancePrepayment] AS [Extent1]
WHERE [Extent1].[BillID] = @p__linq__0
) AS j1 ON (j0.[ID] = j1.[ID])',N'@p__linq__0 int,@p__update__0 int',@p__linq__0=143817,@p__update__0=1
写到这⾥脑海中突然想起了⾸歌：
其实很简单其实很⾃然
两个⼈的爱由两⼈分担
其实并不难是你太悲观
隔着⼀道墙不跟谁分享
分享出来，哈哈哈哈，
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论