efcore公共基础类Services

支持efcore，同时可以使用sql语句，实现精细化控制

C#
using Cowain.Base.DBContext;
using Cowain.Base.IServices;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using System.Data;
using System.Linq.Expressions;
using Dapper;

namespace Cowain.Base.Services;

public class BaseService : IBaseService
{
    protected readonly SqlDbContext _dBContext;
    public BaseService(SqlDbContext dbContext)
    {
        _dBContext = dbContext;

    }

    public int Delete<T>(int Id) where T : class
    {
        T? t = _dBContext.Set<T>().Find(Id);
        if (t == null) throw new KeyNotFoundException($"未找到类型 {typeof(T).Name}，Id={Id}");
        _dBContext.Set<T>().Remove(t);
        return _dBContext.SaveChanges();
    }

    public async Task<int> DeleteAsync<T>(int Id, CancellationToken cancellationToken = default) where T : class
    {
        var t = await _dBContext.Set<T>().FindAsync(Id);
        if (t == null) throw new KeyNotFoundException($"未找到类型 {typeof(T).Name}，Id={Id}");
        _dBContext.Set<T>().Remove(t);
        return await _dBContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }


    public int Delete<T>(T t) where T : class
    {
        // 参数校验，抛出更明确的异常类型
        ArgumentNullException.ThrowIfNull(t);
        // 获取实体跟踪状态，避免重复 Attach 导致不必要的状态变更
        var entry = _dBContext.Entry(t);
        if (entry.State == EntityState.Detached)
        {
            _dBContext.Set<T>().Attach(t);
        }
        // 标记删除并保存
        _dBContext.Set<T>().Remove(t);
        return _dBContext.SaveChanges();
    }

