操作数据库

Posted on 9 月 26, 2026 by

22-EF Core和SqlServer的使用-生成数据库

一个ORM 对象关系模型，支持多数据库：SQLserver、SQLite、Npgsql、MySql 等等。

获取EF Code-sqlserver：

Install-Package Microsoft.EntityFrameworkCore.SqlServerCode language: CSS (css)
PM> Install-Package Microsoft.EntityFrameworkCore.SqlServer
  GET https://api.nuget.org/v3/registration3-gz-semver2/microsoft.entityframeworkcore.sqlserver/index.json
  OK https://api.nuget.org/v3/registration3-gz-semver2/microsoft.entityframeworkcore.sqlserver/index.json 352 毫秒
正在还原 E:\FoxDevelop.com视频\137-ASP.NET CORE 视频教程\EFCoreDemo\EFCoreDemo\EFCoreDemo.csproj 的包...
正在安装 NuGet 程序包 Microsoft.EntityFrameworkCore.SqlServer 2.0.2。
正在提交还原...
将锁定文件写入磁盘。路径: E:\FoxDevelop.com视频\137-ASP.NET CORE 视频教程\EFCoreDemo\EFCoreDemo\obj\project.assets.json
E:\FoxDevelop.com视频\137-ASP.NET CORE 视频教程\EFCoreDemo\EFCoreDemo\EFCoreDemo.csproj 的还原在 491.07 ms 内完成。
执行 nuget 操作花费时间 2.2 sec
已用时间: 00:00:05.0025412
PM>Code language: JavaScript (javascript)

EF Core 通过模型进行数据访问。模型由实体类和会话组成（上下文），会话是和数据库进行通讯的。

两种方式使用EF Core：

  • Database First：数据库生成模型
  • Code First：模型生成数据库

下面编写上下文和模型。一个博客的上下文，添加一个BloggingContext的类 继承之DbContext：

public class BloggingDbContext : DbContext
{
    //下面两个dbset 会被映射成数据库里面的表 数据库下面配置
    public DbSet<Blog> Blogs { set; get; }
    public DbSet<Post> Posts { set; get; }

    //重写OnConfiguring事件 设置数据库连接
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"server=.;database=efcoredb;Trusted_Connection=True;");
    }
}Code language: HTML, XML (xml)

生成数据库，使用命令通过代码生成数据库。打开nuget管理器：

先输入：Add-Migration MyFirstMigration(名字)

再输入：Update-Database

PM> Add-Migration MyFirstMigration
Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0]
      User profile is available. Using 'C:\Users\YY\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
To undo this action, use Remove-Migration.
PM> Update-Database
Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0]
      User profile is available. Using 'C:\Users\YY\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
Applying migration '20180401033242_MyFirstMigration'.
Done.
PM>Code language: JavaScript (javascript)

经过上面两个命令 然后我们就打开数据库sqlserver 就可以看到生成的数据库。

参考：https://blog.csdn.net/aojiancc2/article/details/76615653

常见异常：

System.ArgumentException
  HResult=0x80070057
  Message=AddDbContext was called with configuration, but the context type 'BloggingDbContext' only declares a parameterless constructor. This means that the configuration passed to AddDbContext will never be used. If configuration is passed to AddDbContext, then 'BloggingDbContext' should declare a constructor that accepts a DbContextOptions<BloggingDbContext> and must pass it to the base constructor for DbContext.
  Source=Microsoft.EntityFrameworkCoreCode language: JavaScript (javascript)

Blog类-一对多的关系：

public class Blog
{
    public int BlogId { set; get; }
    public int Rating { set; get; }
    public string Url { set; get; }

    public List<Post> Posts { set; get; }
}Code language: JavaScript (javascript)

博文表Post 一片文章一个Blog：

public class Post
{
    public int PostId { set; get; }
    public string Title { set; get; }
    public string Content { set; get; }

    public int BlogId { set; get; }
    public Blog Blog { set; get; }
}Code language: JavaScript (javascript)

23-EF CORE查询和新增-使用LINQ查询数据

下面我们再Home控制器里面 进行数据查询：

public IActionResult Index()
{
    //调用ef core 上下文进行数据查询
    using (var db = new BloggingDbContext())
    {
        var blogs = db.Blogs
                    .Where(b => b.Rating > 3)
                    .OrderBy(b => b.Url)
                    .ToList();
        ViewBag.BlogList = blogs;
    }

    return View();
}Code language: JavaScript (javascript)

当然我们上面 数据库刚生成 所以还没有数据 下面我们来使用ef core 新增一条数据库：

public IActionResult Index()
{
    using (var db = new BloggingDbContext())
    {
        //使用ef core 插入数据
        var blog = new Blog { Rating = 20, Url = "www.FoxDevelop.com" };
        db.Blogs.Add(blog);
        db.SaveChanges();

        //使用ef core 上下文进行数据查询
        var blogs = db.Blogs
                    .Where(b => b.Rating > 3)
                    .OrderBy(b => b.Url)
                    .ToList();
        ViewBag.BlogList = blogs;
    }

    return View();
}Code language: JavaScript (javascript)

