ef.core Mysql

• Entity层

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Runtime.Serialization;
using System.Text;

namespace Entity.Core
{
    /// <summary>
    /// DB表基础属性
    /// </summary>
    public abstract class BaseEntity<T>
    {
        public BaseEntity()
        {
            CreteTime = DateTime.Now;
        }
        /// <summary>
        /// 主键Id
        /// </summary>
        [DataMember]
        [Key]
        public T Id { get; set; }

        /// <summary>
        /// DB版号,Mysql详情参考;http://www.cnblogs.com/shanyou/p/6241612.html
        /// </summary>
        //[Timestamp]//Mysql不允许byte[]类型上标记TimeStamp/RowVersion，这里使用DateTime类型配合标记ConcurrencyCheck达到并发控制
        [ConcurrencyCheck]
        public DateTime RowVersion { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime CreteTime { get; set; }
    }
}

using Entity.Core;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Text;

namespace Entity.Table
{
    /// <summary>
    /// 商品类
    /// </summary>
    public class Product : BaseEntity<long>
    {
        /// <summary>
        /// 名称
        /// </summary>
        [StringLength(20)]
        [Required]
        public string Name { get; set; }

        /// <summary>
        /// 描述
        /// </summary>
        [StringLength(500)]
        [Required]
        public string Description { get; set; }

        /// <summary>
        /// 类别
        /// </summary>
        [Range(1, int.MaxValue)]
        public int Category { get; set; }

        /// <summary>
        /// 原价
        /// </summary>
        [Required]
        public decimal Price { get; set; }

        /// <summary>
        /// 现价
        /// </summary>
        public decimal Discount { get; set; }
    }
}

• 在DAL层添加以下引用

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Tools

Microsoft.EntityFrameworkCore.Relational

Pomelo.EntityFrameworkCore.MySql

using Entity;
using Entity.Table;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAL
{
    public class ProductContext : DbContext
    {
        //https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/complex-data-model
        public ProductContext(DbContextOptions<ProductContext> options) : base(options)
        {
            //在此可对数据库连接字符串做加解密操作
        }

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

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }
}

• Service 层

添加引用 Microsoft.EntityFrameworkCore.UnitOfWork

using System;
using System.Collections.Generic;
using System.Text;

namespace Service.ProductService
{
    public interface IProductService
    {
        string Test();
    }
}

using Entity.Table;
using Microsoft.EntityFrameworkCore;

namespace Service.ProductService
{
    public class ProductService : IProductService
    {
        private readonly IUnitOfWork _unitOfWork;
        public ProductService(IUnitOfWork unitOfWork)
        {
            _unitOfWork = unitOfWork;
        }

        public string Test()
        {
            var repo = _unitOfWork.GetRepository<Product>();
            repo.Insert(new Product
            {
                Category = 1,
                Description = "此商品为澳洲代购,买不了吃亏买不了上当",
                Discount = (decimal)899.21,
                Price = (decimal)98.2,
                Name = "澳洲袋鼠粉",
            });
            _unitOfWork.SaveChanges();//提交到数据库
            var result = repo.GetFirstOrDefault()?.Description ?? string.Empty;
            return result;
        }
    }
}

• appsettings.json 的配置

{
  "ConnectionStrings": {
    "MySqlConnection": "Server=localhost;database=ProjectManager;uid=root;pwd=password;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}

• Startup 的配置

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ProductContext>(options =>
                options.UseMySql(Configuration.GetConnectionString("MySqlConnection")));//添加Mysql支持   using Microsoft.EnityFrameworkCore

            services.AddUnitOfWork<ProductContext>();//添加UnitOfWork支持
            services.AddScoped(typeof(IProductService), typeof(ProductService));//用ASP.NET Core自带依赖注入(DI)注入使用的类

            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
        }

• 有关数据库迁移命令 (注意要选择保护DbContext项目, 视图 -> 其它窗口 ->  程序包管理器控制台)

add-migration init  (注: 初始化)

update-database

add-migration changeProductTable

remove-migration

如果出现找不到表  __efmigrationshistory, 则运行以下SQL

CREATE TABLE `__EFMigrationsHistory` (
  `MigrationId` varchar(95) NOT NULL,
  `ProductVersion` varchar(32) NOT NULL,
  PRIMARY KEY (`MigrationId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;