Details of the Linq syntax.

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 this will help some new users with linq. If you have any other questions, you can join the group in the upper-right corner, 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            var ss = from r in db.Am_recProScheme                     select r;            //2            var ss1 = db.Am_recProScheme;            //3            string sssql = "select * from Am_recProScheme";

2. query with where

            //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";

3. Simple Function compute (count, min, max, sum)

// 1 /// obtain the maximum rpId // var ss = (from r in db. am_recProScheme // select r ). max (p => p. rpId); // obtain the minimum rpId // var ss = (from r in db. am_recProScheme // select r ). min (p => p. rpId); // obtain the total number of result sets // var ss = (from r in db. am_recProScheme // select r ). count (); // obtain the sum of rpId and 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 ";

4. sort order by desc/asc

Var ss = from r in db. am_recProScheme where r. rpId> 10 orderby r. rpId descending // Reverse Order // orderby r. rpId ascending // select r in the forward direction; // var ss1 = db in the forward direction. am_recProScheme.OrderBy (p => p. rpId ). where (p => p. rpId> 10 ). toList (); // reverse 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]";

5. top (1)

// If the last one is obtained, it can be sorted in reverse order and then var ss = (from r in db. am_recProScheme select r ). firstOrDefault (); // () linq to ef does not seem to support Last () var ss1 = db. am_recProScheme.FirstOrDefault (); // var ss1 = db. am_recProScheme.First (); string sssql = "select top (1) * from Am_recProScheme ";

6. Skip the previous data entry to retrieve the remaining data.

// 1 var ss = (from r in db. am_recProScheme orderby r. rpId descending select r ). skip (10); // Skip the first 10 data records and retrieve all data after 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 ";

7. querying paging data

// 1 var ss = (from r in db. am_recProScheme where r. rpId> 10 orderby r. rpId descending select r ). skip (10 ). take (10); // fetch 11th to 20th pieces of data // 2 Take (10): Get the data from the beginning and get the specified number (10) continuous Data 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 ";

8. Include, similar to like '%'

// 1 var ss = from r in db. am_recProScheme where r. sortsText. contains ("Zhang") select r; // 2 var ss1 = db. am_recProScheme.Where (p => p. sortsText. contains ("Zhang ")). toList (); // 3 string sssql = "select * from Am_recProScheme where SortsText like '% sheets % '";

9. group

// 1 var ss = from r in db. am_recProScheme orderby r. rpId descending group r by r. recType into n select new {n. key, // The Key is recType rpId = n. sum (r => r. rpId), // The sum of rpId in the group MaxRpId = n. max (r => r. rpId), // The maximum rpId in the group. MinRpId = n. min (r => r. rpId), // the smallest rpId in the group}; 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 ";

10. Connection Query

            //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";

11. In SQL

            //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)";

 

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.

 

Summary: Comparison between the syntax of linq and SQL

Linq is an object-oriented SQL statement. That is to say, SQL queries relational databases, while linq queries data in memory.

Although linq was originally an object query, through the efforts of ms, you can use expressions to analyze the ing between entities and relationships (linq to SQL ), converts a user-defined language (linq to xml) to an SQL statement or an xml query ).

Therefore, this technology becomes a convenient ing, conversion, and operation tool for objects to database records. You no longer have to use String concatenation to generate SQL statements based on different situations, instead, you can focus on the processing of the object model. Your modifications to the object will eventually be converted to the corresponding update, insert, delete, and other SQL statements, all submitted to the database when you submit.

Overall, linq to SQL is an intermediate layer from a database to an object structure. It changes the management of relational data to object operations, blocking the trouble of SQL, we can also get help from vs's powerful smart sensing functions.

Language INtegrated Query is a set of extensions for c # and Visual Basic languages. It allows you to write C # Or Visual Basic code to query memory data in the same way as a database.

Basic Concepts
Technically, LINQ defines about 40 query operators, such as select, from, in, where, and orderby (C. You can use these operations to write query statements. However, these queries can also be based on many types of data. Each data type requires a separate LINQ type. Over the past 20 years, the application of object-oriented programming technology (OO) in the industrial field has entered a stable development stage. Programmers now recognize language features such as classes, objects, and methods. Observing the present and next-generation technologies, a major challenge of new programming technologies has begun to emerge. That is, since the birth of object-oriented technology, it has not solved the problem of reducing access and integrating information (accessing and integrating information) complexity issues. Two of the most accessed data sources are related to databases and XML. A more general approach is provided.. Net Framework adds query facilities that can be applied to all sources of information ), this is a better way than adding relational or XML-like features to the development language and runtime. These syntax features are called. NET Language Integrated Query (LINQ ). Including DLinq and XLinq
Basic knowledge
1. The read method of LINQ: (1) lin k (2) lin q 2. Key words of LINQ: from, select, in, where, group by, order... 3. Note: it must end with select or group. 4. the semantics of LINQ: from temporary variable in collection object or database object where condition expression [order by condition] select Temporary Variable query value [group by condition] the type of return value for the query of LINQ is temporary variable type, it may be an object or a set. In addition, the query expression of LINQ is the last time you created the object... the remaining full text>

In the Linq syntax, what is the difference between the where condition and two?

& It is the bitwise operation "and". From the binary point of view, it means that 00 and are equal to and 0 are equal.
& Is and, which plays a role in sum. It is usually used to connect two conditions. Only when both conditions are True is the result of & is True.