使用EntityFramework和Sqlsugar 查询最近插入的几条数据

齐向笛

2023-12-01

在上位机中, 经常需要查询最近的几条数据. 但是查询数据库默认为从最早的数据查询, 这时候仅仅需要将查询的表降序排列即可

Model

namespace Test
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    [Table("ActualData")]
    public partial class ActualData
    {
        public int Id { get; set; }

        public DateTime? InsertTime { get; set; }

        [StringLength(20)]
        public string VarName { get; set; }

        [StringLength(20)]
        public string Value { get; set; }

        [StringLength(50)]
        public string Remark { get; set; }
    }
}

用EntityFramework

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Test.Provider
{
    interface IProvider
    {
        List<ActualData> GetActualDatas();
        
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Test.Provider
{
    public class ModelsResponsitory : IProvider
    {

        private Entities _entities = new Entities();


        public List<ActualData> GetActualDatas()
        {
            return _entities.ActualData.OrderByDescending(i => i.Id).Take(2).ToList();
            
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Test.Provider;
using Test.Sugar;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            var entities = new ModelsResponsitory();
            List<ActualData> actualDatas = entities.GetActualDatas();

            //EntityFramework
            foreach (var item in actualDatas)
            {
                Console.WriteLine(item.Value);
            }

            //Sugar
            var sugarEntities = new SugarResponsitory();
            List<ActualData> sugaractualDatas = sugarEntities.GetActualDatas();
            foreach (var item in sugaractualDatas)
            {
                Console.WriteLine(item.Value);
            }

            Console.ReadKey();
        }
    }
}

使用 Sqlsugar

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Test.Sugar
{
    public interface IProviderSugar
    {
        List<ActualData> GetActualDatas();
    }
}

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;

namespace Test.Sugar
{
    public class SugarResponsitory : IProviderSugar
    {
        private static string connString = ConfigurationManager.ConnectionStrings["Entities"].ToString();
        SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = connString,
            DbType = DbType.SqlServer,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.SystemTable
        });
        public List<ActualData> GetActualDatas()
        {
            return db.Queryable<ActualData>().OrderBy(a => a.Id, OrderByType.Desc).Take(2).ToList();
        }
    }
}

AppData

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
  </startup>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <connectionStrings>
    <add name="Entities" connectionString="data source=.;initial catalog=ScadaData;persist security info=True;user id=sa;password=sasa;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

一些sugar的增删改查操作

using Entitys;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WinFrmMonitorTest1.DAL;

namespace WinFrmMonitorTest1.Service
{
    public class ModelRepository : IModelRepository
    {
        private SqlSugarClient _db = SqlSugarHelper.db;

        public SqlSugarClient Db { get => _db; set => _db = value; }


        /// <summary>
        /// Product
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>

        public Product GetProduct(int Id)
        {
            return _db.Queryable<Product>().First(p => p.Id == Id);
        }

        public List<Product> GetProducts()
        {
            return _db.Queryable<Product>().ToList();
        }

        public int InsertProduct(Product product)
        {
            int count = _db.Insertable(product).ExecuteCommand();//官方推荐的
            return count;
        }

        /// <summary>
        /// Report
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public Report GetReport(int Id)
        {
            return _db.Queryable<Report>().First(p => p.Id == Id);
        }

        public List<Report> GetReports()
        {
            return _db.Queryable<Report>().ToList();
        }

        /// <summary>
        /// admins
        /// </summary>
        /// <param name="user"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public Sysadmins GetSysadmin(string user ,string pwd)
        {
            return _db.Queryable<Sysadmins>().First(p => p.LoginName == user && p.LoginPwd == pwd);
        }

        public List<Sysadmins> GetSysadmins()
        {
            return _db.Queryable<Sysadmins>().ToList();
        }
        /// <summary>
        /// ActualData
        /// </summary>
        /// <param name="ActualData"></param>
        /// <returns></returns>

        public ActualData GetActualData(int Id)
        {
            return _db.Queryable<ActualData>().First(p => p.Id == Id);
        }

        public List<ActualData> GetActualDatas(DateTime t1, DateTime t2)
        {
            return _db.Queryable<ActualData>().Where(a => SqlFunc.Between(a.InsertTime, t1, t2)).ToList();
        }
        public int InsertActualData(List<ActualData> actualDatas)
        {
            int count = _db.Insertable(actualDatas).ExecuteCommand();//官方推荐的
            return count;
        }

        public ReportData GetReportData(int Id)
        {
            return _db.Queryable<ReportData>().First(p => p.Id == Id);
        }

        public List<ReportData> GetReportDatas(DateTime t1, DateTime t2)
        {
            return _db.Queryable<ReportData>().Where(a => SqlFunc.Between(a.InsertTime, t1, t2)).ToList();
        }

        public int InsertReportData(List<ReportData> reportDatas)
        {
            int count = _db.Insertable(reportDatas).ExecuteCommand();//官方推荐的
            return count;
        }
    }
}

