LinQ to SQL

LINQ refers to the Integrated Query language, which transforms the table names in the database into the class names of C # by mapping, the column names as the property names, and the relationship of the tables as the member objects of the class. O--m--r

O-object Object
R-relation relationship
M-mapping Mapping

LINQ includes: LINQ to SQL, a query for SQL, which is an ORM tool with a visual operator interface.

A LINQ to object that implements a query to an in-memory collection object.

LinQ to DataSet, queries a strongly typed or weakly typed DataSet or a stand-alone DataTable.

LinQ to entity, queries the entity collection defined by the EDM in the Entity Framework.

(i): LinQ to SQL

First step: Establish Linq2sql class

Add a new item, select LINQ to SQL, and drag the table you used into.

The second step: Instantiate the context object, and all of the basic operations are done through the context object.

New Mydbdatacontext ();

Step Three: Operation

First, Increase:

1. Build the object.

            New Info ();             " p211 " ;             "Stone " ;             false ;             " N001 " ;             New DateTime (199012);

2. Register the newly created object in the context.

            Context.Info.InsertOnSubmit (data);

3. Submit

            Context. SubmitChanges ();

Second, delete:

1. Find out

            New Mydbdatacontext ();             var " p003 ");

2. Registration

            if 0 )            {                = q.first ();                 // Register                 context. Work.deleteallonsubmit (data. work);                Context. Family.deleteallonsubmit (data. Family);                Context.Info.DeleteOnSubmit (data);

3. Submit

                Context. SubmitChanges ();

Third, change:

1. Find out

            New Mydbdatacontext ();             var " P001 ");

2. Change, do not register

            if 0 )            {                = q.first ();                 // Change                " Mr. Hu " ;                 " n001 " ;                 false;

3. Submit

           Context. SubmitChanges ();

Four, check:

1. Check All

Mydbdatacontext context =NewMydbdatacontext (); //All Personnel            varQ =context. Info;
var q = from P in context.            Info select P; //Show            foreach(Info datainchq) {//data. Nation1: The national object that the current person corresponds to. Console.WriteLine (data. name+"\ t"+data.                Nation1.name); //data. Work: A collection of working records for the current person                foreach(Work workinchdata. Work) {Console.WriteLine ("\ t"+work. firm+"\ t"+Work .                Depart); }            }

2. Depending on the primary key query (conditional query), the default is to return the collection.

varQ = fromPinchContext. InfowhereP.code = ="p211" Selectp;varQ = context.Info.Where (p = = P.code = ="p211"). Where (p = = P.nation1.name = ="Han");//lambda expressions (most simplified function, only parameters and anonymous functions of the function body)varQ = context.Info.Where (p = = P.code = ="p211"&& p.nation1.name=="Han");

var " p211 " // the default is to return the collection if 0)    // See if the data is in the collection. {    // Take the first object out of the    Console.WriteLine (data. Nation1.name + data. Name);}

3. Multi-Criteria Query

varQ = fromPinchContext. CarwhereP.price > -&& p.brand=="b002" Selectp;varQ = Context. Car.where (p = p.price > -&& P.brand = ="b002");varQ = Context. Car.where (p = p.price > -). Where (p = = P.brand = ="b002");varQ = fromPinchContext. CarwhereP.price > -|| P.brand = ="b002" Selectp;varQ = Context. Car.where (p = p.price > -|| P.brand = ="b002");

4. Fuzzy query

varQ = fromPinchContext. CarwhereP.name.contains ("5")SelectP//includevarQ = fromPinchContext. CarwhereP.name.startswith ("Audi")SelectP//StartvarQ = fromPinchContext. CarwhereP.name.endswith ("Audi")SelectP//EndvarQ = Context. Car.where (p = p.name.contains ("5"));//includevarQ = Context. Car.where (p = p.name.startswith ("Audi"));//StartvarQ = Context. Car.where (p = p.name.endswith ("type"));//EndvarQ = fromPinchContext. CarwhereP.name.substring (2,1) =="5" SelectP//a third charactervarQ = Context. Car.where (p = p.name.substring (2,1) =="5");//The third character is a 5

5. Distinct Query

var q = (from inselect p.brand). Distinct (); // Go heavy var q = context. Car.select (P=>p.brand). Distinct (); // Go heavy

6. Connection query--the association between objects is a point.

var q = context. Car.where (p = p.brand1.productor.prod_name==" faw Toyota ");

7. Go to re-check out the display, IEnumerable is the root type of the collection

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

8. Statistical queries

Mydbdatacontext context =NewMydbdatacontext (); //var query = from p in context. Car select P;            varQuery = context. Car;//Find out allConsole.WriteLine (query. Count ());//Number of statisticsConsole.WriteLine (query. Max (P = p.price));//the maximum value of the priceConsole.WriteLine (query. Min (P = p.price));//minimum value of the priceConsole.WriteLine (query. Max (p = p.price * p.oil));//price times the maximum of fuel consumptionConsole.WriteLine (query. Sum (P = p.price));//Price SummationConsole.WriteLine (query. Sum (p = p.price * p.oil));//The price times the fuel consumption and then sumsConsole.WriteLine (query. Average (P = p.price));//Price Average

9. Paging

            // page out            New Mydbdatacontext ();             var query = context. Car.skip (24). Take (4); // 4 data per page, to check the third page of 4 data, skip 8 to take the following 4            Show (query);

10. Sorting

            // Sort            New Mydbdatacontext ();             //  // ascending            var query = context. Car.orderbydescending (p = p.price); // Descending            Show (query);

11. Collection Operations

            //Collection OperationsMydbdatacontext context =NewMydbdatacontext (); varQuery1 = context. Car.where (p = p.brand1.productor.prod_name = ="FAW Toyota"); varQuery2 = context. Car.where (p = p.price > -); //intersection            varquery =Query1.            Intersect (Query2); //and set//var query = Query1.            Union (Query2); //Difference Set//var query = Query1.            Except (Query2); //complement set//var query = context. Car.except (Query1);Show (query);

LinQ to SQL