.net EFCore基本操作

xyy123
102 阅读4分钟

一、环境搭建

1.1 下载包

Microsoft.EntityFrameworkCore.SqlServer image.png

Microsoft.EntityFrameworkCore.Tools

image.png

1.2 创建实体类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; }

    }
}

1.3 创建配置类

1.3.1 对应数据库表

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<Book>
    {
        public void Configure(EntityTypeBuilder<Book> builder)
        {
            builder.ToTable("T_Books");
            builder.Property(b=>
                b.Title)
                .HasMaxLength(50).IsRequired();
            builder.Property(b=>
                b.AuthorName)
                .HasMaxLength(20).IsRequired();
        }
    }
}

1.3.2 对应数据库配置

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<Book> 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);
        }
    }
}

1.4 Migration

1.4.1 初始化

显示控制台

image.png

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

image.png

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

image.png image.png

1.4.2 添加修改

对 person 类添加了一个属性BirthPlace

控制台输入 Add-Migration AddBirth

image.png

Update-Database同步数据

image.png

1.5 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

image.png 
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<Book> 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 后

image.png

二、增删改查

2.1 增

2.1.1 添加一条图书信息

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();
            }
        }
    }
}

image.png

2.2 查

已知有如下数据：

image.png

2.2.1 查询价格大于 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();
            }
        }
    }
}

image.png

2.2.2 查询"西游记"的作者

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);

            }
        }
    }
}

image.png

2.2.3 价格升序

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);
                }

            }
        }
    }
}

image.png

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

修改部分数据

image.png

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}");
                }
            }
        }
    }
}

image.png

2.3 改

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

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();

            }
        }
    }
}

image.png

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

涨价前

image.png

涨价后

image.png

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 > 10);
                foreach(var b in books)
                {
                    b.Price = b.Price + 10;
                }
                await context.SaveChangesAsync();
            }
        }
    }
}

2.4 删

2.4.1 删除编号为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();

            }
        }
    }
}

image.png

2.5 复杂查询

2.5.1 模糊查询

查询标题中带有a的文章标题

image.png 
using System.Linq;

namespace EFCore
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            using (MyDBContext context = new MyDBContext())
            {
                var items = context.Books.Where(
                    c => c.Title.Contains("a"))
                    .Select(c => c.Title).Distinct();

                foreach(var item in items)
                {
                    await Console.Out.WriteLineAsync(item);
                }

            }
        }
    }
}

2.5.2 截取字符串

image.png 
using System.Linq;

namespace EFCore
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            using (MyDBContext context = new MyDBContext())
            {
                var items = context.Books.Select(c => new
                {
                    Id = c.Id,
                    Pre = c.Title.Substring(0,2) + "...."
                });
                foreach(var item in items)
                {
                    await Console.Out.WriteLineAsync(item.Id + item.Pre);
                }

            }
        }
    }
}

2.5.3 分页查询

  static void PrintPage(int pageIndex,int pageSize)
        {
            using(MyDBContext context = new MyDBContext())
            {
                IQueryable<Book> books = context.Books;
                var items = books.Skip((pageIndex - 1)*pageSize).Take(pageSize);
                foreach(var item in items)
                {
                    Console.WriteLine(item.Title);
                }

                // 总数
                var count = books.LongCount();
                // 页数
                long pageCount = (long)Math.Ceiling(count * 1.0 / pageSize);
                Console.WriteLine("总页数为"+pageCount);


            }
        }

每页查询3条，查询第二页

image.png # 三、表关系

3.1 一对一

3.1.1 表结构

3.1.1.1 订单表

image.png image.png

3.1.1.2 快递表

image.png image.png

3.1.2 测试

一个订单对于一个快递，创建数据后依次保存 image.png

3.1.3 数据库查看

快递表

image.png

订单表

image.png

3.2 多对多

一个学生有多个老师，一个老师有多个学生

3.2.1 表结构

3.2.1.1 学生表

image.png image.png

3.2.1.2 教师表

image.png image.png

3.2.2 测试一

测试数据插入

using EFCore;
using System;

namespace efcore5
{
    class Program
    {
        static void Main(string[] args)
        {
            using (MyDBContext context = new MyDBContext())
            {
                Student student1 = new Student
                {
                    Name = "zhangsan"
                };

                Student student2 = new Student
                {
                    Name = "lisis"
                };

                Student student3 = new Student
                {
                    Name = "wangwu"
                };

                Teacher teacher1 = new Teacher
                {
                    Name = "one"
                };

                Teacher teacher2 = new Teacher
                {
                    Name = "twe"
                };

                Teacher teacher3 = new Teacher
                {
                    Name = "three"
                };


                student1.Teachers.Add(teacher1);
                student1.Teachers.Add(teacher2);

                student2.Teachers.Add(teacher2);
                student2.Teachers.Add(teacher3);

                student3.Teachers.Add(teacher1);
                student3.Teachers.Add(teacher3);

                context.Teachers.Add(teacher1);
                context.Teachers.Add(teacher2);
                context.Teachers.Add(teacher3);

                context.Stuents.Add(student1);
                context.Stuents.Add(student2);
                context.Stuents.Add(student3);

                context.SaveChanges();

            }
        }
    }
}

3.2.3 数据库查看

学生表

image.png

教师表 image.png

中介表

image.png

3.2.4 测试二

查询所有的老师，并列出他们的学生

image.png 
using EFCore;
using Microsoft.EntityFrameworkCore;
using System;

namespace efcore5
{
    class Program
    {
        static void Main(string[] args)
        {
            using (MyDBContext context = new MyDBContext())
            {
                var Teachers = context.Teachers.Include(t => t.Students);
                foreach(var t in Teachers)
                {
                    Console.WriteLine(t.Name);
                    foreach(var student in t.Students)
                    {
                        Console.WriteLine("\t" + student.Name);
                    }
                }

            }
        }
    }
}
avatar
文章
阅读
粉丝