EntityFrameworkCore 配置

Nuget引用组件：

Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools

　连接字符串　appsetting.json

 "ConnectionStrings": {
    "DefaultConnection": "Server=DESKTOP-DABHN6U\\MSSQLSERVER2014;uid=sa;pwd=Lz38275292;database=SPA;MultipleActiveResultSets=true;"
  },

　　在Program.cs中注册

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString));

定义数据库上下文

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Options;
using SPA.Shared.Appointment;
using SPA.Shared.BasicInformation;
using SPA.Shared.Finance;
using SPA.Shared.Member;
using SPA.Shared.Reports;
using SPA.Shared.Stock;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SPA.Dal
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions options) : base(options)
        {

        }

        public DbSet<MemberInfo> Members { get; set; }
        public DbSet<SearchMemberInfo> SearchMembers { get; set; }
        public DbSet<MemberAccountInfo> MemberAccounts { get; set; }
        public DbSet<MemberAccountBindingItemInfo> MemberAccountBindingItemInfos { get; set; }
        public DbSet<RunningWaterAccountInfo> RunningWaterAccounts { get; set; }
        public DbSet<EmployeeInfo> Employees { get; set; }
        public DbSet<AppointmentResourceInfo> AppointmentResources { get; set; }
        public DbSet<AppointmentInfo> Appointments { get; set; }
        public DbSet<AppointmentViewInfo> AppointmentView { get; set; }
        public DbSet<StatuseInfo> Statuses { get; set; }
        public DbSet<AppointmentLabelInfo> AppointmentLabels { get; set; }
        public DbSet<UserInfo> Users { get; set; }
        public DbSet<Log> Logs { get; set; }
        public DbSet<ShopInfo> Shops { get; set; }
        public DbSet<AppointmentRoomInfo> AppointmentRooms { get; set; }
        public DbSet<CardHistoryConsumptionInfo> CardHistoryConsumptionInfos { get; set; }
        public DbSet<SalesOrderDetailsInfo> SalesOrderDetailsInfos { get; set; }
        public DbSet<StockInfo> StockInfos { get; set; }
        public DbSet<MemberLevelInfo> MemberLevelInfos { get; set; }
        public DbSet<RemindInformationInfo> RemindInformationInfos { get; set; }
        public DbSet<RemindCategoryInfo> RemindCategoryInfos { get; set; }
        public DbSet<ProductInfo> ProductInfos { get; set; }
        public DbSet<ProductCategroyInfo> ProductCategroyInfos { get; set; }
        public DbSet<BrandInfo> BrandInfos { get; set; }
        public DbSet<ServiceItemInfo> ServiceItemInfos { get; set; }
        public DbSet<ServiceItemCategoryInfo> ServiceItemCategoryInfos { get; set; }
        public DbSet<CardInfo> CardInfos { get; set; }
        public DbSet<CardCategoryInfo> CardCategoryInfos { get; set; }
        public DbSet<CardBindingItemsInfo> CardBindingItemsInfos { get; set; }
        public DbSet<AnotherNameInfo> AnotherNameInfos { get; set; }
        public DbSet<EmployeePerformanceInfo> EmployeePerformanceInfos { get; set; }
        public DbSet<PostInfo> PostInfos { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options.EnableSensitiveDataLogging(true);//错误信息中启用敏感数据记录
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            //设置组合主键
            List<string> keys = new List<string>();
            keys.Add(nameof(CardHistoryConsumptionInfo.会员编号));
            keys.Add(nameof(CardHistoryConsumptionInfo.流水号));
            keys.Add(nameof(CardHistoryConsumptionInfo.销售单明细ID));
            modelBuilder.Entity<CardHistoryConsumptionInfo>().HasKey(keys.ToArray());

            keys = new List<string>();
            keys.Add(nameof(StockInfo.年));
            keys.Add(nameof(StockInfo.月));
            keys.Add(nameof(StockInfo.会所编号));
            keys.Add(nameof(StockInfo.仓库编号));
            keys.Add(nameof(StockInfo.物品编号));
            modelBuilder.Entity<StockInfo>().HasKey(keys.ToArray());

            keys = new List<string>();
            keys.Add(nameof(AnotherNameInfo.表名));
            keys.Add(nameof(AnotherNameInfo.字段名));
            modelBuilder.Entity<AnotherNameInfo>().HasKey(keys.ToArray());

            keys = new List<string>();
            keys.Add(nameof(EmployeePerformanceInfo.流水号));
            keys.Add(nameof(EmployeePerformanceInfo.员工编号));
            modelBuilder.Entity<EmployeePerformanceInfo>().HasKey(keys.ToArray());

            // 配置表名映射
            modelBuilder.Entity<MemberInfo>().ToTable(MemberInfo.TableName);
            modelBuilder.Entity<MemberAccountInfo>().ToTable(MemberAccountInfo.TableName);
            modelBuilder.Entity<MemberAccountBindingItemInfo>().ToTable(MemberAccountBindingItemInfo.TableName);
            modelBuilder.Entity<RunningWaterAccountInfo>().ToTable(RunningWaterAccountInfo.TableName);
            modelBuilder.Entity<AppointmentResourceInfo>().ToTable(AppointmentResourceInfo.TableName);
            modelBuilder.Entity<AppointmentInfo>().ToTable(AppointmentInfo.TableName);
            modelBuilder.Entity<AppointmentViewInfo>().ToView(AppointmentViewInfo.TableName);
            modelBuilder.Entity<StatuseInfo>().ToTable(StatuseInfo.TableName);
            modelBuilder.Entity<AppointmentLabelInfo>().ToTable(AppointmentLabelInfo.TableName);
            modelBuilder.Entity<UserInfo>().ToTable(UserInfo.TableName);
            modelBuilder.Entity<ShopInfo>().ToTable(ShopInfo.TableName);
            modelBuilder.Entity<EmployeeInfo>().ToTable(EmployeeInfo.TableName);
            modelBuilder.Entity<AppointmentRoomInfo>().ToTable(AppointmentRoomInfo.TableName);
            modelBuilder.Entity<SearchMemberInfo>().ToTable(SearchMemberInfo.TableName);
            modelBuilder.Entity<Log>().ToTable(Log.TableName);
            modelBuilder.Entity<CardHistoryConsumptionInfo>().ToTable(CardHistoryConsumptionInfo.TableName);
            modelBuilder.Entity<SalesOrderDetailsInfo>().ToTable(SalesOrderDetailsInfo.TableName);
            modelBuilder.Entity<StockInfo>().ToTable(StockInfo.TableName);
            modelBuilder.Entity<MemberLevelInfo>().ToTable(MemberLevelInfo.TableName);
            modelBuilder.Entity<RemindInformationInfo>().ToTable(RemindInformationInfo.TableName);
            modelBuilder.Entity<RemindCategoryInfo>().ToTable(RemindCategoryInfo.TableName);
            modelBuilder.Entity<ProductInfo>().ToTable(ProductInfo.TableName);
            modelBuilder.Entity<ProductCategroyInfo>().ToTable(ProductCategroyInfo.TableName);
            modelBuilder.Entity<BrandInfo>().ToTable(BrandInfo.TableName);
            modelBuilder.Entity<ServiceItemInfo>().ToTable(ServiceItemInfo.TableName);
            modelBuilder.Entity<ServiceItemCategoryInfo>().ToTable(ServiceItemCategoryInfo.TableName);
            modelBuilder.Entity<CardInfo>().ToTable(CardInfo.TableName);
            modelBuilder.Entity<CardCategoryInfo>().ToTable(CardCategoryInfo.TableName);
            modelBuilder.Entity<CardBindingItemsInfo>().ToTable(CardBindingItemsInfo.TableName);
            modelBuilder.Entity<AnotherNameInfo>().ToTable(AnotherNameInfo.TableName);
            modelBuilder.Entity<EmployeePerformanceInfo>().ToTable(EmployeePerformanceInfo.TableName);
            modelBuilder.Entity<PostInfo>().ToTable(PostInfo.TableName);


        }
    }
}

