ef框架就是将c#代码和数据库形成一种映射,方便数据库的增删改查。下面用book表做一个例子。
book表sql:
create table books{
BookId int not null primary key identity,
[title] nvarchar(50) not null,
[publisher] nvarchar(25) not null
}
nuget包管理:
NETStandard.Library
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
book类是一个简单的实体类型
using System.ComponentModel.DataAnnotations.Schema; namespace efstudy { [Table("Books")] class Book { public int BookId { get; set; } public string Title { get; set; } public string Publisher { get; set; } } }
bookscontext 类 实现book表和数据库的关系。这个类派生自DbContext.BooksContext类定义了DbSet<Book>类型的Books属性,映射到数据库的Books表。接下来要定义数据库连接串,可以重写DbContext的OnConfiguring方法。在这里UseSqlServer扩展方法将上下文映射到sqlsesrver数据库。代码如下:
using Microsoft.EntityFrameworkCore; namespace efstudy { class BookContext:DbContext { private const string ConnectionString = @"Server=localhost\SQLEXPRESS;database=Books;trusted_connection=true"; public DbSet<Book> Books { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { base.OnConfiguring(optionsBuilder); optionsBuilder.UseSqlServer(ConnectionString); } } }
定义好上述,就可以使用program类进行增删改查了:
using System; using System.Threading.Tasks; using Microsoft.EntityFrameworkCore; using System.Linq; namespace efstudy { class Program { static void Main(string[] args) { Console.WriteLine("Hello World!"); var p = new Program(); //创建数据库 p.CreateDateBaseAsync().Wait(); //插入单条数据 p.AddBookAsync("c#6", "Wrox").Wait(); //插入多条数据 p.AddBooksAsync().Wait(); //读取数据 p.ReadBooks(); //查询读取数据 p.QueryBooks(); //更新数据 p.UpdateBookAsync().Wait(); //更新冲突 ConflictHandlingAsync().Wait(); //删除数据 p.DeleteBooksAsync().Wait(); p.ReadBooks(); } private async Task CreateDateBaseAsync() { using (var context = new BookContext()) { bool created = await context.Database.EnsureCreatedAsync(); Console.WriteLine(created ? "books created" : "already exists"); } } private async Task AddBookAsync(string title,string publisher) { using (var context = new BookContext()) { var book = new Book { Title = title, Publisher = publisher }; context.Add(book); int record = await context.SaveChangesAsync(); Console.WriteLine($"{record} record add"); } Console.WriteLine(); } private async Task AddBooksAsync() { using (var context = new BookContext()) { var b1 = new Book { Title = "123", Publisher = "Wrox" }; var b2 = new Book { Title = "1234", Publisher = "wrox" }; var b3 = new Book { Title = "1", Publisher = "Wrox" }; var b4 = new Book { Title = "Test", Publisher = "Test" }; var b5 = new Book { Title = "web", Publisher = "Test" }; context.AddRange(b1, b2, b3, b4, b5); int record = await context.SaveChangesAsync(); Console.WriteLine($"{record} records added"); } Console.WriteLine(); } private void ReadBooks() { using (var context = new BookContext()) { var books = context.Books; foreach (var b in books) { Console.WriteLine($"{b.Title} {b.Publisher}"); } } Console.WriteLine(); } private void QueryBooks() { using (var context = new BookContext()) { var books = context.Books.Where(b => b.Publisher == "Wrox"); foreach (var b in books) { Console.WriteLine($"{b.Title} {b.Publisher}"); } } Console.WriteLine(); } private async Task UpdateBookAsync() { using (var context = new BookContext()) { int record = 0; var book = context.Books.Where(b => b.Title == "Test"); if (book != null) { await book.ForEachAsync(b => b.Title = "Profession"); record = await context.SaveChangesAsync(); } Console.WriteLine($"{record} record update"); } Console.WriteLine(); } private async Task DeleteBooksAsync() { using (var context = new BookContext()) { var book = context.Books; context.Books.RemoveRange(book); int record = await context.SaveChangesAsync(); Console.WriteLine($"{record} records deleted"); } Console.WriteLine(); } public static async Task ConflictHandlingAsync() { Tuple<BookContext, Book> tuple1 = await PrepareUpdateAsync(); tuple1.Item2.Title = "updated from user1"; Tuple<BookContext, Book> tuple2 = await PrepareUpdateAsync(); tuple2.Item2.Title = "updated from user2"; await UpdateAsync(tuple1.Item1, tuple1.Item2); await UpdateAsync(tuple2.Item1, tuple2.Item2); tuple1.Item1.Dispose(); tuple2.Item1.Dispose(); await CheckUdateAsync(tuple1.Item2.BookId); } private static async Task<Tuple<BookContext, Book>> PrepareUpdateAsync() { var context = new BookContext(); Book book = await context.Books.Where(b => b.Title == "1234").FirstOrDefaultAsync(); return Tuple.Create(context, book); } private static async Task UpdateAsync(BookContext context,Book book) { await context.SaveChangesAsync(); Console.WriteLine($"successfully written to the database id:{book.BookId} title{book.Title}"); } private static async Task CheckUdateAsync(int id) { using (var context = new BookContext()) { Book book = await context.Books.Where(b => b.BookId == id).FirstOrDefaultAsync(); Console.WriteLine($"updated {book.Title}"); } } } }
下面展示ef框架如何使用依赖注入。它不是定义连接并利用DbContext派生类来使用sqlserver,而是使用依赖注入框架来注入连接和sqlserver选项
nuget包管理:
NETStandard.Library
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.Framework.DependencyInjection
定义Books表如下:
using System; using System.Collections.Generic; using System.Text; using System.ComponentModel.DataAnnotations.Schema; namespace EFStudyWithDI { [Table("Books")] class Book { public int BookId { get; set; } public string Title { get; set; } public string Publisher { get; set; } } }
bookscontext 类看起来要简单许多,只是定义了Books属性。代码如下:
using System; using System.Collections.Generic; using System.Text; using Microsoft.EntityFrameworkCore; namespace EFStudyWithDI { class BookContext:DbContext { public BookContext(DbContextOptions<BookContext> options) : base(options) { } public DbSet<Book> Books { get; set; } } }
booksservice类是利用bookscontext的新类。在这里,bookscontext通过构造函数注入功能来注入。
using System; using System.Collections.Generic; using System.Text; using System.Threading.Tasks; using static System.Console; namespace EFStudyWithDI { class BookService { private readonly BookContext _bookcontext; public BookService(BookContext context) { _bookcontext = context; } public async Task addBookAsync() { var b1 = new Book { Title = "1", Publisher = "1" }; var b2 = new Book { Title = "2", Publisher = "2" }; var b3 = new Book { Title = "3", Publisher = "3" }; _bookcontext.AddRange(b1, b2, b3); int record = await _bookcontext.SaveChangesAsync(); WriteLine($"{ record} records added"); WriteLine(); } public async Task DeleteBooks() { var book = _bookcontext.Books; _bookcontext.Books.RemoveRange(book); int record = await _bookcontext.SaveChangesAsync(); WriteLine($"{record} recorded deleted"); WriteLine(); } public void ReadBooks() { var book = _bookcontext.Books; foreach (var b in book) { WriteLine($"{b.Title} {b.Publisher}"); } WriteLine(); } } }
依赖注入框架的容器在initializeServices方法中初始化。这里创建serviceCollection实例,并使用addtransient方法,表示每次请求都会创建一个新的实例。还有使用adddbcontext方法来配置数据库连接串。
通过调用IserviceProvider的getservice()方法检索BooksService
using System; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.DependencyInjection; namespace EFStudyWithDI { class Program { static void Main(string[] args) { Console.WriteLine("Hello World!"); try { var p = new Program(); p.InitializeServices(); var servic = p.Container.GetService<BookService>(); servic.addBookAsync().Wait(); servic.ReadBooks(); servic.DeleteBooks().Wait(); servic.ReadBooks(); Console.ReadLine(); } catch (Exception e) { Console.WriteLine(e.Message); } } private void InitializeServices() { const string ConnectionString = @"Server=localhost\SQLEXPRESS;database=Books;trusted_connection=true"; var service = new ServiceCollection(); service.AddTransient<BookService>(); service.AddDbContext<BookContext>(options => options.UseSqlServer(ConnectionString)); Container = service.BuildServiceProvider(); } public IServiceProvider Container { get; private set; } } }
下面来处理ef框架多表关系
1.使用数据注释来完善表信息:
menus 表:
using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace MenusSample { [Table("Menus", Schema = "mc")] public class Menu { public int MenuId { get; set; } [MaxLength(50)] public string Text { get; set; } [Column(TypeName = "Money")] public decimal Price { get; set; } public int MenuCardId { get; set; } public MenuCard MenuCard { get; set; } } }
menucard表:
using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; namespace MenusSample { [Table("MenuCards", Schema = "mc")] public class MenuCard { public int MenuCardId { get; set; } [MaxLength(120)] public string Title { get; set; } public List<Menu> Menus { get; } = new List<Menu>(); } }
2.重写dbcontext的OnModelCreating方法来完善表信息。
protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.HasDefaultSchema("mc"); modelBuilder.Entity<MenuCard>().ToTable("MenuCards").HasKey(c => c.MenuCardId); modelBuilder.Entity<MenuCard>().Property<int>(c => c.MenuCardId).ValueGeneratedOnAdd(); modelBuilder.Entity<MenuCard>().Property<string>(c => c.Title).HasMaxLength(50); modelBuilder.Entity<Menu>().ToTable("Menus").HasKey(m => m.MenuId); modelBuilder.Entity<Menu>().Property<int>(m => m.MenuId).ValueGeneratedOnAdd(); modelBuilder.Entity<Menu>().Property<string>(m => m.Text).HasMaxLength(120); modelBuilder.Entity<Menu>().Property<decimal>(m => m.Price).HasColumnType("Money"); modelBuilder.Entity<MenuCard>().HasMany(c => c.Menus).WithOne(m => m.MenuCard); modelBuilder.Entity<Menu>().HasOne(m => m.MenuCard).WithMany(c => c.Menus).HasForeignKey(m => m.MenuCardId); }
3.用关系添加对象等
using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.ChangeTracking; using System; using System.Linq; using System.Threading.Tasks; using static System.Console; namespace MenusSample { class Program { static void Main() { CreateDatabaseAsync().Wait(); AddRecordsAsync().Wait(); ObjectTracking(); UpdateRecordsAsync().Wait(); ChangeUntrackedAsync().Wait(); } private static async Task ChangeUntrackedAsync() { WriteLine(nameof(ChangeUntrackedAsync)); Menu m = await GetMenuAsync(); m.Price += 0.7m; await UpdateUntrackedAsync(m); WriteLine(); } private static async Task UpdateUntrackedAsync(Menu m) { using (var context = new MenuContext()) { ShowState(context); //EntityEntry<Menu> entry = context.Menus.Attach(m); //entry.State = EntityState.Modified; context.Menus.Update(m); ShowState(context); await context.SaveChangesAsync(); } } private static async Task<Menu> GetMenuAsync() { using (var context = new MenuContext()) { Menu menu = await context.Menus .Skip(2) .FirstOrDefaultAsync(); return menu; } } private static async Task UpdateRecordsAsync() { WriteLine(nameof(UpdateRecordsAsync)); using (var context = new MenuContext()) { Menu menu = await context.Menus .Skip(1) .FirstOrDefaultAsync(); ShowState(context); menu.Price += 0.2m; ShowState(context); int records = await context.SaveChangesAsync(); WriteLine($"{records} updated"); ShowState(context); } WriteLine(); } private static void ObjectTracking() { WriteLine(nameof(ObjectTracking)); using (var context = new MenuContext()) { context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; var m1 = (from m in context.Menus.AsNoTracking() where m.Text.StartsWith("Con") select m).FirstOrDefault(); var m2 = (from m in context.Menus where m.Text.Contains("(") select m).FirstOrDefault(); if (object.ReferenceEquals(m1, m2)) { WriteLine("the same object"); } else { WriteLine("not the same"); } ShowState(context); } WriteLine(); } private static async Task CreateDatabaseAsync() { using (var context = new MenuContext()) { bool created = await context.Database.EnsureCreatedAsync(); string createdText = created ? "created" : "already exists"; WriteLine($"database {createdText}"); // await context.Database.MigrateAsync(); } } private static async Task AddRecordsAsync() { WriteLine(nameof(AddRecordsAsync)); try { using (var context = new MenuContext()) { var soupCard = new MenuCard(); Menu[] soups = { new Menu { Text = "Consommé Célestine (with shredded pancake)", Price = 4.8m, MenuCard =soupCard}, new Menu { Text = "Baked Potato Soup", Price = 4.8m, MenuCard = soupCard }, new Menu { Text = "Cheddar Broccoli Soup", Price = 4.8m, MenuCard = soupCard }, }; soupCard.Title = "Soups"; soupCard.Menus.AddRange(soups); context.MenuCards.Add(soupCard); ShowState(context); int records = await context.SaveChangesAsync(); WriteLine($"{records} added"); WriteLine(); } } catch (Exception ex) { WriteLine(ex.Message); } WriteLine(); } public static void ShowState(MenuContext context) { foreach (EntityEntry entry in context.ChangeTracker.Entries()) { WriteLine($"type: {entry.Entity.GetType().Name}, state: {entry.State}, {entry.Entity}"); } WriteLine(); } } }
使用menu和 menucard表示范事务的使用:
using MenusSample; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Storage; using System.Linq; using System.Threading.Tasks; using static System.Console; namespace TransactionSample { class Program { static void Main() { PreparationAsync().Wait(); AddTwoRecordsWithOneTxAsync().Wait(); AddTwoRecordsWithTwoTxAsync().Wait(); TwoSaveChangesWithOneTxAsync().Wait(); } public static async Task PreparationAsync() { using (var context = new MenusContext()) { bool deleted = await context.Database.EnsureDeletedAsync(); string deletedText = deleted ? "deleted" : "does not exist"; WriteLine($"database {deletedText}"); bool created = await context.Database.EnsureCreatedAsync(); string createdText = created ? "created" : "already exists"; WriteLine($"database {createdText}"); var card = new MenuCard() { Title = "Meat" }; var m1 = new Menu { MenuCard = card, Text = "Wiener Schnitzel", Price = 12.90m }; var m2 = new Menu { MenuCard = card, Text = "Goulash", Price = 8.80m }; card.Menus.AddRange(new Menu[] { m1, m2 }); context.MenuCards.Add(card); int records = await context.SaveChangesAsync(); WriteLine($"{records} records added"); } } private static async Task AddTwoRecordsWithOneTxAsync() { WriteLine(nameof(AddTwoRecordsWithOneTxAsync)); try { using (var context = new MenusContext()) { var card = context.MenuCards.First(); var m1 = new Menu { MenuCardId = card.MenuCardId, Text = "added", Price = 99.99m }; int hightestCardId = await context.MenuCards.MaxAsync(c => c.MenuCardId); var mInvalid = new Menu { MenuCardId = ++hightestCardId, Text = "invalid", Price = 999.99m }; context.Menus.AddRange(m1, mInvalid); WriteLine("trying to add one invalid record to the database, this should fail..."); int records = await context.SaveChangesAsync(); WriteLine($"{records} records added"); } } catch (DbUpdateException ex) { WriteLine($"{ex.Message}"); WriteLine($"{ex?.InnerException.Message}"); } WriteLine(); } private static async Task AddTwoRecordsWithTwoTxAsync() { WriteLine(nameof(AddTwoRecordsWithTwoTxAsync)); try { using (var context = new MenusContext()) { WriteLine("adding two records with two transactions to the database. One record should be written, the other not...."); var card = context.MenuCards.First(); var m1 = new Menu { MenuCardId = card.MenuCardId, Text = "added", Price = 99.99m }; context.Menus.Add(m1); int records = await context.SaveChangesAsync(); WriteLine($"{records} records added"); int hightestCardId = await context.MenuCards.MaxAsync(c => c.MenuCardId); var mInvalid = new Menu { MenuCardId = ++hightestCardId, Text = "invalid", Price = 999.99m }; context.Menus.Add(mInvalid); records = await context.SaveChangesAsync(); WriteLine($"{records} records added"); } } catch (DbUpdateException ex) { WriteLine($"{ex.Message}"); WriteLine($"{ex?.InnerException.Message}"); } WriteLine(); } private static async Task TwoSaveChangesWithOneTxAsync() { WriteLine(nameof(TwoSaveChangesWithOneTxAsync)); IDbContextTransaction tx = null; try { using (var context = new MenusContext()) using (tx = await context.Database.BeginTransactionAsync()) { WriteLine("using one explicit transaction, writing should roll back..."); var card = context.MenuCards.First(); var m1 = new Menu { MenuCardId = card.MenuCardId, Text = "added with explicit tx", Price = 99.99m }; context.Menus.Add(m1); int records = await context.SaveChangesAsync(); WriteLine($"{records} records added"); int hightestCardId = await context.MenuCards.MaxAsync(c => c.MenuCardId); var mInvalid = new Menu { MenuCardId = ++hightestCardId, Text = "invalid", Price = 999.99m }; context.Menus.Add(mInvalid); records = await context.SaveChangesAsync(); WriteLine($"{records} records added"); tx.Commit(); } } catch (DbUpdateException ex) { WriteLine($"{ex.Message}"); WriteLine($"{ex?.InnerException.Message}"); WriteLine("rolling back..."); tx.Rollback(); } WriteLine(); } } }
运行结果如下: