LINQ2EF-LINQ2SQL-LINQ笔记

例1：In

SQL：

Select * from dep where DEP_CLASS=3 AND dep_all_code LIKE '" + depAll + "' AND 
dep_code in (SELECT DISTINCT DEP3 FROM depview WHERE dep3 in (SELECT dep_code 
FROM person_limits WHERE upper(account)='" + UserID + "') )Order By LDAP

LINQ:

IEnumerable<string> dep_codes = perLimRep.GetMany(p => p.ACCOUNT.ToUpper() == UserID).Select(p => p.DEP_CODE);
        IEnumerable<string> DEP2s = depvRep.GetMany(d => dep_codes.Contains(d.DEP3)).Select(d => d.DEP3).Distinct().ToArray();
        var query = (from d in depRep.GetMany(d => d.DEP_CLASS == 3 && d.DEP_ALL_CODE.StartsWith(depAll))
                     select d).OrderBy(d => d.LDAP).ToArray();
        var q= (from d in query
               where
                      DEP2s.Contains(d.DEP_CODE)
               select d).OrderBy(d => d.LDAP);
        var query2 = (from d in depRep.GetMany(d => d.DEP_CLASS == 3 && depAll.StartsWith(d.DEP_ALL_CODE))
                      where
                      DEP2s.Contains(d.DEP_CODE)
                      select d).OrderBy(d => d.LDAP);
        var query3 = depRep.GetMany(d=>d.DEP_ALL_CODE.StartsWith(depAll));
        DataTable dt_dep3 = q.ToDataTable();

例1.1：复杂的in查询 (涉及到字符串拼接而成的SQL)

SQL:

        string sCommand = string.Format(
             " select dep_code, dep_name from ( "
               + " select  *  from dep where dep_code in ( "
                   + " select DISTINCT dep_up from dep where dep_code in ( "
                        + " select DISTINCT dep_up from dep where dep_code in "
                        + "(select dep_code from person_limits where UPPER(account)='{0}'))) "
               + " UNION all "
               + " select DISTINCT * from dep where dep_code in ( "
                   + " select DISTINCT dep_up from dep where dep_code in "
                        + " (select dep_code from person_limits where UPPER(account)='{0}')) "
               + " UNION all "
               + " select DISTINCT * from dep where dep_code in "
                   + " (select dep_code from person_limits where UPPER(account)='{0}') ) "
           + " where (dep_show = 1)  ", sUserAccount);
        if (string.IsNullOrEmpty(sDepClass) == false)
        {
            sDepClass = (int.Parse(sDepClass) + 1).ToString();
            sCommand += " and (dep_class = " + sDepClass + ")";
        }
        if (string.IsNullOrEmpty(sDepUp) == false)
            sCommand += " and (dep_up = " + sDepUp + ")";
        sCommand += " order by dep_code ";

        DataTable dt1 = dataProviderCenter.ProduceData(sCommand);
        if (dt1 != null && dt1.Rows.Count > 0)
        {
            foreach (DataRow dRow in dt1.Rows)
                sResult += ("" + dRow["dep_code"]).Trim() + "|" +
                    ("" + dRow["dep_name"]).Trim() + ",";
        }

LINQ:

