用NetCore + ReactJS 实现一个前后端分离的网站 (3) 服务层的实现以及数据库接入
1. 前言
原来这个系列是要做成仓储模式的，但是经过一些时间的观察，发现EFCore本来就是按照仓储模式设计的，不需要再实现仓储层了，所以从头改了一通(
)。
2. 数据库接入
实体类当然是放在Model工程中，但是Context文件放哪里呢，一般小项目直接放在API层，Migration也在应用层做。
但是仓储层也要用Context，所以把Context文件也放到了Model工程中。
这个项目的数据库需要实现Code First，所以要先定义实体，并通过EFCore的migration功能自动初始化表结构，同时添加一些种子数据。
2.1. Model
新建Models文件夹，把原本的Novel.cs移动进去并修改，设置字段属性，给EFCore生成建表语句。
Novel.cs
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace NovelTogether.Core.Model.Models
{
public class Novel
{
[Key]
public int ID { get; set; }
[Required]
[Column(TypeName = "nvarchar")]
[MaxLength(128)]
public string? Name { get; set; }
[Required]
[Column(TypeName = "nvarchar")]
[MaxLength(128)]
public string? Type { get; set; }
[Required]
[Column(TypeName = "nvarchar")]
[MaxLength(128)]
public string? SubType { get; set; }
[Required]
[Column(TypeName = "nvarchar")]
[MaxLength(1024)]
public string? Description { get; set; }
[Required]
public int CreatedBy { get; set; }
[Required]
public DateTime CreatedTime { get; set; }
public int? ModifiedBy { get; set; }
public DateTime? ModifiedTime { get; set; }
[Required]
public bool IsDeleted { get; set; }
}
}
2.2. DbContext
新建ORM文件夹，新建类NovelTogetherContext.cs
这里有几点要注意
- 构造函数接受一个参数，这个参数会在Program.cs通过添加服务方式设置(步骤3)。
- 创建数据库的时候提供了一些种子数据。
NovelTogetherContext.cs
using Microsoft.EntityFrameworkCore;
using NovelTogether.Core.Model.Models;
namespace NovelTogether.Core.Model.ORM
{
public class NovelTogetherContext: DbContext
{
public NovelTogetherContext(DbContextOptions<NovelTogetherContext> options):base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// 初始化种子数据
modelBuilder.AddNovelSeed();
}
public DbSet<Novel> Novel { get; set; }
}
}
2.3. 种子数据
添加种子数据，在ORM文件夹中新建ModelBuilderExtension.cs
ModelBuilderExtension.cs
using Microsoft.EntityFrameworkCore;
using NovelTogether.Core.Model.Models;
namespace NovelTogether.Core.Model.ORM
{
public static class ModelBuilderExtension
{
public static void AddNovelSeed(this ModelBuilder modelBuilder)
{
modelBuilder.Entity<Novel>().HasData(
new Novel { ID = 1, Name = "三国演义", Type = "古典名著", SubType = "", Description = "", CreatedBy = 1, CreatedTime = DateTime.Now, IsDeleted = false },
new Novel { ID = 2, Name = "红楼梦", Type = "古典名著", SubType = "", Description = "", CreatedBy = 1, CreatedTime = DateTime.Now, IsDeleted = false },
new Novel { ID = 3, Name = "水浒传", Type = "古典名著", SubType = "", Description = "", CreatedBy = 1, CreatedTime = DateTime.Now, IsDeleted = false },
new Novel { ID = 4, Name = "西游记", Type = "古典名著", SubType = "", Description = "", CreatedBy = 1, CreatedTime = DateTime.Now, IsDeleted = false }
);
}
}
}
2.4. 配置项
往appsettings.json中添加配置项
appsettings.json
"Config": {
"Database": {
"SqlServer": {
"InUse": true,
"ConnectionString": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=NovelTogether;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
}
}
}
2.5. 过滤器
在API工程中新建Filters文件夹，创建MigrationStartupFilter.cs，提供随程序启动执行Migration的服务。
MigrationStartupFilter.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.OpenApi.Writers;
namespace NovelTogether.Core.API.Filters
{
public class MigrationStartupFilter<TContext> : IStartupFilter where TContext : DbContext
{
public Action<IApplicationBuilder> Configure(Action<IApplicationBuilder> next)
{
return app =>
{
using (var scope = app.ApplicationServices.CreateScope())
{
foreach (var context in scope.ServiceProvider.GetServices<TContext>())
{
context.Database.SetCommandTimeout(200);
context.Database.Migrate();
}
}
next(app);
};
}
}
}
2.6. Migration
设置目标数据库的类型和连接字符串，并设置在程序启动的时候执行Migration。
先引用EFCore相关的两个库，前者是用来连接SqlServer数据库的，后者使用管理Migration的。
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Design
然后修改入口程序。
Program.cs
// get configuration
var configuration = builder.Configuration;
#region EFCore
builder.Services.AddDbContext<NovelTogetherContext>(option =>
{
// 因为NovelTogetherContext不在API工程中，需要手动指定Migration所在的程序集名称。
option.UseSqlServer(configuration.GetValue<string>("Config:Database:SqlServer:ConnectionString"), b => b.MigrationsAssembly("NovelTogether.Core.API"));
});
// 在程序启动的时候执行数据库的升级
builder.Services.AddTransient<IStartupFilter, MigrationStartupFilter<NovelTogetherContext>>();
#endregion
在API工程中创建Migration
Command
dotnet ef migrations add InitialCreate
以上步骤完毕之后，F5启动程序，就可以自动创建数据库。通过客户端工具连接数据库，可以看到初始化的四条数据。
下面我们接着完善仓储和服务层。
3. 服务层
3.1. IBaseService
在IService工程中新建文件Base，并新建接口IBaseService。这里面定义了基本的增删改查方法。
IBaseService.cs
using System.Linq.Expressions;
namespace NovelTogether.Core.IService.Base
{
public interface IBaseService<TEntity> where TEntity : class
{
Task<TEntity> SelectAsync(Expression<Func<TEntity, bool>> expression);
Task<List<TEntity>> SelectRangeAsync();
Task<List<TEntity>> SelectRangeAsync(Expression<Func<TEntity, bool>>? expression);
Task<List<TEntity>> SelectRangeAsync(Expression<Func<TEntity, bool>>? expression, Dictionary<string, bool> sortingColumnsWithDirection);
Task<List<TEntity>> SelectRangeAsync(Expression<Func<TEntity, bool>>? expression, Dictionary<string, bool> sortingColumnsWithDirection, int pageSize, int pageIndex);
Task<TEntity> AddAsync(TEntity entity);
Task<List<TEntity>> AddRangeAsync(List<TEntity> entities);
Task<TEntity> UpdateAsync(TEntity entity);
Task<List<TEntity>> UpdateRangeAsync(List<TEntity> entities);
Task<bool> DeleteAsync(TEntity entity);
Task<bool> DeleteRangeAsync(List<TEntity> entities);
}
}
3.2. INovelService
在IService工程的根目录新建接口INovelService，继承基接口IBaseService。
INovelService.cs
using NovelTogether.Core.IService.Base;
using NovelTogether.Core.Model.Models;
namespace NovelTogether.Core.IService
{
public interface INovelService: IBaseService<Novel>
{
}
}
3.3. BaseService
在Service工程中新建文件夹Base，并新建类BaseService，继承并实现基接口IBaseService，子类通过依赖注入得到NovelTogetherContext，然后通过构造函数传递给BaseService。
BaseService.cs
using Microsoft.EntityFrameworkCore;
using NovelTogether.Core.Common.ORM;
using NovelTogether.Core.IService.Base;
using NovelTogether.Core.Model.ORM;
using System.Linq.Expressions;
namespace NovelTogether.Core.Service.Base
{
public class BaseService<TEntity> : IBaseService<TEntity> where TEntity : class, new()
{
private DbContext dbContext;
#region 构造函数
public BaseService(NovelTogetherContext novelTogetherContext)
{
dbContext = novelTogetherContext;
}
#endregion
#region 公共方法
public async Task<TEntity> AddAsync(TEntity entity)
{
await dbContext.AddAsync(entity);
await dbContext.SaveChangesAsync();
return entity;
}
public async Task<List<TEntity>> AddRangeAsync(List<TEntity> entities)
{
await dbContext.AddRangeAsync(entities);
await dbContext.SaveChangesAsync();
return entities;
}
public async Task<bool> DeleteAsync(TEntity entity)
{
dbContext.Remove(entity);
await dbContext.SaveChangesAsync();
return true;
}
public async Task<bool> DeleteRangeAsync(List<TEntity> entities)
{
dbContext.RemoveRange(entities);
await dbContext.SaveChangesAsync();
return true;
}
public async Task<TEntity> SelectAsync(Expression<Func<TEntity, bool>> expression)
{
var entity = await dbContext.Set<TEntity>().Where(expression).FirstOrDefaultAsync();
return entity;
}
public async Task<List<TEntity>> SelectRangeAsync()
{
var entities = SelectRangeQuerable(null, new Dictionary<string, bool>());
return await entities.ToListAsync();
}
public async Task<List<TEntity>> SelectRangeAsync(Expression<Func<TEntity, bool>>? expression)
{
var entities = SelectRangeQuerable(expression, new Dictionary<string, bool>());
return await entities.ToListAsync();
}
public async Task<List<TEntity>> SelectRangeAsync(Expression<Func<TEntity, bool>>? expression, Dictionary<string, bool> sortingColumnsWithDirection)
{
var entities = SelectRangeQuerable(expression, sortingColumnsWithDirection);
return await entities.ToListAsync();
}
public async Task<List<TEntity>> SelectRangeAsync(Expression<Func<TEntity, bool>>? expression, Dictionary<string, bool> sortingColumnsWithDirection, int pageSize, int pageIndex)
{
var entities = SelectRangeQuerable(expression, sortingColumnsWithDirection);
entities = entities.Skip((pageIndex - 1) * pageSize).Take(pageSize);
return await entities.ToListAsync();
}
public async Task<TEntity> UpdateAsync(TEntity entity)
{
dbContext.Update(entity);
await dbContext.SaveChangesAsync();
return entity;
}
public async Task<List<TEntity>> UpdateRangeAsync(List<TEntity> entities)
{
dbContext.UpdateRange(entities);
await dbContext.SaveChangesAsync();
return entities;
}
#endregion
#region 私有方法
private IQueryable<TEntity> SelectRangeQuerable(Expression<Func<TEntity, bool>>? expression, Dictionary<string, bool> sortingColumnsWithDirection)
{
IQueryable<TEntity> entities;
if (expression == null)
entities = dbContext.Set<TEntity>().AsQueryable();
else
entities = dbContext.Set<TEntity>().Where(expression);
if (sortingColumnsWithDirection.Keys.Count > 0)
{
var first = sortingColumnsWithDirection.First();
IOrderedQueryable<TEntity> orderedEntities;
if (first.Value)
{
orderedEntities = entities.OrderBy(first.Key);
}
else
{
orderedEntities = entities.OrderByDescending(first.Key);
}
// remove first
sortingColumnsWithDirection.Remove(first.Key);
// sort left columns
foreach (KeyValuePair<string, bool> kvp in sortingColumnsWithDirection)
{
if (kvp.Value)
{
orderedEntities = orderedEntities.ThenBy(kvp.Key);
}
else
{
orderedEntities = orderedEntities.ThenByDescending(kvp.Key);
}
}
return orderedEntities;
}
return entities;
}
#endregion
}
}
3.4. NovelService
在Service工程的根目录新建类NovelService，继承BaseService。
NovelService.cs
using NovelTogether.Core.IService;
using NovelTogether.Core.Model.Models;
using NovelTogether.Core.Service.Base;
namespace NovelTogether.Core.Service
{
public class NovelService : BaseService<Novel>, INovelService
{
public NovelService(NovelTogetherContext context):base(context)
{
}
}
}
3.5. 修改NovelController.cs
NovelController.cs
using Microsoft.AspNetCore.Mvc;
using NovelTogether.Core.IService;
using NovelTogether.Core.Model.Models;
namespace NovelTogether.Core.API.Controllers
{
[ApiController]
[Route("[controller]")]
public class NovelController : Controller
{
private readonly INovelService _novelService;
// 通过构造函数注入依赖
public NovelController(INovelService novelService)
{
_novelService = novelService;
}
[HttpGet]
public async Task<Novel> Get(int id)
{
return await _novelService.SelectAsync(x => x.ID == id);
}
}
}
3.6. F5执行
4. 扩展方法
EntityFrameworkExtensions.cs
using System.Linq.Expressions;
using System.Reflection;
namespace NovelTogether.Core.Common.ORM
{
public static class EntityFrameworkExtensions
{
public static IOrderedQueryable<TSource> OrderBy<TSource>(this IQueryable<TSource> query, string propertyName)
{
var entityType = typeof(TSource);
//Create x=>x.PropName
var propertyInfo = entityType.GetProperty(propertyName);
ParameterExpression arg = Expression.Parameter(entityType, "x");
MemberExpression property = Expression.Property(arg, propertyName);
var selector = Expression.Lambda(property, new ParameterExpression[] { arg });
//Get System.Linq.Queryable.OrderBy() method.
var enumarableType = typeof(System.Linq.Queryable);
var method = enumarableType.GetMethods()
.Where(m => m.Name == "OrderBy" && m.IsGenericMethodDefinition)
.Where(m =>
{
var parameters = m.GetParameters().ToList();
return parameters.Count == 2;
}).Single();
//The linq's OrderBy<TSource, TKey> has two generic types, which provided here
MethodInfo genericMethod = method.MakeGenericMethod(entityType, propertyInfo.PropertyType);
// Call query.OrderBy(selector), with query and selector: x=> x.PropName
// Note that we pass the selector as Expression to the method and we don't compile it.
// By doing so EF can extract "order by" columns and generate SQL for it
var newQuery = (IOrderedQueryable<TSource>)genericMethod.Invoke(genericMethod, new object[] { query, selector });
return newQuery;
}
public static IOrderedQueryable<TSource> ThenBy<TSource>(this IOrderedQueryable<TSource> query, string propertyName)
{
var entityType = typeof(TSource);
//Create x=>x.PropName
var propertyInfo = entityType.GetProperty(propertyName);
ParameterExpression arg = Expression.Parameter(entityType, "x");
MemberExpression property = Expression.Property(arg, propertyName);
var selector = Expression.Lambda(property, new ParameterExpression[] { arg });
//Get System.Linq.Queryable.OrderBy() method.
var enumarableType = typeof(System.Linq.Queryable);
var method = enumarableType.GetMethods()
.Where(m => m.Name == "OrderBy" && m.IsGenericMethodDefinition)
.Where(m =>
{
var parameters = m.GetParameters().ToList();
return parameters.Count == 2;
}).Single();
//The linq's OrderBy<TSource, TKey> has two generic types, which provided here
MethodInfo genericMethod = method.MakeGenericMethod(entityType, propertyInfo.PropertyType);
// Call query.OrderBy(selector), with query and selector: x=> x.PropName
// Note that we pass the selector as Expression to the method and we don't compile it.
// By doing so EF can extract "order by" columns and generate SQL for it
var newQuery = (IOrderedQueryable<TSource>)genericMethod.Invoke(genericMethod, new object[] { query, selector });
return newQuery;
}
public static IOrderedQueryable<TSource> OrderByDescending<TSource>(this IQueryable<TSource> query, string propertyName)
{
var entityType = typeof(TSource);
//Create x=>x.PropName
var propertyInfo = entityType.GetProperty(propertyName);
ParameterExpression arg = Expression.Parameter(entityType, "x");
MemberExpression property = Expression.Property(arg, propertyName);
var selector = Expression.Lambda(property, new ParameterExpression[] { arg });
//Get System.Linq.Queryable.OrderBy() method.
var enumarableType = typeof(System.Linq.Queryable);
var method = enumarableType.GetMethods()
.Where(m => m.Name == "OrderByDescending" && m.IsGenericMethodDefinition)
.Where(m =>
{
var parameters = m.GetParameters().ToList();
return parameters.Count == 2;
}).Single();
//The linq's OrderByDescending<TSource, TKey> has two generic types, which provided here
MethodInfo genericMethod = method.MakeGenericMethod(entityType, propertyInfo.PropertyType);
// Call query.OrderByDescending(selector), with query and selector: x=> x.PropName
// Note that we pass the selector as Expression to the method and we don't compile it.
// By doing so EF can extract "order by" columns and generate SQL for it
var newQuery = (IOrderedQueryable<TSource>)genericMethod.Invoke(genericMethod, new object[] { query, selector });
return newQuery;
}
public static IOrderedQueryable<TSource> ThenByDescending<TSource>(this IOrderedQueryable<TSource> query, string propertyName)
{
var entityType = typeof(TSource);
//Create x=>x.PropName
var propertyInfo = entityType.GetProperty(propertyName);
ParameterExpression arg = Expression.Parameter(entityType, "x");
MemberExpression property = Expression.Property(arg, propertyName);
var selector = Expression.Lambda(property, new ParameterExpression[] { arg });
//Get System.Linq.Queryable.OrderBy() method.
var enumarableType = typeof(System.Linq.Queryable);
var method = enumarableType.GetMethods()
.Where(m => m.Name == "OrderByDescending" && m.IsGenericMethodDefinition)
.Where(m =>
{
var parameters = m.GetParameters().ToList();
return parameters.Count == 2;
}).Single();
//The linq's OrderByDescending<TSource, TKey> has two generic types, which provided here
MethodInfo genericMethod = method.MakeGenericMethod(entityType, propertyInfo.PropertyType);
// Call query.OrderByDescending(selector), with query and selector: x=> x.PropName
// Note that we pass the selector as Expression to the method and we don't compile it.
// By doing so EF can extract "order by" columns and generate SQL for it
var newQuery = (IOrderedQueryable<TSource>)genericMethod.Invoke(genericMethod, new object[] { query, selector });
return newQuery;
}
}
}
5.总结
这一节做了以下事情：
- 通过EFCore引入了数据库，实现Code First，并注册为服务。
- 创建服务层的基接口和基类，具体的服务类只需要简单继承基类就可以向上层提供服务。
- 数据访问层在BaseService中实现，子类把注入的NovelTogetherContext通过构造函数传给BaseService，然后访问数据库
下一节引入用户登录和授权前端访问API。
