Details of the Linq syntax.
Gossip
I'm busy recently, but I still want to write something. I 'd like to share some basic knowledge with you. I hope to help some new users with linq. If you have any other questions, I can add QQ, thank you for your discussion.
Open door
Before reading this article, let me first talk about how I can monetize each search result set in three ways. In order to better understand it, I hope it will not be too embarrassing.
1. Simple linq syntax
1 //12 var ss = from r in db.Am_recProScheme3 select r;4 //25 var ss1 = db.Am_recProScheme;6 //37 string sssql = "select * from Am_recProScheme";
2. query with where
1 //12 var ss = from r in db.Am_recProScheme3 where r.rpId > 104 select r;5 //26 var ss1 = db.Am_recProScheme.Where(p => p.rpId > 10);7 //38 string sssql = "select * from Am_recProScheme where rpid>10";
3. Simple Function compute (count, min, max, sum)
1 // 1 2 /// obtain the maximum rpId 3 // var ss = (from r in db. am_recProScheme 4 // select r ). max (p => p. rpId); 5 // obtain the minimum rpId 6 // var ss = (from r in db. am_recProScheme 7 // select r ). min (p => p. rpId); 8 // obtain the total number of result sets 9 // var ss = (from r in db. am_recProScheme 10 // select r ). count (); 11 // get the rpId and 12 var ss = (from r in db. am_recProScheme13 select r ). sum (p => p. rpId); 14 15 16 // 217 // 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 // 324 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. sort order by desc/asc
1 var ss = from r in db. am_recProScheme 2 where r. rpId> 10 3 orderby r. rpId descending // Reverse Order 4 // orderby r. rpId ascending // positive sequence 5 select r; 6 7 // Positive Sequence 8 var ss1 = db. am_recProScheme.OrderBy (p => p. rpId ). where (p => p. rpId> 10 ). toList (); 9 // 10 var ss2 = db in reverse order. 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 // if the last one is obtained, it can be sorted in reverse order and then set the value to 2 var ss = (from r in db. am_recProScheme 3 select r ). firstOrDefault (); 4 5 // () linq to ef does not seem to support 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. Skip the previous data entry to retrieve the remaining data.
1 // 12 var ss = (from r in db. am_recProScheme3 orderby r. rpId descending4 select r ). skip (10); // Skip the first 10 data records, and retrieve all data after 10. 5 // 2 6 var ss1 = db. am_recProScheme.OrderByDescending (p => p. rpId ). skip (10 ). toList (); 7 // 38 string sssql = "select * from (select ROW_NUMBER () over (order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum> 10 ";
7. querying paging data
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); // get 11th to 20th data records 6 7 // 2 Take (10): Get the data from the beginning and get the specified number (10) continuous Data 8 var ss1 = db. am_recProScheme.OrderByDescending (p => p. rpId ). where (p => p. rpId> 10 ). skip (10 ). take (10 ). toList (); 9 // 310 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. Include, similar to like '%'
1 // 12 var ss = from r in db. am_recProScheme3 where r. sortsText. contains ("sheets") 4 select r; 5 // 26 var ss1 = db. am_recProScheme.Where (p => p. sortsText. contains ("Zhang ")). toList (); 7 // 38 string sssql = "select * from Am_recProScheme where SortsText like '% sheets % '";
9. group
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, // The Key is recType 8 rpId = n. sum (r => r. rpId), // The sum of the rpId in the group 9 MaxRpId = n. max (r => r. rpId), // The maximum rpId10 MinRpId in the group = n. min (r => r. rpId), // the smallest rpId11}; 12 foreach (var t in ss) 13 {14 Response. write (t. key + "--" + t. rpId + "--" + t. maxRpId + "--" + t. minRpId); 15} 16 // 217 var ss1 = from r in db. am_recProScheme18 orderby r. rpId descending19 group r by r. recType into n20 select n; 21 foreach (var t in ss1) 22 {23 Response. write (t. key + "--" + t. min (p => p. rpId); 24} 25 // 326 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 // 432 string sssql = "select recType, min (rpId), max (rpId), sum (rpId) from Am_recProScheme group by recType ";
10. Connection Query
1 //12 var ss = from r in db.Am_recProScheme3 join w in db.Am_Test_Result on r.rpId equals w.rsId4 orderby r.rpId descending5 select r;6 //27 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 //39 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. In SQL
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 //210 string st = "select * from Am_recProScheme where rpId in(24,25,26)";
If you are a beginner in linq, we recommend that you learn the ten commonly used query tags and add a view to solve most of the query problems.