刚开始用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; } //维护人员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 [BankAccount], [Extent1].[AccountName] AS [AccountName], [Extent1].[AccountNumber] AS [AccountNumber], [Extent1].[ContractorID] AS [ContractorID], [Extent1].[ContractDate] AS [ContractDate], [Extent1].[MaintainerID] AS [MaintainerID], [Extent1].[Remark] AS [Remark1], [Extent1].[Status] AS [Status], [Extent1].[IsDelete] AS [IsDelete], [Extent1].[PayStatus] AS [PayStatus], [Extent1].[Balance] AS [Balance], [Extent1].[CreateDate] AS [CreateDate1], [Extent1].[CreateStaffID] AS [CreateStaffID1], [Extent1].[CreateStaffName] AS [CreateStaffName1], [Extent2].[ID] AS [ID2], [Extent2].[Number] AS [Number1], [Extent2].[Name] AS [Name2], [Extent2].[Mobile] AS [Mobile2], [Extent2].[MobileTwo] AS [MobileTwo1], [Extent2].[Gender] AS [Gender1], [Extent2].[Phone] AS [Phone1], [Extent2].[Remark] AS [Remark2], [Extent2].[Email] AS [Email1], [Extent2].[City] AS [City1], [Extent2].[Province] AS [Province1], [Extent2].[DeleteMark] AS [DeleteMark1], [Extent2].[IsActive] AS [IsActive1], [Extent2].[IsCreateAccount] AS [IsCreateAccount1], [Extent2].[ComeInDate] AS [ComeInDate1], [Extent2].[DepartureDate] AS [DepartureDate1], [Extent2].[CompanyID] AS [CompanyID1], [Extent2].[CreateDate] AS [CreateDate2], [Extent2].[CreateStaffID] AS [CreateStaffID2], [Extent2].[CreateStaffName] AS [CreateStaffName2], [Extent3].[ID] AS [ID3], [Extent3].[Number] AS [Number2], [Extent3].[Name] AS [Name3], [Extent3].[Mobile] AS [Mobile3], [Extent3].[MobileTwo] AS [MobileTwo2], [Extent3].[Gender] AS [Gender2], [Extent3].[Phone] AS [Phone2], [Extent3].[Remark] AS [Remark3], [Extent3].[Email] AS [Email2], [Extent3].[City] AS [City2], [Extent3].[Province] AS [Province2], [Extent3].[DeleteMark] AS [DeleteMark2], [Extent3].[IsActive] AS [IsActive2], [Extent3].[IsCreateAccount] AS [IsCreateAccount2], [Extent3].[ComeInDate] AS [ComeInDate2], [Extent3].[DepartureDate] AS [DepartureDate2], [Extent3].[CompanyID] AS [CompanyID2], [Extent3].[CreateDate] AS [CreateDate3], [Extent3].[CreateStaffID] AS [CreateStaffID3], [Extent3].[CreateStaffName] AS [CreateStaffName3], row_number() OVER (ORDER BY [Extent1].[ID] DESC) AS [row_number] 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 https://github.com/loresoft/EntityFramework.Extended
我的程序中用的是 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
写到这里脑海中突然想起了首歌：
- 其实很简单 其实很自然
- 两个人的爱由两人分担
- 其实并不难 是你太悲观
- 隔着一道墙不跟谁分享
分享出来，哈哈哈哈，