.net EFCore基本操作

---

theme: smartblue

环境搭建

下载包

Microsoft.EntityFrameworkCore.SqlServer

Microsoft.EntityFrameworkCore.Tools

创建实体类Book

``` using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks;

namespace EFCore { public class Book { public int Id { get; set; }

public string Title { get; set; }

    public DateTime PubTime { get; set; }

    public double Price { get; set; }

}

}

```

创建配置类

对应数据库表

``` using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata.Builders; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks;

namespace EFCore { public class BookConfig : IEntityTypeConfiguration { public void Configure(EntityTypeBuilder builder) { builder.ToTable("T_Books"); builder.Property(b=> b.Title) .HasMaxLength(50).IsRequired(); builder.Property(b=> b.AuthorName) .HasMaxLength(20).IsRequired(); } } }

```

对应数据库配置

``` using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks;

namespace EFCore { public class MyDBContext:DbContext { public DbSet Books { get; set; }

public DbSet<Person> Persons { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseSqlServer(
            "server=.159.138.73;" +
            "uid=SA;" +
            "pwd=qwer1234.;" +
            "database=demo1_Enviroment;" +
            "TrustServerCertificate=true;"
            );
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        // 从当前程序集加载所有的IEntityTypeConfiguration
        modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
    }
}

}

```

Migration

初始化

显示控制台

控制台输入Add-Migration Init，初始化创表语句

控制台输入Update-Database，同步数据

添加修改

对 person 类添加了一个属性BirthPlace

控制台输入 Add-Migration AddBirth

Update-Database同步数据

Data Annotation

新增实体类cat

``` using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Linq; using System.Text; using System.Threading.Tasks;

namespace EFCore { [Table("T_Cats")] public class Cat { public long Id { get; set; }

[Required]
    [MaxLength]
    public string Name { get; set; }
}

}

```

配置类添加cat

``` using Microsoft.EntityFrameworkCore; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks;

namespace EFCore { public class MyDBContext:DbContext { public DbSet Books { get; set; }

public DbSet<Person> Persons { get; set; }

    public DbSet<Cat> Cats { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseSqlServer(
            "server=192.159.138.73;" +
            "uid=SA;" +
            "pwd=qwer1234.;" +
            "database=demo1_Enviroment;" +
            "TrustServerCertificate=true;"

            );
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        // 从当前程序集加载所有的IEntityTypeConfiguration
        modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
    }
}

}

```

Add-Migration addCat 和 Update-Database 后

增删改查

增

添加一条图书信息

namespace EFCore { public class Program { static async Task Main(string[] args) { // context 相当于逻辑上的数据库 using (MyDBContext context = new MyDBContext()) { Book book = new Book(); book.Title = "西游记"; book.AuthorName = "吴承恩"; context.Books.Add(book); await context.SaveChangesAsync(); } } } }

查

已知有如下数据：

查询价格大于 80 的书籍信息

``` using System.Linq;

namespace EFCore { public class Program { static async Task Main(string[] args) {
// context 相当于逻辑上的数据库 using (MyDBContext context = new MyDBContext()) {

IQueryable<Book> books = context.Books.Where(b => b.Price > 80);
            foreach(var book in books)
            {
                await Console.Out.WriteLineAsync(book.Title+"__"+book.Price);
            }
            await context.SaveChangesAsync();
        }
    }
}

} ```

查询"西游记"的作者

``` using System.Linq;

namespace EFCore { public class Program { static async Task Main(string[] args) {
// context 相当于逻辑上的数据库 using (MyDBContext context = new MyDBContext()) {

Book one = context.Books.Single(b => b.Title == "西游记");
            await Console.Out.WriteLineAsync(one.AuthorName);

        }
    }
}

} ```

价格升序

``` using System.Linq;

namespace EFCore { public class Program { static async Task Main(string[] args) {
// context 相当于逻辑上的数据库 using (MyDBContext context = new MyDBContext()) {

IOrderedQueryable<Book> books = context.Books.OrderBy(b=>b.Price);
            foreach (Book book in books)
            {
                await Console.Out.WriteLineAsync(book.Title+","+book.Price);
            }

        }
    }
}

} ```

按照作者名称分组，查询出版书籍数量以及售卖书籍最贵的价格

修改部分数据

``` using System.Linq;

namespace EFCore { public class Program { static async Task Main(string[] args) {
// context 相当于逻辑上的数据库 using (MyDBContext context = new MyDBContext()) {

var items = context.Books
                .GroupBy(b => b.AuthorName)
                .Select(g => new
                {
                    Name = g.Key,
                    BooksCount = g.Count(),
                    MaxPrice = g.Max(b => b.Price)
                });
            foreach(var item in items)
            {
                await Console.Out.WriteLineAsync($"{item.Name},{item.BooksCount},{item.MaxPrice}");
            }
        }
    }
}

} ```

改

将西游记的作者修改为张三

``` using System.Linq;

namespace EFCore { public class Program { static async Task Main(string[] args) {
// context 相当于逻辑上的数据库 using (MyDBContext context = new MyDBContext()) {

Book book = context.Books.Single(b=>b.Title=="西游记");
            book.AuthorName = "张三";
            await context.SaveChangesAsync();

        }
    }
}

} ```

为所有价格大于10的书籍涨10块钱

涨价前

涨价后

``` using System.Linq;

namespace EFCore { public class Program { static async Task Main(string[] args) { // context 相当于逻辑上的数据库 using (MyDBContext context = new MyDBContext()) { IQueryable books = context.Books.Where(b => b.Price > 10); foreach(var b in books) { b.Price = b.Price + 10; } await context.SaveChangesAsync(); } } } }

```

删

删除编号为6的书籍

``` using System.Linq;

namespace EFCore { public class Program { static async Task Main(string[] args) {
// context 相当于逻辑上的数据库 using (MyDBContext context = new MyDBContext()) {

Book book = context.Books.Single(b=>b.Id == 6);
            context.Books.Remove(book);
            await context.SaveChangesAsync();

        }
    }
}

} ```