Details on the usage of LinQ to SQL and the usage of linqsql

Details on the usage of LinQ to SQL and the usage of linqsql

LinQ refers to the Integrated Query Language. By ing, the table name in the database is changed to the class name of C #, the column name is used as the attribute name, and the table relationship is used as the member object of the class. O--M--R

O-Object
R-Relation relationship
M-Mapping

LinQ includes: LinQ to SQL. It is an ORM tool with a visualized operation interface for SQL queries.

This function is used to query pooled objects in memory.

To query strongly typed or weakly typed DataSet or independent DataTable.

LinQ to Entity: queries the Entity set defined by EDM in the object framework.

(1): LinQ to SQL

Step 1: Create a LinQ2SQL class

Add a new item and select "LinQ to SQL" to drag the tables used.

Step 2: instantiate the context object. Basically all operations are performed through the context object.

MyDBDataContext context = new MyDBDataContext();

Step 3: perform operations

1. Add data:

1. Create a data object to be added:

Info data = new Info (); data. code = "p211"; data. name = "Stone"; data. sex = false; data. nation = "N001"; data. birthday = new DateTime (1990, 1, 2 );

2. register the newly created object in the context.

context.Info.InsertOnSubmit(data);

3. Submit for execution

context.SubmitChanges();

Ii. Delete:

1. Find the data to be deleted

MyDBDataContext context = new MyDBDataContext();var q = context.Info.Where(p => p.Code == "p003");

2. Delete Registration

If (q. count ()> 0) {Info data = q. first (); // register context. work. deleteAllOnSubmit (data. work); context. family. deleteAllOnSubmit (data. family); context. info. deleteOnSubmit (data );}

3. Submit for execution

context.SubmitChanges();

3. modification:

1. Find the data to be modified

MyDBDataContext context = new MyDBDataContext();var q = context.Info.Where(p=>p.Code == "p001");

2. Change, no need to register

If (q. count ()> 0) {Info data = q. first (); // modify data. name = "Hu Sheng"; data. nation = "n001"; data. sex = false ;}

3. Submit for execution

context.SubmitChanges();

4. Query:

1. Check all

MyDBDataContext context = new MyDBDataContext (); // var q = context for all personnel. info; // var q = from p in context. info select p; // display foreach (Info data in q) {// data. nation1: The National object corresponding to the current person. Console. writeLine (data. name + "\ t" + data. nation1.Name); // data. work: foreach (Work in data), a set of work records for the current employee. work) {Console. writeLine ("\ t" + work. firm + "\ t" + work. depart );}}

2. query based on the primary key (condition query). By default, the returned data is a set.

Var q = from p in context. info where p. code = "p211" select p; var q = context. info. where (p => p. code = "p211 "). where (p => p. nation1.Name = "Han"); // Lambda expression (the most simplified function is the anonymous function of the parameter and function body) var q = context. info. where (p => p. code = "p211" & p. nation1.Name = "Han"); var q = context. info. where (p => p. code = "p211"); // The if (q. count ()> 0) // check whether data is found in the collection {Info data = q. first (); // get the First object to the Console. writeLine (data. nation1.Name + data. name );}

3. Multi-condition Query

var q = from p in context.Car where p.Price > 30 && p.Brand=="b002" select p;var q = context.Car.Where(p => p.Price > 30 && p.Brand == "b002");var q = context.Car.Where(p => p.Price > 30).Where(p => p.Brand == "b002");var q = from p in context.Car where p.Price > 30 || p.Brand == "b002" select p;var q = context.Car.Where(p => p.Price > 30 || p.Brand == "b002");

4. Fuzzy search

Var q = from p in context. car where p. name. contains ("5") select p; // Contains var q = from p in context. car where p. name. startsWith ("Audi") select p; // start with var q = from p in context. car where p. name. endsWith ("Audi") select p; // end var q = context. car. where (p => p. name. contains ("5"); // Contains var q = context. car. where (p => p. name. startsWith ("Audi"); // start with var q = context. car. where (p => p. name. endsWith ("type"); // end var q = from p in context. car where p. name. substring (2, 1) = "5" select p; // The third character var q = context. car. where (p => p. name. substring (2, 1) = "5"); // The third character is 5

5. Distinct Query

Var q = (from p in context. car select p. brand ). distinct (); // deduplicated var q = context. car. select (p => p. brand ). distinct (); // deduplication

6. Connection query-links between objects.

Var q = context. Car. Where (p => p. Brand1.Productor. Prod_Name = "faw Toyota ");

7. deduplicate query and display. IEnumerable is the root type of the set.

static void Show(IEnumerable<string> q){    foreach (string d in q)    {        Console.WriteLine(d);    }}

8. Statistics Query

MyDBDataContext context = new MyDBDataContext (); // var query = from p in context. car select p; var query = context. car; // locate all consoles. writeLine (query. count (); // Count Console. writeLine (query. max (p => p. price); // the maximum Price of the Console. writeLine (query. min (p => p. price); // The minimum Price of the Console. writeLine (query. max (p => p. price * p. oil); // multiply the price by the maximum fuel consumption. writeLine (query. sum (p => p. price); // Price sum Console. writeLine (query. sum (p => p. price * p. oil); // The price is multiplied by the fuel consumption and summed to the Console. writeLine (query. average (p => p. price); // average Price

9. Paging Query

// Pagination MyDBDataContext context = new MyDBDataContext (); var query = context. car. skip (2*4 ). take (4); // four data entries on each page. to query the four data entries on the third page, skip the eight entries and Take the following four Show (query) records );

10. Sort Query

// Sort MyDBDataContext context = new MyDBDataContext (); // var query = context. car. orderBy (p => p. price); // ascending var query = context. car. orderByDescending (p => p. price); // Show (query) in descending order );

11. Set Operations

// Set MyDBDataContext context = new MyDBDataContext (); var query1 = context. car. where (p => p. brand1.Productor. prod_Name = "faw Toyota"); var query2 = context. car. where (p => p. price> 30); // intersection var query = query1.Intersect (query2); // Union // var query = query1.Union (query2 ); // difference set // var query = query1.t T (query2); // complement set // var query = context. car. counter T (query1); Show (query );

The above are some basic usage of LINQ. It is very convenient to use and has powerful functions and is very useful.