閑言碎語 近期比較忙，但還是想寫點什麼，就分享一些基礎的知識給大家看吧，希望能幫助一些linq新手，如果有其它疑問，可以加我QQ，進行交流探討，謝謝。 開門見山 讀這篇文章之前，我先說下，每一種搜索結果集，我都以三種方式變現出來，為啦更好的理解，希望不要嫌我啰嗦。 1.簡單的linq語法 2.帶wh ...
閑言碎語
近期比較忙，但還是想寫點什麼，就分享一些基礎的知識給大家看吧，希望能幫助一些linq新手，如果有其它疑問，可以加我QQ，進行交流探討，謝謝。
開門見山
讀這篇文章之前，我先說下，每一種搜索結果集，我都以三種方式變現出來，為啦更好的理解，希望不要嫌我啰嗦。
1.簡單的linq語法
1 //1 2 var ss = from r in db.Am_recProScheme 3 select r; 4 //2 5 var ss1 = db.Am_recProScheme; 6 //3 7 string sssql = "select * from Am_recProScheme";
2.帶where的查詢
1 //1 2 var ss = from r in db.Am_recProScheme 3 where r.rpId > 10 4 select r; 5 //2 6 var ss1 = db.Am_recProScheme.Where(p => p.rpId > 10); 7 //3 8 string sssql = "select * from Am_recProScheme where rpid>10";
3.簡單的函數計算（count，min，max，sum）
1 //1 2 ////獲取最大的rpId 3 //var ss = (from r in db.Am_recProScheme 4 // select r).Max(p => p.rpId); 5 ////獲取最小的rpId 6 //var ss = (from r in db.Am_recProScheme 7 // select r).Min(p => p.rpId); 8 //獲取結果集的總數 9 //var ss = (from r in db.Am_recProScheme 10 // select r).Count(); 11 //獲取rpId的和 12 var ss = (from r in db.Am_recProScheme 13 select r).Sum(p => p.rpId); 14 15 16 //2 17 //var ss1 = db.Am_recProScheme.Max(p=>p.rpId); 18 //var ss1 = db.Am_recProScheme.Min(p => p.rpId); 19 //var ss1 = db.Am_recProScheme.Count() ; 20 var ss1 = db.Am_recProScheme.Sum(p => p.rpId); 21 Response.Write(ss); 22 23 //3 24 string sssql = "select max(rpId) from Am_recProScheme"; 25 sssql = "select min(rpId) from Am_recProScheme"; 26 sssql = "select count(1) from Am_recProScheme"; 27 sssql = "select sum(rpId) from Am_recProScheme";
4.排序order by desc/asc
1 var ss = from r in db.Am_recProScheme 2 where r.rpId > 10 3 orderby r.rpId descending //倒序 4 // orderby r.rpId ascending //正序 5 select r; 6 7 //正序 8 var ss1 = db.Am_recProScheme.OrderBy(p => p.rpId).Where(p => p.rpId > 10).ToList(); 9 //倒序 10 var ss2 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).ToList(); 11 12 string sssql = "select * from Am_recProScheme where rpid>10 order by rpId [desc|asc]";
5.top(1)
1 //如果取最後一個可以按倒敘排列再取值 2 var ss = (from r in db.Am_recProScheme 3 select r).FirstOrDefault(); 4 5 //（）linq to ef 好像不支持 Last() 6 var ss1 = db.Am_recProScheme.FirstOrDefault(); 7 //var ss1 = db.Am_recProScheme.First(); 8 9 string sssql = "select top(1) * from Am_recProScheme";
6.跳過前面多少條數據取餘下的數據
1 //1 2 var ss = (from r in db.Am_recProScheme 3 orderby r.rpId descending 4 select r).Skip(10); //跳過前10條數據，取10條之後的所有數據 5 //2 6 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Skip(10).ToList(); 7 //3 8 string sssql = "select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10";
7.分頁數據查詢
1 //1 2 var ss = (from r in db.Am_recProScheme 3 where r.rpId > 10 4 orderby r.rpId descending 5 select r).Skip(10).Take(10); //取第11條到第20條數據 6 7 //2 Take(10): 數據從開始獲取，獲取指定數量（10）的連續數據 8 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).Skip(10).Take(10).ToList(); 9 //3 10 string sssql = "select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10 and rowNum<=20";
8.包含，類似like '%%'
1 //1 2 var ss = from r in db.Am_recProScheme 3 where r.SortsText.Contains("張") 4 select r; 5 //2 6 var ss1 = db.Am_recProScheme.Where(p => p.SortsText.Contains("張")).ToList(); 7 //3 8 string sssql = "select * from Am_recProScheme where SortsText like '%張%'";
9.分組group by
1 //1 2 var ss = from r in db.Am_recProScheme 3 orderby r.rpId descending 4 group r by r.recType into n 5 select new 6 { 7 n.Key, //這個Key是recType 8 rpId = n.Sum(r => r.rpId), //組內rpId之和 9 MaxRpId = n.Max(r => r.rpId),//組內最大rpId 10 MinRpId = n.Min(r => r.rpId), //組內最小rpId 11 }; 12 foreach (var t in ss) 13 { 14 Response.Write(t.Key + "--" + t.rpId + "--" + t.MaxRpId + "--" + t.MinRpId); 15 } 16 //2 17 var ss1 = from r in db.Am_recProScheme 18 orderby r.rpId descending 19 group r by r.recType into n 20 select n; 21 foreach (var t in ss1) 22 { 23 Response.Write(t.Key + "--" + t.Min(p => p.rpId)); 24 } 25 //3 26 var ss2 = db.Am_recProScheme.GroupBy(p => p.recType); 27 foreach (var t in ss2) 28 { 29 Response.Write(t.Key + "--" + t.Min(p => p.rpId)); 30 } 31 //4 32 string sssql = "select recType,min(rpId),max(rpId),sum(rpId) from Am_recProScheme group by recType";
10.連接查詢
1 //1 2 var ss = from r in db.Am_recProScheme 3 join w in db.Am_Test_Result on r.rpId equals w.rsId 4 orderby r.rpId descending 5 select r; 6 //2 7 var ss1 = db.Am_recProScheme.Join(db.Am_Test_Result, p => p.rpId, r => r.rsId, (p, r) => p).OrderByDescending(p => p.rpId).ToList(); 8 //3 9 string sssql = "select r.* from [Am_recProScheme] as r inner join [dbo].[Am_Test_Result] as t on r.[rpId] = t.[rsId] order by r.[rpId] desc";
11.sql中的In
1 //1 2 var ss = from p in db.Am_recProScheme 3 where (new int?[] { 24, 25,26 }).Contains(p.rpId) 4 select p; 5 foreach (var p in ss) 6 { 7 Response.Write(p.Sorts); 8 } 9 //2 10 string st = "select * from Am_recProScheme where rpId in(24,25,26)";
如果你是linq新手，建議這十個常用的查詢標記學習，再加上視圖，基本上就能解決大部分的查詢問題啦。
