LinQ to SQL queries

LINQ to SQL is the mapping of object relationships to. The implementation of the NET Framework. It can map a relational database to some classes in the. NET framework. The developer can then query, modify, insert, delete, and so on in the database by using LINQ to SQL.

LINQ to SQL Classes map to tables in SQL Server databases, which are called "entity classes", properties or fields in entity classes map to columns in a SQL Server database, associations between entity classes map to foreign key relationships in SQL Server databases, LINQ to The methods in the SQL class map to stored procedures or functions in the SQL database. The following table maps the LINQ to SQL object model and the objects in the SQL Server database.

Add the LINQ to SQL class first xxxxdb.dbml remember your name opens in App_Code xxxxdb.dbml Drag the table you want to manipulate

Create a DBML file that contains the *.dbml.layout file and the *.designer.cs file. Where *.dbml.layout saves the layout of the dbml file. The *designer.cs file is the code for the dbml file created by the visual Studio 2008 integrated development Environment Call code generation tool Sqlmetail.

DataContext is called a data context, which provides a gateway to the operational database for LINQ to SQL, and if you use LINQ to SQL to manipulate the database, you first need to create a custom data context class for the database that inherits from the DataContext class and define the table in that class. And how to manipulate the data.

The DataContext class acts as a pipe between a SQL Server database and a LINQ to SQL entity class that is mapped to that database, and it contains connection string information and methods for connecting to the database and manipulating database data. The DataContext class can map the source of all entities in a database through a database connection or connection string, and track and identify user changes to the database. Users can call their SubmitChanges () method to commit all changes to the database.

Simple query

New Northwinddbdatacontext ();   //Add LINQ to SQL class What's the name of this          ? var  from inch DC. Products                     where a.unitprice.getvalueordefault () <ten                     select  A;        //linq Query statement  to find data with a price less than 10  = result;        Gridview1.databind ();

Federated queries

Linqdbdatacontext db =NewLinqdbdatacontext (linqsystem.linqdbconnectionstring); varresult = fromUserinchdb. UserInfo join urinchdb. userrole on user.id equals Ur. UserID Join Roleinchdb. Role on Ur. Roleid equals Role.idwhereUser.ID < $                     Select New{ID= User.ID,//display ID, user name, e-mail address and roleUserName =user. Username, Email=user. Email, RoleName=role.        RoleName}; Dl_list.datasource=result; Dl_list.databind ();

* * Group queries and aggregate queries

From the p in DC. Products
Group P (grouped objects) by P.categoryid (group basis) into G //g inside is the set of P G.key=p.categoryid

Select new{} //show what to write here

Northwinddbdatacontext DC =NewNorthwinddbdatacontext (); //according to the product ID to get the most expensive product price and the lowest product price        varresult = fromPinchDC. Products group P by P.categoryid to GSelect New     //mapping to a new anonymous object needs to show what's inside to generate what{Cateid=g.key,//By and what G.key is whatMax=g.max ((x) =>x.unitprice),//aggregate function Maximum valueMin=g.min ((x) =>x.unitprice)//aggregate function Minimum value                     }; //get the total number of products in each category, and show the category name, Total, Total price        varRESULT1 = fromPinchDC. Products Join CinchDC. Categories on P.categoryid equals C.categoryid groupNew{p = p, c = c} by C.categoryname to G//new {p = p, c = c} adds the required objects to the anonymous object                      Select New{catename=G.key, Count=g.count (),//TotalSum=g.sum ((x) =>x.p.unitprice)//Request a Total price                      }; //get the most expensive product price and product name in each category        varRESULT2 = fromPinchDC. Products Join CinchDC. Categories on P.categoryid equals C.categoryid groupNew{p = p, c =C} by C.categoryname to GSelect New{Max=g.max ((x) =x.p.unitprice), Catename=G.key, ProductName=( fromUinchDC. ProductswhereU.unitprice==g.max ((x) =>x.p.unitprice)//conditions are used to filter the product name, only the most expensive one                                          SelectU.productname//projection a product name map as needed the product name is required to map only the product name). First ()//returns the first element in a sequence                      }; Gridview1.datasource=result2; Gridview1.databind ();

LinQ to SQL queries