一些entityFramework的操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class ActualDataProvider
    {
        private MainEntities db = new MainEntities();
        public int Delete(ActualData t)
        {
            if (t == null) return 0;
            var model = db.ActualData.ToList().FirstOrDefault(item => t.Id == item.Id);
            if (model == null) return 0;
            db.ActualData.Remove(model);
            int count = db.SaveChanges();
            return count;
        }

        public int Insert(List<ActualData> t)
        {
            if (t == null) return 0;
            //if (t.Value == null) return 0;
            foreach (var item in t.ToArray())
            {
                db.ActualData.Add(item);
            }
            int count = db.SaveChanges();
            return count;
        }

        public List<ActualData> Select()
        {
            var actualData = db.ActualData.Where(u=>System.Data.Objects.EntityFunctions.DiffDays(u.InsertTime, DateTime.Now)<1).ToList();
            return actualData;
        }

        public int Update(ActualData t)
        {
            if (t==null) return 0;
            var model = db.ActualData.ToList().FirstOrDefault(item=>t.Id== item.Id);
            if(model == null) return 0;
            model.InsertTime = t.InsertTime;
            model.Value=t.Value;
            model.Remark= t.Remark;
            model.InsertTime = t.InsertTime;
            int count = db.SaveChanges();
            return count;
        }
    }
}

一些entityFrameworkCore的操作

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using WebApplication1.Models;
using System.Threading.Tasks;

namespace WebApplication1.Services
{
    public interface ITouristRouteRespository
    {
        IEnumerable<TouristRoute> GetTouristRoutes(string keyword, string ratingOperator, int? ratingValue); //返回一组旅游路线
        TouristRoute GetTouristRoute(Guid touristRouteId); //返回单独旅游路线
        bool TouristRouteExists(Guid touristRouteId);
        IEnumerable<TouristRoutePicture> GetPicturesByTouristRouteId(Guid touristRouteId);
        TouristRoutePicture GetPicture(int pictureId);
        void AddTouristRoute(TouristRoute touristRoute);
        bool Save();
        void AddTouristRoutePicture(Guid touristRouteId, TouristRoutePicture touristRoutePicture);
        void DeleteTouristRoute(TouristRoute touristRoute);
        void DeleteTouristRoutePicture(TouristRoutePicture picture);
    }
}

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using WebApplication1.Database;
using WebApplication1.Models;

namespace WebApplication1.Services
{
    public class TouristRouteRepository : ITouristRouteRespository
    {
        private AppDbContext _context;

        public TouristRouteRepository(AppDbContext context)
        {
            _context = context;
        }

        //GET方法
        public IEnumerable<TouristRoutePicture> GetPicturesByTouristRouteId(Guid touristRouteId)
        {
            return _context.TouristRoutePictures.Where(p => p.TouristRouteId == touristRouteId).ToList();
        }

        public TouristRoutePicture GetPicture(int pictureId)
        {
            return _context.TouristRoutePictures.Where(p => p.Id == pictureId).FirstOrDefault();
        }

        public TouristRoute GetTouristRoute(Guid touristRouteId)
        {
            return _context.TouristRoutes.FirstOrDefault(n => n.Id == touristRouteId);
        }

        public IEnumerable<TouristRoute> GetTouristRoutes(string keyword, string ratingOperator, int? ratingValue)
        {
            IQueryable<TouristRoute> result = _context.TouristRoutes.Include(p => p.TouristRoutePictures);
            //如果关键字不为空, 则使用关键词查询
            if (!string.IsNullOrWhiteSpace(keyword))
            {
                keyword = keyword.Trim();
                result = result.Where(t => t.Title.Contains(keyword));
            }
            if (ratingValue >= 0)
            {
                //switch的新语法糖
                result = ratingOperator switch
                {
                    "largerThan" => result.Where(t => t.Rating >= ratingValue),
                    "lessThan" => result.Where(t => t.Rating <= ratingValue),
                    _ => result.Where(t => t.Rating == ratingValue), //default
                };
                //switch (ratingOperator)
                //{
                //    case "largerThan":
                //        result = result.Where(t => t.Rating >= ratingValue);
                //        break;
                //    case "lessThan":
                //        result = result.Where(t => t.Rating <= ratingValue);
                //        break;
                //    case "equalTo":
                //        result = result.Where(t => t.Rating == ratingValue);
                //        break;
                //    default:
                //        break;
                //}
            }
            return result.ToList();
        }

        //验证是否存在
        public bool TouristRouteExists(Guid touristRouteId)
        {
            return _context.TouristRoutes.Any(t => t.Id == touristRouteId);
        }

        //新增POST的方法
        public void AddTouristRoute(TouristRoute touristRoute)
        {
            if (touristRoute == null)
            {
                throw new ArgumentNullException(nameof(touristRoute)); //如果为空则抛出异常
            }
            _context.TouristRoutes.Add(touristRoute);
            //_context.SaveChanges();
        }
        public void AddTouristRoutePicture(Guid touristRouteId, TouristRoutePicture touristRoutePicture)
        {
            if (touristRouteId == Guid.Empty)
            {
                throw new ArgumentNullException(nameof(touristRouteId));
            }
            if (touristRoutePicture == null)
            {
                throw new ArgumentNullException(nameof(touristRoutePicture));
            }
            touristRoutePicture.TouristRouteId = touristRouteId;
            _context.TouristRoutePictures.Add(touristRoutePicture);
        }
        public bool Save()
        {
            return (_context.SaveChanges() >= 0);
        }

        //删除
        public void DeleteTouristRoute(TouristRoute touristRoute)
        {
            _context.TouristRoutes.Remove(touristRoute);
        }

        public void DeleteTouristRoutePicture(TouristRoutePicture picture)
        {
            _context.TouristRoutePictures.Remove(picture);
        }
    }
}

使用EntityFramework和Sqlsugar 查询最近插入的几条数据

相关阅读

相关文章

相关问答

相关文档