视图中呈现数据列表 找到Home目录下面的Index视图：

@{
    ViewData["Title"] = "Home Page";
    var blogList = ViewBag.BlogList as List<Blog>;
}
<div class="col-md-3">
    <h2>博客列表</h2>
    <ul>
        @for (int i = 0; i < blogList.Count; i++)
        {
            <li><a href="@blogList[i].Url">@blogList[i].Url</a></li>
        }
    </ul>
</div>Code language: JavaScript (javascript)

24-CodeFirst 方式使用Sqlite

新建一个EfcoreSqlite的项目。Nuget安装sqlite支持：

Install-Package Microsoft.EntityFrameworkCore.SqliteCode language: CSS (css)
PM> Install-Package Microsoft.EntityFrameworkCore.Sqlite
  GET https://api.nuget.org/v3/registration3-gz-semver2/microsoft.entityframeworkcore.sqlite/index.json
  OK https://api.nuget.org/v3/registration3-gz-semver2/microsoft.entityframeworkcore.sqlite/index.json 308 毫秒
正在还原 E:\FoxDevelop.com视频\137-ASP.NET CORE 视频教程\EfcoreSqlite\EfcoreSqlite\EfcoreSqlite.csproj 的包...
正在安装 NuGet 程序包 Microsoft.EntityFrameworkCore.Sqlite 2.0.2。
正在提交还原...
E:\FoxDevelop.com视频\137-ASP.NET CORE 视频教程\EfcoreSqlite\EfcoreSqlite\EfcoreSqlite.csproj 的还原在 566.12 ms 内完成。
执行 nuget 操作花费时间 1.84 sec
已用时间: 00:00:05.8583027
PM>Code language: JavaScript (javascript)

新增实体类：

namespace EfcoreSqlite.Models
{
    public class User
    {
        public int Id { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
    }
}Code language: JavaScript (javascript)

创建上下文：

namespace EfcoreSqlite.Models
{
    public class DataContext : DbContext
    {
        public DataContext(DbContextOptions<DataContext> options) : base(options)
        {
        }

        public DbSet<User> Users { set; get; }
    }
}Code language: HTML, XML (xml)

创建数据库，Startup下面先引用 using Microsoft.EntityFrameworkCore; 然后：

public void ConfigureServices(IServiceCollection services)
{
    var connection = "Filename=./ecsqlite.db";
    services.AddDbContext<DataContext>(options => options.UseSqlite(connection));
    services.AddMvc();
}Code language: JavaScript (javascript)

方便操作数据库ef 安装EntityFrameworkCore Tool，执行nuget命令：

Install-package microsoft.entityframeworkcore.tools -preCode language: CSS (css)
PM> Install-package microsoft.entityframeworkcore.tools -pre
  GET https://api.nuget.org/v3/registration3-gz-semver2/microsoft.entityframeworkcore.tools/index.json
  OK https://api.nuget.org/v3/registration3-gz-semver2/microsoft.entityframeworkcore.tools/index.json 394 毫秒
正在还原 ...\EfcoreSqlite.csproj 的包...
  GET https://api.nuget.org/v3-flatcontainer/microsoft.entityframeworkcore.tools/index.json
  OK https://api.nuget.org/v3-flatcontainer/microsoft.extensions.configuration.abstractions/2.1.0-preview1-final/microsoft.extensions.configuration.abstractions.2.1.0-preview1-final.nupkg 220 毫秒
正在安装 Microsoft.Extensions.Configuration.Abstractions 2.1.0-preview1-final。
...
已将“Microsoft.EntityFrameworkCore.Design 2.1.0-preview1-final”成功安装到 EfcoreSqlite
执行 nuget 操作花费时间 2.69 sec
已用时间: 00:00:16.3804816
PM>Code language: PHP (php)

数据库迁移和执行：

E:\...\EfcoreSqlite> dotnet ef migrations add mysecondmigration
未找到与命令“dotnet-ef”匹配的可执行文件

.net core 2.0 dbfirst 报 dotnet ef 未找到与命令“dotnet-ef”匹配的可执行文件的解决办法：右击项目弹出菜单点击编辑 ***.csproj，增加如下配置：

<ItemGroup>
  <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
</ItemGroup>Code language: HTML, XML (xml)
E:\...\EfcoreSqlite> dotnet ef migrations add MySecMigration
Build failed.

E:\...\EfcoreSqlite> dotnet ef
                     _/\__
               ---==/    \\
         ___  ___   |.    \|\
        | __|| __|  |  )   \\\
        | _| | _|   \_/ |  //|\\
        |___||_|       /   \\\/\\

