Linq 简明教程

LINQ - Entities

作为 ADO.NET 实体框架的一部分,LINQ to Entities 比 LINQ to SQL 更灵活,但由于其复杂性和缺乏关键特性而不太流行。但是,它没有 LINQ to SQL 的限制,后者仅允许在 SQL Server 数据库中进行数据查询,因为 LINQ to Entities 可以在大量数据提供程序(如 Oracle、MySQL 等)中促进数据查询。

A part of the ADO.NET Entity Framework, LINQ to Entities is more flexible than LINQ to SQL, but is not much popular because of its complexity and lack of key features. However, it does not have the limitations of LINQ to SQL that allows data query only in SQL server database as LINQ to Entities facilitates data query in a large number of data providers like Oracle, MySQL, etc.

此外,它得到了 ASP.Net 的大力支持,这意味着用户可以利用数据源控件通过 LINQ to Entities 执行查询,并促进结果的绑定,而无需任何额外的编码。

Moreover, it has got a major support from ASP.Net in the sense that users can make use of a data source control for executing a query via LINQ to Entities and facilitates binding of the results without any need of extra coding.

LINQ to Entities 凭借这些优势已成为目前面向数据库使用 LINQ 的标准机制。使用 LINQ to Entities 还可以更改查询的数据详细信息,并轻松地提交批处理更新。最吸引人的是,LINQ to Entities 具有与 SQL 相同的语法,甚至拥有相同的标准查询操作符组,如“联接”、“选择”、“按顺序排列”等。

LINQ to Entities has for these advantages become the standard mechanism for the usage of LINQ on databases nowadays. It is also possible with LINQ to Entities to change queried data details and committing a batch update easily. What is the most intriguing fact about LINQ to Entities is that it has same syntax like that of SQL and even has the same group of standard query operators like Join, Select, OrderBy, etc.

LINQ to Entities Query Creation and Execution Process

  1. Construction of an ObjectQuery instance out of an ObjectContext (Entity Connection)

  2. Composing a query either in C# or Visual Basic (VB) by using the newly constructed instance

  3. Conversion of standard query operators of LINQ as well as LINQ expressions into command trees

  4. Executing the query passing any exceptions encountered to the client directly

  5. Returning to the client all the query results

ObjectContext 这里的主要类,可与 Entity Data Model 交互;换句话说,它起到了 LINQ 与数据库之间的桥梁作用。命令树这里是为了与 Entity 框架兼容的查询表示形式。

ObjectContext is here the primary class that enables interaction with Entity Data Model or in other words acts as a bridge that connects LINQ to the database. Command trees are here query representation with compatibility with the Entity framework.

另一方面,Entity Framework 实际上是 Object Relational Mapper ,开发人员通常将其缩写为 ORM,它可以根据数据库表生成业务对象以及实体,并支持各种基本操作,如创建、更新、删除和读取。下图显示了 Entity 框架及其组件。

The Entity Framework, on the other hand, is actually Object Relational Mapper abbreviated generally as ORM by the developers that does the generation of business objects as well as entities as per the database tables and facilitates various basic operations like create, update, delete and read. The following illustration shows the entity framework and its components.

linq entities

Example of ADD, UPDATE, and DELETE using LINQ with Entity Model

首先按照以下步骤添加 Entity 模型。

First add Entity Model by following below steps.

Step 1 − 右键单击项目,然后单击添加新项目,将打开如下图所示的窗口。选择 ADO.NET Entity Data Model,指定名称,然后单击添加。

Step 1 − Right click on project and click add new item will open window as per below. Select ADO.NET Entity Data Model and specify name and click on Add.

linq entities 1

Step 2 − 选择 Generate from database.

Step 2 − Select Generate from database.

linq entities 2

Step 3 − 从下拉菜单中选择数据库连接。

Step 3 − Choose Database Connection from the drop-down menu.

linq entities 3

Step 4 − 选择所有表。

Step 4 − Select all the tables.

linq entities 4

现在编写以下代码。

Now write the following code.

using DataAccess;
using System;
using System.Linq;

namespace LINQTOSQLConsoleApp {
   public class LinqToEntityModel {
      static void Main(string[] args) {

         using (LinqToSQLDBEntities context = new LinqToSQLDBEntities()) {
            //Get the List of Departments from Database
            var departmentList = from d in context.Departments
            select d;

            foreach (var dept in departmentList) {
               Console.WriteLine("Department Id = {0} , Department Name = {1}",
                  dept.DepartmentId, dept.Name);
            }

            //Add new Department
            DataAccess.Department department = new DataAccess.Department();
            department.Name = "Support";

            context.Departments.Add(department);
            context.SaveChanges();

            Console.WriteLine("Department Name = Support is inserted in Database");

            //Update existing Department
            DataAccess.Department updateDepartment = context.Departments.FirstOrDefault(d ⇒d.DepartmentId == 1);
            updateDepartment.Name = "Account updated";
            context.SaveChanges();

            Console.WriteLine("Department Name = Account is updated in Database");

            //Delete existing Department
            DataAccess.Department deleteDepartment = context.Departments.FirstOrDefault(d ⇒d.DepartmentId == 3);
            context.Departments.Remove(deleteDepartment);
            context.SaveChanges();

            Console.WriteLine("Department Name = Pre-Sales is deleted in Database");

            //Get the Updated List of Departments from Database
            departmentList = from d in context.Departments
            select d;

            foreach (var dept in departmentList) {
               Console.WriteLine("Department Id = {0} , Department Name = {1}",
                  dept.DepartmentId, dept.Name);
            }
         }

         Console.WriteLine("\nPress any key to continue.");
         Console.ReadKey();
      }
   }
}

编译并执行上述代码后,将产生以下结果 −

When the above code is compiled and executed, it produces the following result −

linq entities 5