    public async Task<int> DeleteAsync<T>(T t, CancellationToken cancellationToken = default) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        var entry = _dBContext.Entry(t);
        if (entry.State == EntityState.Detached)
        {
            _dBContext.Set<T>().Attach(t);
        }
        _dBContext.Set<T>().Remove(t);
        return await _dBContext.SaveChangesAsync().ConfigureAwait(false);
    }

    public List<T> Find<T>() where T : class
    {
        return _dBContext.Set<T>().ToList();

    }

    // 异步、安全的批量读取（支持取消）
    public async Task<List<T>> FindAsync<T>(CancellationToken cancellationToken = default) where T : class
    {
        return await _dBContext.Set<T>().ToListAsync(cancellationToken).ConfigureAwait(false);
    }

    public T? Find<T>(int id) where T : class
    {
        return _dBContext.Set<T>().Find(id);

    }
    public async Task<T?> FindAsync<T>(int id, CancellationToken cancellationToken = default) where T : class
    {
        return await _dBContext.Set<T>().FindAsync(new object[] { id }, cancellationToken).ConfigureAwait(false);
    }

    public int Insert<T>(T t) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        _dBContext.Set<T>().Add(t);
        return _dBContext.SaveChanges();
    }

    public async Task<int> InsertAsync<T>(T t, CancellationToken cancellationToken = default) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        _dBContext.Set<T>().Add(t);
        return await _dBContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }

    public int Insert<T>(IEnumerable<T> tList) where T : class
    {
        _dBContext.Set<T>().AddRange(tList);
        return _dBContext.SaveChanges();
    }

    public async Task<int> InsertAsync<T>(IEnumerable<T> tList, CancellationToken cancellationToken = default) where T : class
    {
        _dBContext.Set<T>().AddRange(tList);
        return await _dBContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }
    public IQueryable<T> Query<T>(Expression<Func<T, bool>> funcWhere) where T : class
    {
        return _dBContext.Set<T>().Where<T>(funcWhere);
    }

    public int Update<T>(T t) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        var entry = _dBContext.Entry(t);
        if (entry.State == EntityState.Detached)
        {
            _dBContext.Set<T>().Attach(t);
        }
        _dBContext.Entry<T>(t).State = EntityState.Modified;
        return _dBContext.SaveChanges();
    }

    public async Task<int> UpdateAsync<T>(T t, CancellationToken cancellationToken = default) where T : class
    {
        ArgumentNullException.ThrowIfNull(t);
        var entry = _dBContext.Entry(t);
        if (entry.State == EntityState.Detached)
        {
            _dBContext.Set<T>().Attach(t);
        }
        _dBContext.Entry<T>(t).State = EntityState.Modified;
        return await _dBContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }


    public int Update<T>(IEnumerable<T> tList) where T : class
    {
        ArgumentNullException.ThrowIfNull(tList);
        var list = tList as IList<T> ?? tList.ToList();
        if (list.Count == 0) return 0;
        // 更简洁且更高效：让 EF Core 处理 Attach/状态设置
        _dBContext.Set<T>().UpdateRange(list);
        return _dBContext.SaveChanges();
    }

    public async Task<int> UpdateAsync<T>(IEnumerable<T> tList, CancellationToken cancellationToken = default) where T : class
    {
        ArgumentNullException.ThrowIfNull(tList);
        var list = tList as IList<T> ?? tList.ToList();
        if (list.Count == 0) return 0;
        _dBContext.Set<T>().UpdateRange(list);
        return await _dBContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
    }

    public DataTable GetDataTable(string sql, IEnumerable<MySqlParameter>? parameters = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        using var cmd = new MySqlCommand(sql, conn)
        {
            CommandType = CommandType.Text,
            // CommandTimeout = 30 // 根据需求设置超时（秒）
        };

        if (parameters != null)
        {
            foreach (var p in parameters) cmd.Parameters.Add(p);
        }
        using var adapter = new MySqlDataAdapter(cmd);
        var table = new DataTable();
        adapter.Fill(table);
        return table;
    }

    public async Task<DataTable> GetDataTableAsync(string sql, IEnumerable<MySqlParameter>? parameters = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));

        await using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);

        using var cmd = new MySqlCommand(sql, conn) { CommandType = CommandType.Text };
        if (parameters != null) cmd.Parameters.AddRange(parameters.ToArray());

        using var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
        var table = new DataTable();
        table.Load(reader); // 从 reader 同步加载数据
        return table;
    }

    // ================= Dapper 辅助方法 =================

    /// <summary>
    /// 使用 Dapper 查询并映射到 POCO（同步）
    /// </summary>
    public IEnumerable<T> QueryByDapper<T>(string sql, object? param = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        conn.Open();
        return conn.Query<T>(sql, param);
    }

    /// <summary>
    /// 使用 Dapper 查询并映射到 POCO（异步，支持 CancellationToken）
    /// </summary>
    public async Task<IEnumerable<T>> QueryByDapperAsync<T>(string sql, object? param = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        await using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
        var result = await conn.QueryAsync<T>(command).ConfigureAwait(false);
        return result;
    }

    /// <summary>
    /// 使用 Dapper 查询单条记录（同步）
    /// </summary>
    public T? QueryFirstOrDefaultByDapper<T>(string sql, object? param = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        conn.Open();
        return conn.QueryFirstOrDefault<T>(sql, param);
    }

    /// <summary>
    /// 使用 Dapper 查询单条记录（异步）
    /// </summary>
    public async Task<T?> QueryFirstOrDefaultByDapperAsync<T>(string sql, object? param = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        await using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
        return await conn.QueryFirstOrDefaultAsync<T>(command).ConfigureAwait(false);
    }

    /// <summary>
    /// 使用 Dapper 执行非查询 SQL（同步），返回受影响行数
    /// </summary>
    public int ExecuteByDapper(string sql, object? param = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        conn.Open();
        return conn.Execute(sql, param);
    }

    /// <summary>
    /// 使用 Dapper 执行非查询 SQL（异步），返回受影响行数
    /// </summary>
    public async Task<int> ExecuteByDapperAsync(string sql, object? param = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        await using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
        return await conn.ExecuteAsync(command).ConfigureAwait(false);
    }

    /// <summary>
    /// 使用 Dapper 执行 SQL 并返回 DataTable（异步，支持 CancellationToken）
    /// </summary>
    public async Task<DataTable> QueryToDataTableByDapperAsync(string sql, object? param = null, CancellationToken cancellationToken = default)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        await using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
        var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken, flags: CommandFlags.Buffered);
        await using var reader = await conn.ExecuteReaderAsync(command).ConfigureAwait(false);
        var table = new DataTable();
        table.Load(reader);
        return table;
    }

    /// <summary>
    /// 使用 Dapper 执行 SQL 并返回 DataTable（同步）
    /// </summary>
    public DataTable QueryToDataTableByDapper(string sql, object? param = null)
    {
        if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
        using var conn = new MySqlConnection(_dBContext.Database.GetConnectionString());
        conn.Open();
        using var reader = conn.ExecuteReader(sql, param);
        var table = new DataTable();
        table.Load(reader);
        return table;
    }


}