.net core 3.1 添加mysql ef core

1. 添加nuget管理包

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Design

Microsoft.EntityFrameworkCore.Relational

Pomelo.EntityFrameworkCore.MySql

2. 添加map映射

public class EatMap : IEntityTypeConfiguration<Eats>
    {
        public void Configure(EntityTypeBuilder<Eats> builder)
        {
            builder.ToTable("TbEat"); 
            builder.HasIndex(x => x.Milk); //索引
            builder.Property(x => x.Milk).HasMaxLength(200);
            builder.Property(x => x.Rice).HasColumnType("text");
        }
    }

3. 添加dbcontext

public class FcbDbContext:DbContext
    {
        public FcbDbContext(DbContextOptions options) :base(options)
        {

        }protected  void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new EatMap());
        }
        public DbSet<Eats> dbeats { get; set; }

    }

4. 注入服务

services.AddDbContext<FcbDbContext>(options =>
            {
                options.UseMySql(Configuration.GetSection("dbStr").Value, mySqlOptionsAction =>
                {
                    mySqlOptionsAction.MigrationsAssembly(typeof(Startup).Assembly.GetName().Name);
                });
            });

 

5. 添加自定义初始化migrations文件管道

public static void FcbInitData(this IApplicationBuilder app)
        {
            using (var scope = app.ApplicationServices.GetService<IServiceScopeFactory>().CreateScope())
            {
                //根据现有的migrations自动生成数据库（还是要先生成migrations文件，这两行代码相当于 “dotnet ef database update” 命令）
                var context = scope.ServiceProvider.GetRequiredService<FcbDbContext>();
                context.Database.Migrate();
                //初始化分页存储过程 此处可以忽略，这里可以初始化数据
                if (context.Database.IsMySql())
                {
                    var procSql = @"
                    DROP PROCEDURE IF EXISTS `query_pagination`;
                    CREATE PROCEDURE `query_pagination`(
                        in _fields varchar(2000),   
                        in _tables text, 
                        in _where varchar(2000),  
                        in _orderby varchar(200),
                        in _pageindex int,
                        in _pagesize int,
                        in _sumfields  varchar(200),/*增加统计字段2013-5-8 peaceli*/
                        out _totalcount int ,
                        out _pagecount int)
                    begin
                        set @startRow = _pageSize*(_pageIndex -1);
                        set @pageSize = _pageSize;
                        set @rowindex = 0;
                        set @strsql = CONCAT('select sql_calc_found_rows ',_fields,',@rowindex:=@rowindex+1 as rownumber from ',_tables,case ifnull(_where,'') when '' then '' else concat(' where ',_where) end,' order by ',_orderby,' limit ',@startRow,',',@pageSize);
                        prepare strsql from @strsql;
                        execute strsql;
                        deallocate prepare strsql;
                        set _totalcount = found_rows(); 

                        if (_totalcount <= _pageSize) then
                            set _pagecount = 1;
                        else
                            if (_totalcount % _pageSize > 0) then
                                set _pagecount = ceil(_totalcount / _pageSize);
                            else
                                set _pagecount = _totalcount / _pageSize;
                            end if;
                        end if;

                        if(ifnull(_sumfields,'') <> '') then
                            set @sumsql = contact('select ',_sumfields,' from ',_tables,case ifnull(_where,'') when '' then '' else concat(' where ',_where) end); prepare sumsql from @sumsql; execute sumsql; deallocate prepare sumsql;
                        end if; 
                    end
                    ";
                    context.Database.ExecuteSqlRaw(procSql);
                    context.SaveChanges();
                }
            }
        }

注入自定义管道

app.FcbInitData();

6. 通过cmd生成数据库

dotnet ef migrations add initdb -c FcbDbContext -o Migrations/dbdir 
dotnet ef database update