Linq 简明教程

LINQ - SQL

LINQ to SQL 为管理对象关系数据提供一个基础结构（运行时）。它是 .NET Framework 3.5 版本的一个组件，能够将对象模型的语言集成查询翻译成 SQL。然后将这些查询发送到数据库以供执行。在从数据库获取结果后，LINQ to SQL 会再次将结果转换为对象。

LINQ to SQL offers an infrastructure (run-time) for the management of relational data as objects. It is a component of version 3.5 of the .NET Framework and ably does the translation of language-integrated queries of the object model into SQL. These queries are then sent to the database for the purpose of execution. After obtaining the results from the database, LINQ to SQL again translates them to objects.

Introduction of LINQ To SQL

对于大多数的 ASP.NET 开发人员而言，LINQ to SQL（也称为 DLINQ）是语言集成查询中令人振奋的部分，因为它可以通过使用通常的 LINQ 表达式来查询 SQL 服务器数据库。它还允许更新、删除和插入数据，但它所遭受的唯一缺点是它仅限于 SQL 服务器数据库。然而，LINQ to SQL 相较于 ADO.NET 具有许多优势，例如复杂度降低、编码行数减少等等。

For most ASP.NET developers, LINQ to SQL (also known as DLINQ) is an electrifying part of Language Integrated Query as this allows querying data in SQL server database by using usual LINQ expressions. It also allows to update, delete, and insert data, but the only drawback from which it suffers is its limitation to the SQL server database. However, there are many benefits of LINQ to SQL over ADO.NET like reduced complexity, few lines of coding and many more.

以下是显示 LINQ to SQL 执行架构的图表。

Below is a diagram showing the execution architecture of LINQ to SQL.

How to Use LINQ to SQL?

Step 1 - 与数据库服务器建立新的“数据连接”。查看 & Farrar; 服务器资源管理器 & Farrar; 数据连接 & Farrar; 添加连接

Step 1 − Make a new “Data Connection” with database server. View &arrar; Server Explorer &arrar; Data Connections &arrar; Add Connection

Step 2 - 添加 LINQ To SQL 类文件

Step 2 − Add LINQ To SQL class file

Step 3 - 从数据库中选择表并将其拖放到新的 LINQ to SQL 类文件中。

Step 3 − Select tables from database and drag and drop into the new LINQ to SQL class file.

Step 4 - 已将表添加到类文件中。

Step 4 − Added tables to class file.

Querying with LINQ to SQL

使用 LINQ to SQL 执行查询的规则类似于标准 LINQ 查询，即延迟或立即执行查询。有各种组件在使用 LINQ to SQL 执行查询中发挥作用，如下所示。

The rules for executing a query with LINQ to SQL is similar to that of a standard LINQ query i.e. query is executed either deferred or immediate. There are various components that play a role in execution of a query with LINQ to SQL and these are the following ones.

LINQ to SQL API − requests query execution on behalf of an application and sent it to LINQ to SQL Provider.
LINQ to SQL Provider − converts query to Transact SQL(T-SQL) and sends the new query to the ADO Provider for execution.
ADO Provider − After execution of the query, send the results in the form of a DataReader to LINQ to SQL Provider which in turn converts it into a form of user object.

需要注意的是，在执行 LINQ to SQL 查询之前，必须通过 DataContext 类连接到数据源。

It should be noted that before executing a LINQ to SQL query, it is vital to connect to the data source via DataContext class.

Insert, Update and Delete using LINQ To SQL

Add OR Insert

using System;
using System.Linq;

namespace LINQtoSQL {
   class LinqToSQLCRUD {
      static void Main(string[] args) {

         string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();

         LinqToSQLDataContext db = new LinqToSQLDataContext(connectString);

         //Create new Employee

         Employee newEmployee = new Employee();
         newEmployee.Name = "Michael";
         newEmployee.Email = "yourname@companyname.com";
         newEmployee.ContactNo = "343434343";
         newEmployee.DepartmentId = 3;
         newEmployee.Address = "Michael - USA";

         //Add new Employee to database
         db.Employees.InsertOnSubmit(newEmployee);

         //Save changes to Database.
         db.SubmitChanges();

         //Get new Inserted Employee
         Employee insertedEmployee = db.Employees.FirstOrDefault(e ⇒e.Name.Equals("Michael"));

         Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}, Address = {4}",
                          insertedEmployee.EmployeeId, insertedEmployee.Name, insertedEmployee.Email,
                          insertedEmployee.ContactNo, insertedEmployee.Address);

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

Module Module1

   Sub Main()

      Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()

      Dim db As New LinqToSQLDataContext(connectString)

      Dim newEmployee As New Employee()

      newEmployee.Name = "Michael"
      newEmployee.Email = "yourname@companyname.com"
      newEmployee.ContactNo = "343434343"
      newEmployee.DepartmentId = 3
      newEmployee.Address = "Michael - USA"

