Linq Learning (2)-LinQ to Entity

I learned some basic knowledge about LinQ yesterday and wrote some examples of LinQ to Object. I have some knowledge about the basic syntax and key points of LinQ. I will continue my learning today. I will summarize some of the topics that I learned today, such as LinQ to DataSet and LinQ to Entity. On the one hand, I will deepen my understanding, and on the other hand, I will be able to master the implementation mechanism of the LinQ technology, it is also good to have a preliminary perceptual knowledge if you are interested in one aspect like me.

Today, I will explain the C # implementation mechanism of LinQ to Entity and solve the two questions after I read the section yesterday. I will take a moment to introduce the content of the section "LinQ to DataSet, now, the specific introduction to LinQ is basically complete.

After reading the entire chapter, I will summarize the implementation mechanism of the LinQ to Entity, which can be roughly described in the following figure:

EntityFrameWork: it is an object-link ing system that plays a crucial role in object Conversion Between Relational databases and c # source code environments, it is estimated that the object cannot be found without its LinQ to Entity, hahaha. It can map all databases and data objects in a relational database to the Context Environment of LinQ to Entity and map them into an ObjectContext class-database object; and the corresponding ObjectSet object-the corresponding database object (table, view, and stored procedure). Then, we can access data and perform data operations like the operation object through the previously learned LinQ expression, the principle is that in relational databases, tables, rows, and fields can all be mapped to objects. This may be the essence of LinQ to Entity.

In combination with the above explanation, we can better understand the following simple frame analysis diagram, which is a bit ugly and understandable:

[1] First, we need to generate a data model, which is the prerequisite for accessing database objects in the source code environment:

Entity FrameWork relies on the database data model to use the LinQ query. the rows in the table are converted to the row object instance, and the columns of each record are converted to the attributes of the row object. The data model of the database can be automatically generated through Visual Studio, or you can manually create a ing by adding a data model file. In this example, VS is used to generate a ing, which is simple and error-prone.

Select generate from database to go to the following page:

Click to continue:

After the connection passes the test, click OK to generate a connection string. In general, [Yes] is selected as the next option by default:

Next, this is an important step: we can see that several dll references are automatically added at the moment, and we can see that the relationships between tables in the database are displayed at a glance, therefore, this is also a magical place for LinQ to Entity.

Based on the preceding intuitive Data Model diagram, we will describe its architecture here: the system was restored, leading to the loss of Viso on the computer. Therefore, we cannot describe it using structured images, let's make it easy to understand. Haha

The data model of a Database: (1) context class of the derived object, that is, the previously mentioned ObjecgContext, which can be understood as a source code ing of the database.

(2) object class: The ObjectSet we mentioned above is the source code ing of the tables, views, and stored procedures that we just selected to map to the program.

(2.1) the object relationship is similar to the table relationship in SQL: there is a one-to-one and one-to-many relationship, and Its Association is associated with the [foreign table key] through the navigation attribute shown above]

[2] Now, you can try to access our database through LinQ to Entity.

// The following fields in the customer table are displayed on the front end. In today's example, the data in the system database northWind is used.
<Asp: GridView ID = "GridView1" runat = "server" AutoGenerateColumns = "false"> <Columns> <asp: boundField DataField = "id" HeaderText = "Customer ID"/> <asp: BoundField DataField = "name" HeaderText = "customer name"/> <asp: boundField DataField = "city" HeaderText = "city"/> <asp: BoundField DataField = "country" HeaderText = "country"/> </Columns> </asp: GridView>

 

Using System; using System. collections. generic; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; using System. collections; using System. linq; using System. data. objects; // namespace LinQ {public partial class _ Default: System. web. UI. page {protected void Page_Load (object sender, EventArgs e) {// instantiate the database object ObjectContext NorthwindEntities myTestDB = new NorthwindEntities (); var donews = from customer in myTestDB. MERs where customer. country = "USA" select new {id = customer. customerID, name = customer. contactName, city = customer. city, country = customer. country}; GridView1.DataSource = donews; GridView1.DataBind (); // you need to convert the query result to an ObjectQuery instance to track the output SQL statement TextBox1.Text = (donews as ObjectQuery ). toTraceString ();}}}View Code

 

Page display:

SQL statement for conversion:

SELECT 1 AS [C1], [Extent1]. [CustomerID] AS [CustomerID], [Extent1]. [ContactName] AS [ContactName], [Extent1]. [City] AS [City], [Extent1]. [Country] AS [Country] FROM [dbo]. [MERs] AS [Extent1] where n 'usa' = [Extent1]. [Country]View Code

 

