例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 == && 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 == && 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) + ).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 > )
{
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()
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()
where d3.DEP_CODE == d2.DEP_CODE
select new {DEP_CODE = d3.DEP_UP}).Distinct()
)
from d5 in depRep.GetManyByClass_LIST()
where d5.DEP_CODE == d4.DEP_CODE
select d5;
var deptemp2 =
(from d2 in
((from d1 in depRep.GetManyByClass_LIST()
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()
where d3.DEP_CODE == d2.DEP_CODE
select d3).Distinct();
var deptemp3 =
(from d1 in depRep.GetManyByClass_LIST()
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 == ).ToList();
if (string.IsNullOrEmpty(sDepClass) == false)
deptemp = deptemp.Where(d => d.DEP_CLASS == (decimal.Parse(sDepClass) + )).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 = ; 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 <= ).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()
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='' and Is_Share='' 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 == && s.IS_SHARE == && 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;
}