Linq语句使用问题总结

Linq使用in关键字完成多个条件的操作

MyDataContext db = new MyDataContext();

string[] strArray = new string [] { "ALFKI", "ANATR" };

var result = from c in db.Orders
                 where strArray.Contains(c.CustomerID)
                 orderby c.CustomerID ascending
                 select c;

LINQ的IN解析成SQL：

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[CustomerID] IN (@p0, @p1)

 

批量更新、删除数据
当程序中发生更新、删除数据操作时，要考虑是否是批量的行为。
如果是批量的操作行为，那么最好使用 update set where id in(ids)
这样多次的数据库请求就变成了一次数据库请求

多表链接

var saleOrderSplitDetileDatasLst=(from ts in tempSplitLst
                         join sos in saleOrderSplitDatas on ts.splitOrderID equals sos.splitOrderID into temp1
                         from sos in temp1.DefaultIfEmpty()
                         join psto in produceSaleTaskOrderDatas
                         on new { sos.productMaterialKind, sos.baseproduceProductTypeID }
                         equals new { psto.productMaterialKind, psto.baseProduceProductTypeID }
                         where temp1.taskOrderID > maxTaskOrderId
                         select new { temp1.taskOrderID ,sos}).ToList();

　　这块有问题：

　　　　如果tempSplitLst是数据实体表集合就不会出现：join子句中其中一个表达式的类型不正确；这个错误；但我代码时这块是实体类与数据实体像关联就会出现前边的错误

　　　　另外这么写的复杂度比较高，可以将代码改为：

 

var saleOrderSplitDetileDatasLst = from ts in tempSplitLst
                                                           from sos in saleOrderSplitDatas
                                                           from psto in produceSaleTaskOrderDatas
                                                           where ts.splitOrderID == sos.splitOrderID && sos.productMaterialKind == psto.productMaterialKind
                                                           && psto.periodTypeID == (short?)sos.periodTypeID && psto.baseProduceProductTypeID == sos.baseproduceProductTypeID
                                                           && psto.taskOrderID > maxTaskOrderId
                                                           select new { psto.taskOrderID, sos };

 

　　这样讲多个关联条件放在where子句中，降低开发难度，可读性也高一些，同时避免了表字段属性不同的这个问题

　

GroupBy多字段分组并对字段进行操作

 

var saleOrderSplitDatasLst = (from ts in tempSplitLst
                                                      join sos in saleOrderSplitDatas on ts.splitOrderID equals sos.splitOrderID
                                                      group sos
                                                      by new { sos.productMaterialKind, sos.periodTypeID, sos.baseproduceProductTypeID } into j
                                                      select new { saleMoney = j.Sum(e => e.saleMoney), saleOrderDate = j.Min(e => e.saleOrderDate), j.Key }).ToList();

 

group时列出要对分组的数据，by new后，可以对分组的字段进行一些汇总，这样在select时可以展示出集合列；对应的sql语句

INSERT INTO produceSaleTaskOrder
                    (saleMoney,orderStatusID,saleOrderDate,boxOrderStatusID,buyStatusID,productMaterialKind,periodTypeID
                        ,orderTypeID,dzType,produceLineID,baseProduceProductTypeID,branchDepID)
                    SELECT SUM(sos.saleMoney),0,MIN(sos.saleOrderDate),0,77,isnull(sos.productMaterialKind,''),sos.periodTypeID
                        ,@ordderTypeID,@dzType,@produceLineID,isnull(sos.baseProduceProductTypeID,0),@branchDepID
                    FROM #tempSplit ts 
                    INNER JOIN saleOrderSplit sos ON sos.splitOrderID = ts.splitOrderID
                    GROUP BY isnull(sos.productMaterialKind,''),sos.periodTypeID,isnull(sos.baseProduceProductTypeID,0) 

 

 

GroupBy使用解析
var GroupData = (from a in data select a).GroupBy(o => new { o.ArrAngeID });
foreach (var item in GroupData)
   {
          AirMoneyView model = new AirMoneyView();
          model.ArrAngeID = item.Key.ArrAngeID;
          foreach (var ik in item)
           {
                model.GroupID = ik.GroupID;
                model.GroupNo = ik.GroupNo;
                model.ArrAngeMoney = ik.ArrAngeMoney;
                        //要求部门名称
                model.GroupDeptName = ik.GroupDeptName;
                model.GroupDeptNo = ik.GroupDeptCode;
                model.InputDeptName = ik.InputDeptName;
                model.InputDeptNo = ik.InputDeptCode;
            }
            model.ConfirmMoney = item.Sum(o=>o.ConfirmMoney);
     }

通过需求按照主键循环第一次获得key，第二次循环使用第一次循环后的集合(KEY+后续字段)，循环出要使用的id、name等信息