SQL to LinQ 之查询条件 In的表示（Any）【实例类型、list集合】

==      其实.Any()的用法都一样。

SQL：

 strQuery = "SELECT OPER, OPER_DESC, OPER_SHORT_DESC FROM WIPOPER WHERE"
                                      + "     FACTORY = ?"
                                      + " AND OPER IN ("
                                      + " SELECT DISTINCT OT.OPER FROM WIPOPER OT, WIPMATFLOWREL MF, WIPFLOWOPERREL FO WHERE"
                                      + "     OT.FACTORY = ?"
                                      + " AND OT.OPER >= ?"
                                      + " AND OT.OPER like ?"
                                      + " AND MF.FACTORY = OT.FACTORY"
                                      + " AND FO.FACTORY = OT.FACTORY"
                                      + " AND MF.MAT_ID = ?"
                                      + " AND FO.FLOW = MF.FLOW"
                                      + " AND OT.OPER = FO.OPER"
                                      + " ) ORDER BY OPER";

to Linq：

var strQuery = ctx.Wipoper.Where(t => t.Factory == sFactory && t.Oper.CompareTo(sOper) >= 0 && t.Oper.Contains(sOper)).Join(
                        ctx.Wipmatflowrel.Where(t => t.MatId == sMatId),
                        a => new { f = a.Factory },
                        b => new { f = b.Factory },
                        (a, b) => new
                        {
                            a.Factory,
                            a.Oper,
                            b.Flow,
                        }).Join(
                        ctx.Wipflowoperrel,
                        a => new { f = a.Factory, fl = a.Flow, op = a.Oper },
                        b => new { f = b.Factory, fl = b.Flow, op = b.Oper },
                        (a, b) => new
                        {
                            a.Oper,
                        }).ToList();
                    var strQuery1 = ctx.Wipoper.Where(t => t.Factory == sFactory && strQuery.Any(tt => tt.Oper == t.Oper)).ToList();

 

 

 

注：list转为数组：

string[] s = new string[strQuery.Count];
for (int i = 0; i < strQuery.Count; i++)
{
   s[i] = strQuery[i].Oper;
}

字段  In   list：

SQL

 string InCondition = "";

                        if (LOT_LIST.Count < 1)
                        {
                            return false;
                        }
                        for (i = 0; i < LOT_LIST.Count; i++)
                        {
                            InCondition += "'";
                            InCondition += Convert.ToString(LOT_LIST[i]);

                            InCondition += "'";
                            if (i < LOT_LIST.Count - 1)
                            {
                                InCondition += ", ";
                            }
                        }

                        strQuery = "" +
                        "SELECT LT.* , MT.MAT_DESC, FT.FLOW_DESC, OT.OPER_DESC " + DBGV.RETURN_STRING +
                        "FROM WIPLOTSTS LT, WIPMAT MT, WIPFLOW FT, WIPOPER OT WHERE" + DBGV.RETURN_STRING
                            + " LT.FACTORY=?" + DBGV.RETURN_STRING
                            + " AND MT.FACTORY=LT.FACTORY" + DBGV.RETURN_STRING
                            + " AND FT.FACTORY=LT.FACTORY" + DBGV.RETURN_STRING
                            + " AND OT.FACTORY=LT.FACTORY" + DBGV.RETURN_STRING
                            + " AND MT.MAT_ID=LT.MAT_ID" + DBGV.RETURN_STRING
                            + " AND FT.FLOW=LT.FLOW" + DBGV.RETURN_STRING
                            + " AND OT.OPER=LT.OPER" + DBGV.RETURN_STRING
                            + " AND LT.LOT_ID IN (" + InCondition + ")" + DBGV.RETURN_STRING
                            + " AND LT.LOT_ID>=?" + DBGV.RETURN_STRING
                            + " ORDER BY LT.LOT_ID ";

To  LinQ:

List：

List<string> lotlist = new List<string>（）;

 strQuery = ctx.Wiplotsts.Where(t => t.Factory == sFactory && lotList.Any(s => t.LotId == s)
                   && t.LotId.CompareTo(sLotId) >= 0)
                        .Join(
                     ctx.Wipmat,
                    a => new { f = a.Factory, id = a.MatId },
                    b => new { f = b.Factory, id = b.MatId },
                    (a, b) => new { A1 = a, b.MatDesc }
                   ).Join(
                       ctx.Wipflow,
                    a => new { f = a.A1.Factory, fl = a.A1.Flow },
                    b => new { f = b.Factory, fl = b.Flow },
                    (a, b) => new { A2 = a, b.FlowDesc }
                   ).Join(
                       ctx.Wipoper,
                    a => new { f = a.A2.A1.Factory, fl = a.A2.A1.Oper },
                    b => new { f = b.Factory, fl = b.Oper },
                    (a, b) => new { wiplotsts = a.A2.A1, a.A2.MatDesc, a.FlowDesc, b.OperDesc }).OrderBy(t => t.wiplotsts.LotId).ToList();
                

 

转载于:https://my.oschina.net/8824/blog/3070376