　　客户端使用

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SPA.Shared.Member;
using SPA.Shared.CustomEnum;
using System.Data.Entity.Core.Objects;
using SPA.Shared;

namespace SPA.Dal.Member
{
    public class MemberDal
    {
        ApplicationDbContext db;
        public MemberDal(ApplicationDbContext db)
        {
            this.db = db;
        }

        public async Task<List<MemberInfo>> GetListAsync(SearchPageMemberInfo info)
        {
            List<MemberInfo> list = new List<MemberInfo>();
            string sql = GetPageSql(info);
            var result = db.Members.FromSqlRaw(sql);
            list = await result.Skip((info.PageIndex - 1) * info.PageSize).Take(info.PageSize).AsNoTracking().ToListAsync();
            return list;
        }
        private string GetPageSql(SearchPageMemberInfo info)
        {
            string sql = $"SELECT * FROM {MemberInfo.TableName} where {info.GetSqlWhere()} ";
            return sql;
        }
        public async Task<int> GetCountAsync(SearchPageMemberInfo info)
        {
            string countSql = $"SELECT 会员编号 FROM {MemberInfo.TableName} Where {info.GetSqlWhere()} ";
            int count = await db.Members.FromSqlRaw(countSql).CountAsync();
            return count;
        }
        public async Task<MemberInfo?> GetModelAsync(string code)
        {
            string sql = "select top 1 * FROM View_HY会员档案 where " + GetWhereBy(code);
            MemberInfo? model = await db.Members.FromSqlRaw<MemberInfo>(sql).FirstOrDefaultAsync();
            return model;
        }
        public string GetWhereBy(string code)
        {
            string sqlWhere = $" (会员编号 = '{code}' OR 姓名 LIKE '%{code}%' OR 手机 = '{code}'  OR 简码 LIKE '%{code}%' OR 会员卡编号 = '{code}' OR 会员卡表面号 = '{code}')";
            return sqlWhere;
        }
        /// <summary>
        /// 查询会员自动完成数据源
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public async Task<List<SearchMemberInfo>> Search(string value)
        {
            if (string.IsNullOrEmpty(value))
                return new List<SearchMemberInfo>();

            StringBuilder sqlWhere = new StringBuilder();
            sqlWhere.AppendFormat(" (会员编号='{0}' OR 姓名 LIKE '%{0}%' OR 手机 = '{0}'  OR 简码 LIKE '%{0}%' OR 会员卡编号='{0}' OR 会员卡表面号='{0}')", value.Trim());
            string sql = $"select top 7 会员编号,姓名,简码,手机,会员卡表面号 FROM View_HY会员档案 where 会员状态={Convert.ToInt32(MemberStatus.正常)} AND " + sqlWhere.ToString();
            var result = await db.SearchMembers.FromSqlRaw<SearchMemberInfo>(sql).ToListAsync();
            if (result != null)
            {
                result.ForEach(x => x.SetDisplayText());
            }
            return result;
        }
        /// <summary>
        /// 得到会员级别
        /// </summary>
        /// <returns></returns>
        public async Task<List<MemberLevelInfo>> GetMemberLevelInfoList()
        {
            List<MemberLevelInfo> list = await db.MemberLevelInfos.ToListAsync();
            List<MemberLevelInfo> resultList = new List<MemberLevelInfo>();
            resultList.Add(new MemberLevelInfo
            {
                编号 = ConstHelper.ALL_NO.ToString(),
                名称 = ConstHelper.ALL,
                分段起始值 = 0,
                分段终止值 = 0,
                备注 = String.Empty,
                级别图形 = String.Empty,
            });
            resultList.AddRange(list);
            return resultList;
        }
    }
}