Entity Framework Core .NET Command Line Tools 2.0.0-rtm-26452
Usage: dotnet ef [options] [command]
...Code language: PHP (php)
dotnet ef migrations add InitialCreate
# 提交到数据库
dotnet ef database updateCode language: PHP (php)
E:\...\EfcoreSqlite> dotnet ef migrations add InitialCreate
info: Microsoft.AspNetCore.DataProtection.KeyManagement.XmlKeyManager[0]
      User profile is available. Using 'C:\Users\YY\AppData\Local\ASP.NET\DataProtection-Keys' ...
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.0.2-rtm-10011 initialized 'DataContext' using provider 'Microsoft.EntityFrameworkCore.Sqlite' with options: None
Done. To undo this action, use 'ef migrations remove'Code language: JavaScript (javascript)

需要再上下文中引入命名空间 Microsoft.EntityFrameworkCore.Sqlite，需要安装 Install-package Microsoft.EntityFrameworkCore.Sqlite。

生成数据库文件：

public void ConfigureServices(IServiceCollection services)
{
    //var connection = "Filename=./ecsqlite.db";
    //services.AddDbContext<DataContext>(options=> options.UseSqlite(connection));
    services.AddDbContext<DataContext>();
    services.AddMvc();
}Code language: JavaScript (javascript)
public class DataContext : DbContext
{
    public DataContext(DbContextOptions<DataContext> options) : base(options)
    {
    }

    public DbSet<User> Users { set; get; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=blogging.db");
    }
}Code language: HTML, XML (xml)

注意事项：开发调试时默认生成的数据库文件所在目录为 bin/Debug/netcoreapp1.1/blogging.db。发布后，应该把数据库文件拷贝到发布程序的根目录里，不然报错，找不到数据库文件。

24-解决sqlite数据库没法创建的问题

Nuget中安装：

  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.Sqlite
  • Microsoft.EntityFrameworkCore.Tools.DotNet

在DataContext中指定数据连接串：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlite("Data Source=data.db");
}Code language: JavaScript (javascript)

原来是在Startup中指定的，修改ConfigureServices：

// 原来
public void ConfigureServices(IServiceCollection services)
{
    var connection = "Filename=./ecsqlite.db";
    services.AddDbContext<DataContext>(options => options.UseSqlite(connection));
    services.AddMvc();
}

// 修改后
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<DataContext>();
    services.AddMvc();
}Code language: JavaScript (javascript)

安装Tools.DotNet时可能遇到的包降级错误：

PM> Install-Package Microsoft.EntityFrameworkCore.Tools.DotNet
NU1605: 检测到包降级: Microsoft.NETCore.App 从 2.0.6 降级到 2.0.0...
Install-Package : 程序包还原失败。正在回滚“EfcoreSqlite”的程序包更改。Code language: CSS (css)

解决办法：去掉csproj里面之前根据网上加的虚假安装配置。

新建User控制器：

namespace EfcoreSqlite.Controllers
{
    public class UserController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }

        public IActionResult Register()
        {
            return View();
        }
    }
}

右击action添加视图，最后生成的视图：

Index视图：

@model IEnumerable<EfcoreSqlite.Models.User>
@{
    ViewData["Title"] = "Index";
}
<h2>Index</h2>
<p><a asp-action="Create">Create New</a></p>
<table class="table">
    <thead>
        <tr>
            <th>@Html.DisplayNameFor(model => model.UserName)</th>
            <th>@Html.DisplayNameFor(model => model.Password)</th>
            <th></th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>@Html.DisplayFor(modelItem => item.UserName)</td>
            <td>@Html.DisplayFor(modelItem => item.Password)</td>
            <td>
                <a asp-action="Edit" asp-route-id="@item.Id">Edit</a> |
                <a asp-action="Details" asp-route-id="@item.Id">Details</a> |
                <a asp-action="Delete" asp-route-id="@item.Id">Delete</a>
            </td>
        </tr>
}
    </tbody>
</table>Code language: HTML, XML (xml)

Register视图：

@model EfcoreSqlite.Models.User
@{
    ViewData["Title"] = "Register";
}
<h2>Register</h2>
<h4>User</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form asp-action="Register">
            <div asp-validation-summary="ModelOnly" class="text-danger"></div>
            <div class="form-group">
                <label asp-for="UserName" class="control-label"></label>
                <input asp-for="UserName" class="form-control" />
                <span asp-validation-for="UserName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Password" class="control-label"></label>
                <input asp-for="Password" class="form-control" />
                <span asp-validation-for="Password" class="text-danger"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </form>
    </div>
</div>
<div><a asp-action="Index">Back to List</a></div>Code language: HTML, XML (xml)

完成action的Post操作：

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Register(User info)
{
    if (ModelState.IsValid)
    {
        using (var db = new DataContext())
        {
            db.Users.Add(info);
            db.SaveChanges();
        }
    }
    return View();
}Code language: PHP (php)

列表页呈现：

public IActionResult Index()
{
    using (var db = new DataContext())
    {
        var blogs = db.Users.ToList();
        return View(blogs);
    }
}Code language: PHP (php)

Posted in ASP.NET CORE

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注