2. join query: the front-end file also needs to be modified. It will not be pasted here. Let's take a look at the backend LinQ expression.

The associated query can be implemented through two expressions: let and selectMany extension. This is a demonstration of the let method.

Let mode:

Protected void Page_Load (object sender, EventArgs e) {// instantiate the database object ObjectContext NorthwindEntities myTestDB = new NorthwindEntities (); var donews = from customer in myTestDB. customers // let implementation. I don't know if let is used as the keyword to be consistent with the left Outer Join in SQL. // However, the usage is similar to let leftTab = from order in customer. orders // This is not like the subquery and group filter expressions in SQL, haha ...... It is too similar to select order where customer. country = "USA" select new {id = customer. customerID, name = customer. contactName, city = customer. city, country = customer. country, orderCnt = leftTab. count ()}; GridView1.DataSource = donews; GridView1.DataBind (); // you need to convert the query result to an ObjectQuery instance to trace the output SQL statement TextBox1.Text = (donews as ObjectQuery ). toTraceString ();}View Code

 

SQL statement for conversion:

SELECT 1 AS [C1], [Project1]. [CustomerID] AS [CustomerID], [Project1]. [ContactName] AS [ContactName], [Project1]. [City] AS [City], [Project1]. [Country] AS [Country], [Project1]. [C1] AS [C2] FROM (SELECT [Extent1]. [CustomerID] AS [CustomerID], [Extent1]. [ContactName] AS [ContactName], [Extent1]. [City] AS [City], [Extent1]. [Country] AS [Country], (select count (1) AS [A1] FROM [dbo]. [Orders] AS [Extent2] WHERE [Extent1]. [CustomerID] = [Extent2]. [CustomerID]) AS [C1] FROM [dbo]. [MERs] AS [Extent1] where n 'usa' = [Extent1]. [Country]) AS [Project1]View Code

 

// Insert a single table. Create a record in the new instantiation method and insert the updated public void insertSignlData () {// instantiate the database object ObjectContext NorthwindEntities myTestDB = new NorthwindEntities (); customer myCust = new Customer () {CustomerID = "george_liyk", ContactName = "", City = "Wuhan ", country = "USA" // For the previous data filtering, it can be displayed on the page}; // submit the object data to save and execute the update myTestDB. MERs. addObject (myCust);} // insert a single table: Creat [T] Call the constructor of each table object to create a record and insert and update public void insertSignlData () {// instantiate the database object ObjectContext NorthwindEntities myTestDB = new NorthwindEntities (); Customer mycust = Customer. createCustomer ("george", ""); mycust. city = "Beijing"; mycust. country = "USA"; // submit the object data to save and execute the update myTestDB. MERs. addObject (mycust );}View Code

 

There are also two ways to insert data in the joined table object: one is to instantiate the related field values of the two table objects (just insert multiple times in the same single table as above ), one is to initialize its navigation property page while instantiating a table object. The following code demonstrates the second method.

// Join table data insertion method-public void insertRelatData () {// instantiate the database object ObjectContext NorthwindEntities myTestDB = new NorthwindEntities (); Customer myCust = new Customer () {CustomerID = "lio", ContactName = "Join table multi-Table Insert", City = "Wuhan", Country = "USA", // used to filter the preceding data, you can see Orders = {new Order {CustomerID = "lio" // because the id in the order table is not required to be filled in, the remaining fields are not mentioned here }}}; // submit the object data to save and execute the update myTestDB. MERs. addObject (myCust );}View Code

 

4. Table update:

// Replace the inserted customer name [no sub-accounts] with [hahaha] public void updateData () {// instantiate the database object ObjectContext NorthwindEntities myTestDB = new NorthwindEntities (); customer data = (from customer in myTestDB. MERs where customer. customerID = "george" select customer ). single (); data. contactName = "HAHAHA"; // submit data to update myTestDB. saveChanges ();}View Code

 

5. Table deletion:

// Delete the george record public void deletData () {// instantiate the database object ObjectContext NorthwindEntities myTestDB = new NorthwindEntities (); Customer george = (from cust in myTestDB. customers where cust. customerID = "george" select cust ). single (); // Delete the myTestDB record in the memory object. MERs. deleteObject (george); // submit the updated data from the memory to the database to update myTestDB. saveChanges ();}View Code

 

 

Follow-up: The above page shows because the image has to be delayed for a long time, so there is no one here. You can also understand that this time the content has not been written yet ....

The remaining content will be added tomorrow. It was too late yesterday. I had to go to bed early today...

[Next content]: Concurrent management of LinQ to Entity

Implementation of LinQ to DataSet

Related entitysponse controls