[ASP.NET MVC Mavericks Road] 06 - Using Entity Framework

[ASP.NET
MVC The Road to Mavericks]06 - Using Entity Framework

It’s okay to be idle at home, let’s continue writing my [ASP.NET MVC The Road to Mavericks] series . In the previous blog post of this series, when displaying the book information list, we hand-crafted the data in the program code. This article will demonstrate how to use Entity
Framework in ASP.NET MVC to get data from the database. Although the title of this article sounds relatively simple, if you read it carefully, I believe you will definitely gain something.

Directory of this article:

ORM and EF

When we want to develop an application, we must consider how to display data and how to persist it. ized data. When considering this issue, the most important thing we should care about is the performance of the program, the ease of development, and the maintainability and scalability of the code.

Persistence refers to a mechanism that can permanently save various processing status information in an application. If there is no persistence mechanism, the state can only be saved in memory and will be lost when the machine is shut down.

In an application, when we want to save data permanently, we choose a relational database (Relation DataBase); when we want to save data temporarily, we use objects stored in memory. Currently, most developers use relational database technology as the persistence mechanism. Although some people are now trying to use object database (Object DataBase) technology, relational database technology will still be the main persistence mechanism for many years.

Why does the object database appear? The SQL language is a non-procedural, set-oriented, interpreted language, while many high-level languages ​​are procedural, object-oriented, compiled languages. This creates a mismatch between the two languages, resulting in unsatisfactory efficiency. This mismatch is called "impedance mismatch", and the object database emerged to solve the "impedance mismatch".

We know that in relational database technology, Table is used to store and organize data in a row and column structure. Before .NET 2.0, when C# did not have generics, people basically used DataTables filled in DataSets to map and store data queried from relational databases, as shown in the following code:

using (SqlConnection conn = new SqlConnection(connString)) {
    using (SqlDataAdapter da = new SqlDataAdapter("Select * from order", conn)) {
        DataTable dt = new DataTable();
        da.Fill(dt);
        ...
    }
}

Although this approach allows object-oriented languages ​​to match relational databases, it has some obvious shortcomings, such as non-type safety, difficulty in manipulation, and low performance. Starting from .NET 2.0, people began to use collections of entity model objects to match data in relational databases through generic technology. This method solved the shortcomings faced by the DataTable method, and it has strong typing, automatic completion in VS, Compile-time checking and other features make it popular among .Net developers.

In order to save developers from having to do this "matching" work manually, many ORM tools (such as Entity Framework, NHibernate, etc.) have been developed. ORM (Object Relation Mapping) tool, as the name suggests, its role is to solve the "mismatch" between "relationship" and "object-oriented". It allows developers to spend more time without paying too much attention to the persistence layer. Focus on business.

Entity Framework (EF) is an ORM solution developed by Microsoft based on ADO.NET, mainly based on Entity Data Model (EDM). EF uses the method of abstracting data structures to convert each database object into a class object (Entity) in the application, while the data fields are converted into properties (Property), and the relationships are converted into association properties (Association), so that The E/R
model of the database is completely converted into an object model, so that developers can use familiar object-oriented programming languages ​​to call and access. EF 4.0 and later will support three generation modes: Database First, Model First, and Code First. The Code First mode is used by more people.

I won’t talk much about concepts and theoretical things. I also searched for online resources with questions and summarized them based on my own understanding. And I am not talented. There are many things I want to describe but can’t. Know how to organize language.

The purpose of this article is mainly to let readers have a perceptual understanding of EF, and then understand the application of EF in ASP.NET MVC. I will not study theoretical things. I will introduce EF separately next time when I have time. .

Using Entity Framework

Following the previous blog post [ASP.NET
MVC Mavericks Road] 05 - Using Ninject, we now need to change the hand-crafted data in the code to Read from database. To do this, we first prepare the database. This example uses MS SQL Server, but the same applies to other databases. First create a database named BookShop, and then execute the following script to create a Books table:

CREATE TABLE Books 
( 
    [ID] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Title] NVARCHAR(100) NOT NULL,
    [Isbn] VARCHAR(20) NOT NULL, 
    [Summary] NVARCHAR(1000) NOT NULL,
    [Author] NVARCHAR(50) NOT NULL,
    [Thumbnail] VARBINARY(MAX),  
    [Price] DECIMAL(16, 2) NOT NULL,
    [Published] DATE NOT NULL,
)

然后随便在表中加几条用于测试的数据：

接下来我们就要让应用程序连接数据库了。由于上一篇博文是我在公司用休息的时间写的，公司的电脑装的是VS2010，家里的笔记本装的是VS2012，所以得重新把上篇博文的示例移到VS2012上，对于本示例，VS2010和VS2012都是一样的。上一篇示例项目的目录结构如下：

本文的示例将在上篇的这个示例基础上继续。

用NuGet在BookShop.Domain工程中安装Entity Framework包，方法请参考本系列的上一篇文章。

在BookShop.Domain工程的Concrete文件夹中添加一个名为EFDbContext的类，代码如下：

public class EFDbContext : DbContext { 
    public DbSet<Book> Books { get; set; } 
}