      db.Employees.InsertOnSubmit(newEmployee)

      db.SubmitChanges()

      Dim insertedEmployee As Employee = db.Employees.FirstOrDefault(Function(e) e.Name.Equals("Michael"))

      Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3},
         Address = {4}", insertedEmployee.EmployeeId, insertedEmployee.Name,
         insertedEmployee.Email, insertedEmployee.ContactNo, insertedEmployee.Address)

      Console.WriteLine(vbLf & "Press any key to continue.")
      Console.ReadKey()

   End Sub

End Module

编译并运行 C# 或 VB 的上述代码后，会产生以下结果 −

When the above code of C# or VB is compiled and run, it produces the following result −

Emplyee ID = 4, Name = Michael, Email = yourname@companyname.com, ContactNo =
343434343, Address = Michael - USA

Press any key to continue.

Update

using System;
using System.Linq;

namespace LINQtoSQL {
   class LinqToSQLCRUD {
      static void Main(string[] args) {

         string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();

         LinqToSQLDataContext db = new LinqToSQLDataContext(connectString);

         //Get Employee for update
         Employee employee = db.Employees.FirstOrDefault(e =>e.Name.Equals("Michael"));

         employee.Name = "George Michael";
         employee.Email = "yourname@companyname.com";
         employee.ContactNo = "99999999";
         employee.DepartmentId = 2;
         employee.Address = "Michael George - UK";

         //Save changes to Database.
         db.SubmitChanges();

         //Get Updated Employee
         Employee updatedEmployee = db.Employees.FirstOrDefault(e ⇒e.Name.Equals("George Michael"));

         Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}, Address = {4}",
                          updatedEmployee.EmployeeId, updatedEmployee.Name, updatedEmployee.Email,
                          updatedEmployee.ContactNo, updatedEmployee.Address);

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

Module Module1

   Sub Main()

      Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()

      Dim db As New LinqToSQLDataContext(connectString)

      Dim employee As Employee = db.Employees.FirstOrDefault(Function(e) e.Name.Equals("Michael"))

      employee.Name = "George Michael"
      employee.Email = "yourname@companyname.com"
      employee.ContactNo = "99999999"
      employee.DepartmentId = 2
      employee.Address = "Michael George - UK"

      db.SubmitChanges()

      Dim updatedEmployee As Employee = db.Employees.FirstOrDefault(Function(e) e.Name.Equals("George Michael"))

      Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3},
         Address = {4}", updatedEmployee.EmployeeId, updatedEmployee.Name,
         updatedEmployee.Email, updatedEmployee.ContactNo, updatedEmployee.Address)

      Console.WriteLine(vbLf & "Press any key to continue.")
      Console.ReadKey()

   End Sub

End Module

编译并运行 C# 或 Vb 的上述代码后，会产生以下结果 −

When the above code of C# or Vb is compiled and run, it produces the following result −

Emplyee ID = 4, Name = George Michael, Email = yourname@companyname.com, ContactNo =
999999999, Address = Michael George - UK

Press any key to continue.

Delete

using System;
using System.Linq;

namespace LINQtoSQL {
   class LinqToSQLCRUD {
      static void Main(string[] args) {

         string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();

         LinqToSQLDataContext db = newLinqToSQLDataContext(connectString);

         //Get Employee to Delete
         Employee deleteEmployee = db.Employees.FirstOrDefault(e ⇒e.Name.Equals("George Michael"));

         //Delete Employee
         db.Employees.DeleteOnSubmit(deleteEmployee);

         //Save changes to Database.
         db.SubmitChanges();

         //Get All Employee from Database
         var employeeList = db.Employees;
         foreach (Employee employee in employeeList) {
            Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}",
               employee.EmployeeId, employee.Name, employee.Email, employee.ContactNo);
         }

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

Module Module1

   Sub Main()

      Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()

      Dim db As New LinqToSQLDataContext(connectString)

      Dim deleteEmployee As Employee = db.Employees.FirstOrDefault(Function(e) e.Name.Equals("George Michael"))

      db.Employees.DeleteOnSubmit(deleteEmployee)

      db.SubmitChanges()

      Dim employeeList = db.Employees

      For Each employee As Employee In employeeList
         Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}",
            employee.EmployeeId, employee.Name, employee.Email, employee.ContactNo)
      Next

      Console.WriteLine(vbLf & "Press any key to continue.")
      Console.ReadKey()
   End Sub

End Module

编译并运行 C# 或 VB 的上述代码后，会产生以下结果 −

When the above code of C# or VB is compiled and run, it produces the following result −

Emplyee ID = 1, Name = William, Email = abc@gy.co, ContactNo = 999999999
Emplyee ID = 2, Name = Miley, Email = amp@esds.sds, ContactNo = 999999999
Emplyee ID = 3, Name = Benjamin, Email = asdsad@asdsa.dsd, ContactNo =

Press any key to continue.