EntityFramework与SQLite

[renyh]

官方教程
https://docs.microsoft.com/zh-cn/ef/core/
https://docs.microsoft.com/zh-cn/ef/efcore-and-ef6/side-by-side

比较 EF Core 和 EF6
https://docs.microsoft.com/zh-cn/ef/efcore-and-ef6/

参考资料
https://stackoverflow.com/questions/36488461/sqlite-in-asp-net-core-with-entityframeworkcore
https://ianvink.wordpress.com/2018/04/09/asp-net-core-2-using-sqlite-as-a-light-weight-database/

sqlite查看工具
https://sqlitebrowser.org/dl/ 这个工具很好用。

[renyh]

[renyh]

[renyh]

[renyh]

Step 1: Create a simple appliation

Step 2: 引用Nuget包

Microsoft.EntityFrameworkCore.SQlite，最新版本3.1.1。安装会自动安装其引用的下级包。

Step 3: Defining your Models

Create your models and add a new property for each of your new models (given that you want a table for each!)

    public class Cat
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

Step 4: Create a context class for your database.

What’s very interesting here is that the database doesn’t exist, the class SimpleDataStorage tells EF how and where to create it.

public class SimpleDataStorage: DbContext
{
    public SimpleDataStorage(){}
 
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        //Set the filename of the database to be created
        optionsBuilder.UseSqlite("Data Source=db.sqlite");
    }
 
    public DbSet<Cat> Cats { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
 
        //Define the Table(s) and References to be created automatically
        modelBuilder.Entity<Cat>(b =>
        {
            b.HasKey(e => e.Id);
            b.Property(e => e.Id).ValueGeneratedOnAdd();
            b.Property(e => e.Name).IsRequired().HasMaxLength(255);
            b.ToTable("Cats");
        });
    }
}

Step 5: Create the database and tables.

在程序初始化的阶段调用

   public void CreateDb()
    {
        using (var client = new SimpleDataStorage())
        {
            //Create the database file at a path defined in SimpleDataStorage
            client.Database.EnsureCreated();
            //Create the database tables defined in SimpleDataStorage
            client.Database.Migrate();
        }
    }

Step 6: Add Cats to the database!

We can now access the database anywhere in code

public void AddCate(SimpleDataStorage db)
{
    for (int i = 0; i < 1000; i++)
    {
        db.Cats.Add(new Cat() {Name = "Caty McCat: " + i}); 
    }
    db.SaveChanges(true);
}

[renyh]

查询

使用语言集成查询 (LINQ) 从数据库检索实体类的实例。 有关详细信息，请参阅查询数据。

using (var db = new BloggingContext())
{
    var blogs = db.Blogs
        .Where(b => b.Rating > 3)
        .OrderBy(b => b.Url)
        .ToList();
}

例2

using (var context = new BloggingContext())
{
    var blogs = context.Blogs
        .Where(b => b.Url.Contains("dotnet"))
        .ToList();
}

加载所有数据

using (var context = new BloggingContext())
{
    var blogs = context.Blogs.ToList();
}

加载单个实体

using (var context = new BloggingContext())
{
    var blog = context.Blogs
        .Single(b => b.BlogId == 1);
}

[renyh]

保存数据

使用实体类的实例在数据库中创建、删除和修改数据。 有关详细信息，请参阅保存数据。

using (var db = new BloggingContext())
{
    var blog = new Blog { Url = "http://sample.com" };
    db.Blogs.Add(blog);
    db.SaveChanges();
}

[renyh]

删除数据
使用 DbSet.Remove 方法删除实体类的实例。
如果实体已存在于数据库中，则将在“SaveChanges” 期间删除该实体。 如果实体尚未保存到数据库（即跟踪为“已添加”），则在调用SaveChanges时，该实体会从上下文中移除且不再插入。

using (var context = new BloggingContext())
{
    var blog = context.Blogs.First();
    context.Blogs.Remove(blog);
    context.SaveChanges();
}