近期比較忙,但還是想寫點什麼,就分享一些基礎的知識給大家看吧,希望能幫助一些linq新手,如果有其它疑問,可以進右上角群,進行交流探討,謝謝。
讀這篇文章之前,我先說下,每一種搜索結果集,我都以三種方式變現出來,為啦更好的理解,希望不要嫌我啰嗦。
//1 var ss = from r in db.Am_recProScheme select r; //2 var ss1 = db.Am_recProScheme; //3 string sssql = "select * from Am_recProScheme";
//1 var ss = from r in db.Am_recProScheme where r.rpId > 10 select r; //2 var ss1 = db.Am_recProScheme.Where(p => p.rpId > 10); //3 string sssql = "select * from Am_recProScheme where rpid>10";
//1 ////獲取最大的rpId //var ss = (from r in db.Am_recProScheme // select r).Max(p => p.rpId); ////獲取最小的rpId //var ss = (from r in db.Am_recProScheme // select r).Min(p => p.rpId); //獲取結果集的總數 //var ss = (from r in db.Am_recProScheme // select r).Count(); //獲取rpId的和 var ss = (from r in db.Am_recProScheme select r).Sum(p => p.rpId); //2 //var ss1 = db.Am_recProScheme.Max(p=>p.rpId); //var ss1 = db.Am_recProScheme.Min(p => p.rpId); //var ss1 = db.Am_recProScheme.Count() ; var ss1 = db.Am_recProScheme.Sum(p => p.rpId); Response.Write(ss); //3 string sssql = "select max(rpId) from Am_recProScheme"; sssql = "select min(rpId) from Am_recProScheme"; sssql = "select count(1) from Am_recProScheme"; sssql = "select sum(rpId) from Am_recProScheme";
var ss = from r in db.Am_recProScheme where r.rpId > 10 orderby r.rpId descending //倒序 // orderby r.rpId ascending //正序 select r; //正序 var ss1 = db.Am_recProScheme.OrderBy(p => p.rpId).Where(p => p.rpId > 10).ToList(); //倒序 var ss2 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).ToList(); string sssql = "select * from Am_recProScheme where rpid>10 order by rpId [desc|asc]";
//如果取最後一個可以按倒敘排列再取值 var ss = (from r in db.Am_recProScheme select r).FirstOrDefault(); //()linq to ef 好像不支持 Last() var ss1 = db.Am_recProScheme.FirstOrDefault(); //var ss1 = db.Am_recProScheme.First(); string sssql = "select top(1) * from Am_recProScheme";
//1 var ss = (from r in db.Am_recProScheme orderby r.rpId descending select r).Skip(10); //跳過前10條數據,取10條之後的所有數據 //2 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Skip(10).ToList(); //3 string sssql = "select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10";
//1 var ss = (from r in db.Am_recProScheme where r.rpId > 10 orderby r.rpId descending select r).Skip(10).Take(10); //取第11條到第20條數據 //2 Take(10): 數據從開始獲取,獲取指定數量(10)的連續數據 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).Skip(10).Take(10).ToList(); //3 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";
//1 var ss = from r in db.Am_recProScheme where r.SortsText.Contains("張") select r; //2 var ss1 = db.Am_recProScheme.Where(p => p.SortsText.Contains("張")).ToList(); //3 string sssql = "select * from Am_recProScheme where SortsText like '%張%'";
//1 var ss = from r in db.Am_recProScheme orderby r.rpId descending group r by r.recType into n select new { n.Key, //這個Key是recType rpId = n.Sum(r => r.rpId), //組內rpId之和 MaxRpId = n.Max(r => r.rpId),//組內最大rpId MinRpId = n.Min(r => r.rpId), //組內最小rpId }; foreach (var t in ss) { Response.Write(t.Key + "--" + t.rpId + "--" + t.MaxRpId + "--" + t.MinRpId); } //2 var ss1 = from r in db.Am_recProScheme orderby r.rpId descending group r by r.recType into n select n; foreach (var t in ss1) { Response.Write(t.Key + "--" + t.Min(p => p.rpId)); } //3 var ss2 = db.Am_recProScheme.GroupBy(p => p.recType); foreach (var t in ss2) { Response.Write(t.Key + "--" + t.Min(p => p.rpId)); } //4 string sssql = "select recType,min(rpId),max(rpId),sum(rpId) from Am_recProScheme group by recType";
//1 var ss = from r in db.Am_recProScheme join w in db.Am_Test_Result on r.rpId equals w.rsId orderby r.rpId descending select r; //2 var ss1 = db.Am_recProScheme.Join(db.Am_Test_Result, p => p.rpId, r => r.rsId, (p, r) => p).OrderByDescending(p => p.rpId).ToList(); //3 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";
//1 var ss = from p in db.Am_recProScheme where (new int?[] { 24, 25,26 }).Contains(p.rpId) select p; foreach (var p in ss) { Response.Write(p.Sorts); } //2 string st = "select * from Am_recProScheme where rpId in(24,25,26)";
linq是面向對象的sql。也就是說,sql是向關系型數據庫的查詢,而linq實際上是對內存裡的數據的查詢。
雖然linq原來是對象查詢,但經過ms的努力,可以通過表達式分析與實體到關系的映射(linq to sql),把linq轉換為sql語句或是對xml的查詢(linq to xml)。
因此,這種技術就成了對象到數據庫記錄的一個方便的映射、轉化與操作的工具,你再也不必去去根據不同的情況用字符串拼接的辦法生成sql,而是專心於對象模型的處理即可,你對於對象的修改最終都會被轉換為對應的update, insert, delete等sql語句,在你submit時全部提交到數據庫中。
綜爾言之,linq to sql是一個數據庫到對象結構的一個中間層, 他把對關系數據的管理轉變為對象的操作,屏蔽了麻煩的sql,而且,還可以得到vs強大的智能感知功能的幫助。
LINQ,語言集成查詢(Language INtegrated Query)是一組用於c#和Visual Basic語言的擴展。它允許編寫C#或者Visual Basic代碼以查詢數據庫相同的方式操作內存數據。
基本概念
從技術角度而言,LINQ定義了大約40個查詢操作符,如select、from、in、where以及orderby(C#)中。試用這些操作可以編寫查詢語句。不過,這些查詢還可以基於很多類型的數據,每個數據類型都需要一個單獨的LINQ類型。 經過了最近 20 年,面向對象編程技術( object-oriented (OO) programming technologies )在工業領域的應用已經進入了一個穩定的發展階段。程序員現在都已經認同像 類(classes)、對象(objects)、方法(methods)這樣的語言特性。考察現在和下一代的技術,一個新的編程技術的重大挑戰開始呈現出來,即面向對象技術誕生以來並沒有解決降低訪問和整合信息數據( accessing and integrating information )的復雜度的問題。其中兩個最主要訪問的數據源與數據庫( database )和 XML 相關。 LINQ 提供了一條更常規的途徑即給 .Net Framework 添加一些可以應用於所有信息源( all sources of information )的具有多種用途( general-purpose )的語法查詢特性( query facilities ),這是比向開發語言和運行時( runtime )添加一些關系數據( relational )特性或者類似 XML 特性( XML-specific )更好的方式。這些語法特性就叫做 .NET Language Integrated Query (LINQ) 。 包含 DLinq 和 XLinq
基礎知識
1. LINQ的讀法:(1)lin k (2)lin q 2. LINQ的關鍵詞:from, select, in, where, group by, order by … 3. LINQ的注意點:必須以select或者是group by 結束。 4. LINQ的語義: from 臨時變量 in 集合對象或數據庫對象 where 條件表達式 [order by條件] select 臨時變量中被查詢的值 [group by 條件] LINQ的查詢返回值的類型是臨時變量的類型,可能是一個對象也可能是一個集合。並且LINQ的查詢表達式是在最近一次創建對......余下全文>>
&是位運算“與”,這得從二進制的角度來看了,就是00與得0,11與得1,01與得0
&&是 and ,就是起一個和的作用。一般是作為連接兩個條件的,只有當兩個條件都成立是,&&的結果才是True