DDWiki/分享/后端/DDD/9、EF Core使用Sql语句提高查询效率.md

一种会报错的写法

var macBreakerDatas = _breakerDataRepo.GetAll().Where(t => t.mac == mac.Mac);

var removedBreakerDatas = macBreakerDatas.Where(t => (DateTime.Now - DateTime.Parse(t.CreateTime)).Minutes >= 65);

_breakerDataRepo.RemoveRange(removedBreakerDatas);

不报错，但查询效率慢

var macBreakerDatas = _breakerDataRepo.GetAll().Where(t => t.mac == mac.Mac);

var removedBreakerDatas = macBreakerDatas.ToList().Where(t => (DateTime.Now - DateTime.Parse(t.CreateTime)).Minutes >= 65);

_breakerDataRepo.RemoveRange(removedBreakerDatas);

修改基础设施层

    public interface IOpenBreakerDataRepository : IRepository<BreakerData, Guid>
    {
        IQueryable<BreakerData> GetAll();
        IQueryable<BreakerData> GetAllBySql(string sql);
    }
    public class OpenBreakerDataRepository : Repository<BreakerData, Guid, OpenAPIContext>, IOpenBreakerDataRepository
    {
        public OpenBreakerDataRepository(OpenAPIContext context) : base(context)
        {

        }

        public IQueryable<BreakerData> GetAll()
        {
            return DbContext.BreakerDatas.Where(t => true);
            
        }

        public IQueryable<BreakerData> GetAllBySql(string sql)
        {
            return DbContext.BreakerDatas.FromSqlRaw(sql);
        }
    }

最后改成

string sixtyFiveAgoTimeStr = DateTime.Now.AddMinutes(-65).ToFullTimeStr();

string rawSql = $"select * from  breakerdatas where mac='{mac.Mac}' and str_to_date(CreateTime, '%Y-%m-%d %H:%i:%s') <= str_to_date('{sixtyFiveAgoTimeStr}', '%Y-%m-%d %H:%i:%s')";

var removedBreakerDatas = _breakerDataRepo.GetAllBySql(rawSql);
_breakerDataRepo.RemoveRange(removedBreakerDatas);