IPERSON_LIMITSRepository presonLimitRep = new PERSON_LIMITSRepository(DatabaseFactory.GetFactory());
        IDEPRepository depRep = new DEPRepository(DatabaseFactory.GetFactory());

        List<PERSON_LIMITS> listPersonLimit = presonLimitRep.GetMany(p => p.ACCOUNT.ToUpper() == sUserAccount.ToUpper()).ToList();
        var deptemp1 = 
                        from d4 in (
                        (from d2 in
                        ((from d1 in depRep.GetManyByClass_LIST(3)
                        from p1 in listPersonLimit
                        where d1.DEP_CODE == p1.DEP_CODE 
                        select new {DEP_CODE = d1.DEP_UP}).Distinct())
                        from d3 in depRep.GetManyByClass_LIST(2)
                        where d3.DEP_CODE == d2.DEP_CODE
                        select new {DEP_CODE = d3.DEP_UP}).Distinct()
                        )
                        from d5 in depRep.GetManyByClass_LIST(1)
                        where d5.DEP_CODE == d4.DEP_CODE
                        select d5;
        var deptemp2 =
            (from d2 in
                ((from d1 in depRep.GetManyByClass_LIST(3)
                  from p1 in listPersonLimit
                  where d1.DEP_CODE == p1.DEP_CODE
                  select new { DEP_CODE = d1.DEP_UP }).Distinct())
            from d3 in depRep.GetManyByClass_LIST(2)
            where d3.DEP_CODE == d2.DEP_CODE
            select d3).Distinct();
        var deptemp3 =
            (from d1 in depRep.GetManyByClass_LIST(3)
             from p1 in listPersonLimit
             where d1.DEP_CODE == p1.DEP_CODE
             select d1).Distinct();

        List<DEP> deptemp = deptemp1.Union(deptemp2).Union(deptemp3)
            .Where(d => d.DEP_SHOW == 1).ToList();
        if (string.IsNullOrEmpty(sDepClass) == false)
            deptemp = deptemp.Where(d => d.DEP_CLASS == (decimal.Parse(sDepClass) + 1)).ToList();
        if (string.IsNullOrEmpty(sDepUp) == false)
            deptemp = deptemp.Where(d => d.DEP_UP == sDepUp).ToList();
        deptemp = deptemp.OrderBy(d => d.DEP_CODE).ToList();

 

例2：Join

SQL:

SELECT DISTINCT dep1  DEP_CODE,depname1  DEP_NAME FROM DepView join PERSON_LIMITS on dep3=dep_code WHERE upper(account)='{0}' ORDER BY DEP_CODE", account.ToUpper()

LINQ:

 var dvPERSON_LIMITS = PERSON_LIMITS_rep.GetManyAccountLIST(account.ToUpper().Trim()).ToList();
            List<string> stPERSON_LIMITS = new List<string>();
            for (int i = 0; i < dvPERSON_LIMITS.Count; ++i)
            {
                stPERSON_LIMITS.Add(dvPERSON_LIMITS[i].DEP_CODE);
            }
            var DEPVIEW_dt = DEPVIEW_rep.GetMany(value => stPERSON_LIMITS.Contains(value.DEP3)).Select(value => new { DEP_CODE = value.DEP1, DEP_NAME = value.DEPNAME1 }).Distinct().OrderBy(value => value.DEP_CODE).ToList();

 例3：ORACLE ROWNUM

SQL:

SELECT HTTP_PATH, FILE_NAME ,FILE_NAME_SOURCE FROM"
                    //+ " ( SELECT * FROM  UPLOAD WHERE SER_NO = :SerNo) WHERE ROWNUM <= 1 ORDER BY ROWNUM ASC

上面的这句SQL其实就是：

elect top 1 HTTP_PATH,FILE_NAME,FILE_NAME_SOURCE from UPLOAD where SER_NO='" + xudaxia.Trim() + "'"

 

LINQ:

第一个需要根据ROWNUM排序，所以Select((x, index) => new { x, index })这句，把index提出来了，原来的结构放在x里面.

第二次的第一个x指的是new { x, index }这个整体，实际的结构是在这个整体中的x里面.

【不能在查询表达式中使用select(t, index)这种表达形式的。】

var result = from u in ((Entities)this.DataContext).UPLOAD.Where(s => s.SER_NO == serno).Where((s, index) => index <= 1).Select((s, index) => new { s, index }).OrderBy(s => s.index)
                         select new { HTTP_PATH = u.s.HTTP_PATH, FILE_NAME = u.s.FILE_NAME, FILE_NAME_SOURCE = u.s.FILE_NAME_SOURCE };

 为了避免LINQ不识别以上的方法，故改为：

  var result = from u in ((Entities)this.DataContext).UPLOAD.Where(s => s.SER_NO == serno).Select(s => new { s.HTTP_PATH, s.FILE_NAME, s.FILE_NAME_SOURCE }).Take(1)
                         select u;

 例4：查询多字段