使用EF Code First第一步就是创建一个继承自System.Data.Entity.DbContext的类，这个类将为数据库中的每个表定义一个属性，属性的名称代表数据库中的表名。DbSet作为返回类型，它是用于生成CRUD(Create、Read、Update和Delete)操作的装置，映射数据库表的行。

我们需要在BookShop.WebUI工程中的web.config配置文件中添加数据库的连接字符串，来告诉EF怎样连接数据库。根据自己机器上的数据库配置连接字符串如下：

<connectionStrings>
  <add name="EFDbContext" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=BookShop;User ID=sa;Password=sa" providerName="System.Data.SqlClient" />
</connectionStrings>

接下来，我们把BookShop.Domain工程下Concrete文件中的BookRepository类文件改造一下，把代码中手工造的数据改成从数据库读取，以测试应用程序是否可以正常连接数据库。修改后的BookRepository类如下：

public class BookRepository : IBookRepository {
    private EFDbContext context = new EFDbContext();

    public IQueryable<Book> Books {
        get { return context.Books; }
    }
}

在我们的这个仓储类中，我们改使用EF，通过创建一个EFDbContext类的实例来获取数据库中的数据。如你所见，我们不需要自己写ADO.NET代码去连接和读取数据库，非常简洁明了，我们就是这样使用Entity Framework的。我们来看一下运行效果吧：

到这我们已经成功使用EF连接上了数据库，并从数据库中读取出来了数据。我们还可以通过Linq进行非常灵活的查询，就像写SQL一样。比如要查询价格在100元以下的前10条记录，并且按价格从低到高显示，那么我们可以在BookShop.WebUI工程下的BookController中的List方法中这样写：

public ViewResult List() {
    return View(repository.Books
        .OrderBy(b => b.Price)
        .Where(b => b.Price < 100)
        .Take(10));
}

或许你很快就会对EF获取数据库的方式产生这样的疑问：EF从数据库中读取整个Books表的数据到内存，然后返回给调用者(上面代码中的repository.Books)用Linq语句过滤用户想要的前10条数据，如果Books表中有几百万条数据，那内存岂不是完蛋了，EF不会这么傻吧？EF会不会根据Linq查询语句智能地生成SQL文本再到数据库中去查询数据呢？这里就要讲讲IQueryable和IEnumerable了。

IQueryable 和 IEnumerable

其实，对于上面的即有过虑又有排序的条件查询Linq语句，EF是读取数据库中整个Books表中的数据到内存，还是根据Linq查询语句智能的生成SQL再执行查询，完全编码者来决定的。我们打开BookShop.Domain工程的BookRepository类文件，请注意该类中Books属性的返回类型：

...
public IQueryable<Book> Books {
    get { return context.Books; }
}

我们对使用IQueryable作为返回类型提了个疑问：为什么用IQueryable而不用IEnumerable作为返回类型？答案是：使用IQueryable，EF会根据调用者的Linq表达式先生成相应的SQL查询语句，然后到数据库中执行查询，查询出来的数据即是用户想要的数据；而使用IEnumerable，Linq表达式的过滤、排序等操作都是在内存中发生的，即EF会先从数据库中把整个表的数据查询出来放在内存中，然后由调用者使用Linq语句进行过滤、排序等操作。是不是这样呢？我们来监视一下两种情况EF生成的SQL语句就知道了。

我们先来看看使用IQueryable的情况。重新运行一下程序，然后使用SQL Server Management Studio的活动和监视器查看一下我们的BookShop应用程序所执行的SQL语句，结果如下：

结果证明使用IQueryable，EF是先根据Linq表达式生成相应的SQL语句再执行查询的。

我们再稍稍修改一下代码来看看用IEnumerable的情况。把BookRepository类修改如下：

public class BookRepository : IBookRepository {
    private EFDbContext context = new EFDbContext();

    public IEnumerable<Book> Books {
        get { return context.Books; }
    }
}

当然BookRepository类所实现的IBookRepository接口（在BookShop.Domain工程的Abstract文件夹中）也要改一下：

public interface IBookRepository {    IEnumerable<Book> Books { get; }
}

再重新运行一下应用程序，用活动和监视器查看最后执行的SQL语句如下图：

我们看到改用IEnumerable后，EF生成的SQL没有任何过滤、排序等的操作，它一次把表中的所有数据都Select出来，和上面写的Linq表达式一点都没关系。

IQueryable虽然可以很智能地根据Linq表达式生成相应的SQL语句，但毕竟有一个分析Linq表达式的过程，相对来说性能比IEnumerable要差。那么我们什么时候用IEnumerable，什么时候用IQueryable呢？我想，对于少量的数据（比如从数据库中读取应用程序相关的系统信息）和不需要对数据进行过滤操作的情况，用IEnumerable比较适合；对于数据量较大需要对数据进行过滤（比如分页查询）的情况，则用IQueryable比较合适。

 以上就是[ASP.NET MVC 小牛之路]06 - 使用 Entity Framework的内容，更多相关内容请关注PHP中文网（www.php.cn）！