LINQ to Entities is the most attractive part of LINQ.It allows you to use standard C # objects to deal with database structures and data. When using LINQ to Entities, the LINQ query is converted to an SQL query in the background and executed when data is required, that is, the result of enumeration is started.LINQ to Entities also provides change tracking for all the data you get, that is, you can modify the objects obtained by the query, and then submit the update to the database in the whole batch.
LINQ to Entities is part of the Entity Framework and replaces LINQ to SQL as the standard mechanism for using LINQ on the database. Entity Framework is an industry-leading object-relational ing (ORM) system. It can be used with multiple databases and supports various flexible and complex data models.
Note:
Microsoft has moved the focus of development from LINQ to SQL to LINQ to Entities, and announced that no updates will be provided for LINQ to SQL. Currently, this function is not recommended.
LINQ to Entities is an impressive technology, but it is only a small improvement for most developers. Like DataSet,The query features of ASP. NET developers are far more advanced than those of LINQ. This is because updates to Web applications are usually Updated at a time rather than in batches. They prefer to execute updates immediately upon page sending back, and get the original and new (update) values, which makes it easier to submit updates through the ADO. NET command.
In short, LINQ to Entities does not provide any features that cannot be implemented using ADO. NET code, custom Objects, and LINQ to Objects. But sometimes, for some reason, you need to consider using LINQ to Entities:
Generate data model
Entity Framework relies on a data model to use LINQ to Entities for queries. The rows in the table are converted to instances of C # objects, and the columns in the table are the attributes of these objects.The ing between database architecture and data model objects is the core of Entity Framework.
To generate a model, right-click the App_Code directory, and click "add new item" and "ADO. NET object data model ", set the name of the created File (here is the NorthwindModel. edmx), and click OK ".
Generate a model from an existing database, that is, Microsoft's Northwind sample database. Configure database connections and select tables, views, and stored procedures. You can also choose whether to use the complex or singular object name (for example, the row in the Products Table is named "Product") and whether the table contains foreign key relationships. Select all tables and select "the singular and plural form of the generated object ".
Visual Studio creates a model chart for the selected database elements. It displays the created ing objects, fields owned by the objects, and relationships between objects.
The following two files are added to the project:
Data Model
We will spend most of our timeNorthwindModel. Designer. csFile. Because it contains the data type that we want to use for the LINQ to Entities query. (This file will be re-generated by the data model. Therefore, you do not need to manually modify this file, and your modifications will be lost.)
Open the file and you can see two sections of code: Contexts and Entities.
1. Derived object context
NorthwindModel. Designer. csThe first class defined in the file is derived from ObjectContext and its name is NorthwindEntities. The constructor of this class is connected to the database of the generated model, or you can specify the connection string to connect to other databases (the same architecture must be available, otherwise the model cannot work ).
The following is a simple example:
Protected void Page_Load (object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities ();
GridView1.DataSource = db. Products;
GridView1.DataBind ();
}
2. Entity class
The entity class is used to map records of database tables to C # objects. If the option "determining the complex form of the generated object" is selected, the name of the object created in a table like Products is Product.
Each object contains the following content:
Tip:
The entity class is declared as a partial class, so you can create an extension function that will not be overwritten when the data model is regenerated.
Example:
Protected void Page_Load (object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities ();
Var result = from p in db. Products
Where p. Discontinued = false
Select new
{
ID = p. ProductID,
Name = p. ProductName
};
GridView1.DataSource = result;
GridView1.DataBind ();
}
The product with ID = 5 does not meet the criteria and is filtered out.
Entity Relationship
The object class contains navigation attributes. You can move data models without considering foreign key relationships by using navigation properties. See the following example:
Protected void Page_Load (object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities ();
Var result = from c in db. MERS
Let o = from q in c. Orders
Where q. Employee. LastName! = "King"
Select q
Where c. City = "London" & o. Count ()> 5
Select new
{
Name = c. CompanyName,
Contact = c. ContactName,
OrderCount = o. Count ()
};
GridView1.DataSource = result;
GridView1.DataBind ();
}
This query uses the Orders navigation attribute to query all Orders associated with the Customer. We use the Order object type's Employee navigation attribute to check the Employee's last name and filter out the data whose last name is equal to "King.
You can navigate between data models without having to create separate queries for each object class by using navigation properties.
1. One-to-multiple relationship
The navigation properties of one-to-multiple relationships are processed by a strongly typed EntityCollection. You don't need to worry about selecting a suitable record for a link. It is already handled by a foreign key link. Therefore, when selecting a user's Order, only the Order instances with the same CustomerID value and the same CustomerID attribute value of the Customer are obtained.
You can use the selectties extension method to perform a LINQ to Entities query. You can directly use the EntityCollection class as the query result. It will include all matching results in the result set.
Example:
Protected void Page_Load (object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities ();
IEnumerable <Order> orders = db. MERS
// Locate the LAZYK customer
. Where (c => c. CustomerID = "LAZYK ")
// The preceding customer result set is accurate. Navigate to the customer's Orders attribute to obtain all Orders.
. Selectqueues (c => c. Orders );
GridView1.DataSource = orders;
GridView1.DataBind ();
}
You can also rewrite it to an implicit LINQ expression to get the same result:
Var result = from o in db. Orders
Where o. CustomerID = "LAZYK"
Select o;
2. One-to-one relationship
For a one-to-one relationship, there are two navigation attributes.
Query stored procedures
In solution explorer, double-clickNorthwindModel. edmxFile, open the data model diagram, right-click and select "update from database" to import the stored procedure. Open the "object data model Browser" (you can find it in the "View"> "others" menu) and expand the NorthwindModel. store node, open the stored procedure directory, and you will see the list of stored procedures in the import model.
Select a stored procedure, right-click to add a function for import, and select "create a new complex type" on the import interface. Then you can use it as follows:
Protected void Page_Load (object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities ();
IEnumerable <ESBC_Result> results
= From c in db. ESBC (DateTime. Now. AddYears (-20), DateTime. Now)
Select c;
GridView1.DataSource = results;
GridView1.DataBind ();
}
In this case, the type name returned is the name of the previously selected custom complex type.
How to search by using LINQ to Entities
The previously demonstrated usage of LINQ to Entities is almost the same as that of LINQ to Objects. This is true (at least on the surface ).One of the best thing about LINQ is that it maintains a high degree of consistency with various data sources. If you know how to use basic LINQ query, you can use it to query objects, databases, XML, and so on.
The disadvantage is that this similarity comes from hiding a lot of complexity. If you are not careful, the database will be overloaded. You should take the time to check what SQL queries are generated in order to serve your LINQ to Entities queries.It is not easy to use Entity Framework to view SQL queries. You need to convert the results of the LINQ to Entities query to an instance of System. Data. Objects. ObjectQuery and call the ToTraceString () method.
Example:
Protected void Page_Load (object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities ();
Var result = from c in db. MERS
Let o = from q in c. Orders
Where q. Employee. LastName! = "King"
Select q
Where c. City = "London" & o. Count ()> 5
Select new
{
Name = c. CompanyName,
Contact = c. ContactName,
OrderCount = o. Count ()
};
Label1.Text = (result as System. Data. Objects. ObjectQuery). ToTraceString ();
}
In many cases, printing SQL queries like this is not realistic. If you are using a non-Express version of SQL Server, you can use the SQL Server Profile tool. For the Express version, we recommend that you use the free SQL Profile developed by Anjlab, which is good.
1. Late filtering
A common cause for unnecessary database queries is that it is too late to filter the data in the query. This is an example of a query:
NorthwindEntities db = new NorthwindEntities ();
IEnumerable <NorthwindModel. Customer> custs
= From c in db. MERS
Where c. Country = "UK"
Select c;
IEnumerable <NorthwindModel. Customer> results
= From c in custs
Where c. City = "London"
Select c;
GridView1.DataSource = results;
GridView1.DataBind ();
The problem here is that the first query retrieves all records of Country = UK from the database. The second query is applied to the results of the first query, but it uses LINQ to Objects, that is to say, we discard the vast majority of data requested from the database (the first query is a waste of resources ).
In this example, only one SQL query is generated, which is similar to the following:
SELECT * from mers where Country = 'UK'
The solution is to integrate the filter into the same query.
2. Use delayed and greedy data for loading
To make navigation attributes work seamlesslyDelayed loadingTo load data from the database only when necessary.When the navigation attribute is transferred from an object type to another object type, the instance of the second object type is loaded only when necessary.
Protected void Page_Load (object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities ();
IEnumerable <NorthwindModel. Customer> custs
= From c in db. MERS
Where c. Country = "UK" & c. City = "London"
Select c;
List <string> names = new List <string> ();
Foreach (NorthwindModel. Customer c in custs)
{
If (c. Orders. Count> 2)
{
Names. Add (c. CompanyName );
}
}
GridView1.DataSource = names;
GridView1.DataBind ();
}
In this query, we filter out a group of MERS, iterate the results, and navigate to the relevant Order instance. Finally, we get the name of the company that is located in London, UK and has two additional orders.
Due to delayed loading, the data in the Orders table is loaded only when needed. That is to say, to obtain the order associated with each customer in a loop, an SQL query is generated. This produces too many queries. For this simple example, we can integrate all this into a LINQ query.
But what we want to demonstrate is the greedy loading function. It can load the associated data of other tables in the query. Example:
Protected void Page_Load (object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities ();
IEnumerable <NorthwindModel. Customer> custs
= From c in db. MERS.Include ("Orders ")
Where c. Country = "UK" & c. City = "London"
Select c;
List <string> names = new List <string> ();
Foreach (NorthwindModel. Customer c in custs)
{
If (c. Orders. Count> 2)
{
Names. Add (c. CompanyName );
}
}
GridView1.DataSource = names;
GridView1.DataBind ();
}
The Include () extension method is used to Include the associated data. It tells the LINQ to Entities engine that the Order instance associated with the Customer we query should be loaded, even if the query is not directly associated with the Orders table. Finally, Entity Framework captures the results. That is to say, when we iterate the Customer instance and check the associated Orders, they are all loaded and no additional database queries need to be generated.
3. Use explicit loading
To fully control the loaded data, you can use explicit loading. You can use the derived ObejectContext class to disable delayed loading, and then use the EntityCollection. Load () method to Load data as needed. You can use the IsLoaded method to check whether the required data has been loaded.
Protected void Page_Load (object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities ();
Db. ContextOptions. LazyLoadingEnabled = false;
IEnumerable <NorthwindModel. Customer> custs
= From c in db. MERS
Where c. Country = "UK"
Select c;
Foreach (NorthwindModel. Customer c in custs)
{
If (c. City = "London ")
{
C. Orders. Load ();
}
}
List <Order> orders = new List <Order> ();
Foreach (NorthwindModel. Customer c in custs)
{
If (C. Orders. IsLoaded)
{
Orders. Add (C. Orders. First ());
}
}
GridView1.DataSource = orders;
GridView1.DataBind ();
}
This example is not natural, but it is enough to show you the knowledge needed to use explicit loading in the project.
4. Compile and query
Another hidden function of LINQ to Entities is to create compiled queries. A compiled query is a strongly typed Func delegate that has a parameter for query. When compiling a query, execute the translation to the SQL statement, and reuse the compiled query each time.
This is not as efficient as using stored procedures, because the database still needs to create a query plan to execute the SQL statement, but it does avoid the repeated resolution of the LINQ query by The LINQ to Entities.
Example:
Using System. Data. Objects;
Public partial class Chapter13_DerivedObjectContext: System. Web. UI. Page
{
// Encapsulate a method that has two parameters and returns the type value specified by the TResult parameter.
Func <NorthwindEntities, string, IQueryable <NorthwindModel. Customer> MyCompiledQuery;
NorthwindEntities db;
Protected void Page_Load (object sender, EventArgs e)
{
// CompiledQuery indicates a cached LINQ to Entities query.
// Compile () creates a new delegate that indicates compiled LINQ to Entities queries.
MyCompiledQuery = CompiledQuery. Compile <NorthwindEntities, string,
IQueryable <NorthwindModel. Customer> (context, city) =>
From c in context. Customers
Where c. City = city
Select c );
Db = new NorthwindEntities ();
GridView1.DataSource = MyCompiledQuery (db, "London ");
GridView1.DataBind ();
}
}
LINQ (LINQ to Entities)
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