SQL:

SELECT ID, panel_name, PIC, Review_PIC, Is_Share, Create_ID, Create_Date, Update_Date, Is_Del, Type_Width, Type_Height FROM PANEL_STYLE where Is_Del='0' and Is_Share='1' and Create_ID <> :UserID ORDER BY Create_ID, Create_Date desc, panel_name

LINQ:

(其实还有一种比较为麻烦的方法，就是先定义一个MOdel Class的类，把属性都COPY都里面。然后在Select new YourClass{xx=u.xx,bb=u.bb...})具体代码不写了，道理都差不多一样)

var result = from p in ((Entities)this.DataContext).PANEL_STYLE.Where(s => s.IS_DEL == 0 && s.IS_SHARE == 1 && s.CREATE_ID != uid).Select(u => new { u.ID, u.PANEL_NAME, u.PIC, u.REVIEW_PIC, u.IS_SHARE, u.CREATE_ID, u.CREATE_DATE, u.UPDATE_DATE, u.IS_DEL, u.TYPE_WIDTH, u.TYPE_HEIGHT })
                         orderby p.CREATE_ID, p.CREATE_DATE descending, p.PANEL_NAME
                         select p;

 例5：LINQ做符号运算（多字符串拼接的SQL）

            if (!string.IsNullOrEmpty(sYear)) commYear = " AND ACTION_YEAR='" + sYear.Trim() + "' ";
            if (!string.IsNullOrEmpty(sQuarter)) commQuarter = " AND ACTION_QUARTER='" + sQuarter.Trim() + "' ";
            if (!string.IsNullOrEmpty(sMonth)) commMonth = " AND ACTION_MONTHS='" + sMonth.Trim() + "' ";
            if (!string.IsNullOrEmpty(sWeekY)) commWeekY = " AND ACTION_WEEK_BYYEAR='" + sWeekY.Trim() + "' ";
            if (!string.IsNullOrEmpty(sWeekM)) commWeekM = " AND ACTION_WEEK_BYMONTHS='" + sWeekM.Trim() + "' ";
            if (!string.IsNullOrEmpty(sDate)) commDate = " AND to_date(Action_Date,'yyyy-mm-dd hh24:mi:ss')=to_date('" + sDate.Trim() + "','yyyy-mm-dd hh24:mi:ss') ";
            string strComm = "SELECT (sum(Call_No)-sum(UnCall_No)) SumCallNo ,sum(Process_Time_Total)  SumProcessTime ,sum(Wait_Time_Avg*(call_no - uncall_no))  SumWaitTime ,max(Wait_People_Max)  MaxWaitPeople ";
            strComm += " FROM branch_comparison_bykind WHERE BRANCH_ID='" + sDep.Trim() + "' " + commYear + commQuarter + commMonth + commWeekY + commWeekM + commDate;

LINQ：

 public IQueryable<dynamic> GetDetailInfo(string sdep,decimal sYear,decimal sQuarter,decimal sMonth,decimal sWeekY,decimal sWeekM,string sDate)
        {
            var bcList=((Entities)this.DataContext).BRANCH_COMPARISON_BYKIND.ToList();
            var result = from b in bcList.Where(s => s.BRANCH_ID == sdep && s.ACTION_YEAR == sYear && s.ACTION_QUARTER == sQuarter && s.ACTION_MONTHS == sMonth && s.ACTION_WEEK_BYYEAR == sWeekY && s.ACTION_WEEK_BYMONTHS == sWeekM && s.ACTION_DATE == sDate).Select(b => new { SumCallNo = bcList.Sum(s => s.CALL_NO) - bcList.Sum(s => s.UNCALL_NO), Process_Time_Total = bcList.Sum(s => s.PROCESS_TIME_TOTAL), SumWaitTime = bcList.Sum(s => s.WAIT_TIME_AVG * (s.CALL_NO - s.UNCALL_NO)), MaxWaitPeople = bcList.Max(s => s.WAIT_PEOPLE_MAX) })
                         select b;
            return result as dynamic;         
        }