首页新闻找找看学习计划

这段拼接成的SQL怎么改成LINQ?急需求助!!

0
悬赏园豆:15 [已解决问题] 解决于 2014-05-27 09:33
 1 string sDepCode = "";
 2  string sDepClass = "";
 3 switch (sDepClass)
 4                 {
 5                     case "1":
 6                         sDepName = "depname1";
 7                         sDepCode = "dep1";
 8                         sWhereDep = "dep1 in (" + sDepList + ")";
 9                         
10                         showChartParameter = string.Empty;
11                         IsShowChart = false;
12                         break;
13                     case "2":
14                         sDepName = "depname2";
15                         sDepCode = "dep2";
16                         sWhereDep = "dep2 in (" + sDepList + ")";
17 
18                         showChartParameter = string.Empty;
19                         IsShowChart = false;
20                         break;
21                     case "3":
22                         sDepName = "depname3";
23                         sDepCode = "dep3";
24                         sWhereDep = "dep3 in (" + sDepList + ")";
25 
26                         if (!string.IsNullOrEmpty(sDepList))
27                         {
28                             IsShowChart = false;
29                             string[] arrTmp = sDepList.Split(',');
30                             if (arrTmp.Length > 0)
31                             {
32                                 int iTmp = 0;
33                                 foreach (string str in arrTmp)
34                                 {
35                                     if (!string.IsNullOrEmpty(str))
36                                     {
37                                         showChartParameter += "&dep=" + str.Trim();
38                                         IsShowChart = true;
39                                         iTmp++;
40                                     }
41                                     if (iTmp > 1)
42                                     {
43                                         showChartParameter = string.Empty;
44                                         IsShowChart = false;
45                                         break;
46                                     }
47                                 }
48                             }
49                             else
50                             {
51                                 showChartParameter += "&dep=" + sDepList.Trim();
52                                 IsShowChart = true;
53                             }
54                             showChartParameter = showChartParameter.Replace("'", "");
55                         }
56 
57                         break;
58 
59                 }
60  sSql = String.Format(" select {0},c.kind,sum(Call_No)  Call_No,sum(UnCall_No)  UnCall_No,sum(Process_Time_Total)  Process_Time_Total,ROUND(avg(Process_Time_Avg),1)  Process_Time_Avg,max(Wait_Time_Max)  Wait_Time_Max,ROUND(avg(Wait_Time_Avg),1)  Wait_Time_Avg,", sDepName);
61  sSql += " sum(Wait_Time_Under5)  Wait_Time_Under5,sum(Wait_Time_5to10)  Wait_Time_5to10,sum(Wait_Time_10to15)  Wait_Time_10to15,sum(Wait_Time_Over15)  Wait_Time_Over15,max(Wait_People_Max)  Wait_People_Max, ROUND(avg(Wait_People_Avg),1)  Wait_People_Avg ";
62                 sSql += "," + sDepCode;
63  sSql += ",sum(Call_No)-sum(UnCall_No) Call_UnCall_No,( select distinct to_number(SUBSTR(kind,2,LENGTH(kind)-1)) from client2_kind where kind <> 'all' and kind = c.kind)  serno ";
64 
65                 sSql += " ,sum(Wait_Time_Avg*(call_no - uncall_no))  Wait_Time_sum ";
66                 
67                 sSql += " from (client2_kind  c  join DepView  d on c.ou3 = d.dep3" + String.Format(" and {0} ", sWhereDep) + ") left join  Branch_Comparison_ByKind   b on b.Branch_Id = d.dep3";
68                 sSql += " and " + sDate;
69                 sSql += " and  b.Branch_Id=c.ou3 and c.kind=b.kind ";
70 if (int.Parse(sDepClass) < 3)
71                 {
72                     if (sDep3List == "")
73                         sSql += " and Branch_Id in ('') ";
74                     else
75                         sSql += String.Format(" and Branch_Id in ({0}) ", sDep3List);
76                 }
77                 sSql += String.Format(" WHERE c.kind in ({0})", MultipleKind1.kind);
78                 sSql += String.Format(" group by {0},{1},c.kind", sDepName, sDepCode);
79                 sSql += String.Format(" order by {0},{1},serno,c.kind", sDepName, sDepCode);

求助 求助!!!1

许大虾的主页 许大虾 | 初学一级 | 园豆:12
提问于:2014-05-07 18:24
< >
分享
最佳答案
0

对于这种拼装的SQL始终感觉无语...........
好像回答过你一个SQL转LINQ的问题了.........
我感觉更好的建议是考虑把数据库表做做调整吧.......
或者把以上这段每个case 的拼装结果做个一个LINQ,再用CASE进行选择...........

 

具体内部怎么变可以参照我上次回答的那个

收获园豆:14
hailants | 小虾三级 |园豆:750 | 2014-05-08 16:05
其他回答(1)
0

应该要自己去实现Linq Provider.可以参考一下http://www.cnblogs.com/Terrylee/category/48778.html.

其实使一个方法UserAdd(where) 变成能支持UserAdd(u=>u.Name=="qinao"&&u.Email=="qinaode@126.com")只需要自己对userAdd(Expression<Fun<T,bool>> wherelambda)中的wherelambda进行解析生成对于的sql查询字符串:select * from User where Name='qinao' and Email='qinaode@126.com'

收获园豆:1
duguao | 园豆:124 (初学一级) | 2014-05-08 11:39
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册