使用所需输出运行SQL查询的实体框架
本文关键字:查询 实体 框架 SQL 运行 输出 | 更新日期: 2023-09-27 18:28:38
使用Linq时,我无法使用SQL全文搜索功能,因此我想在DbSet上使用SqlQuery方法,该方法允许我编写原始SQL查询。
让我们考虑以下代码:
IQueryable<Message> query = DBContext.Set<Message>();
PagedList<Message> messages = query.Where(x => x.Content.Contains("magic"))
.OrderBy(x => x.CreatedDate)
.ToPagedList<Message>(50, 2);
如果我想运行以下SqlQuery查询:
DBContext.Set<Message>().SqlQuery("SELECT * FROM Message WHERE CONTAINS("Content", 'magic')");
SqlQuery方法返回IEnumerable<Message>
,我希望返回PagedList<Message>
对象。实现这一目标的最佳(好)方式是什么?请注意,我希望我的数据库执行SQL查询,而不是在内存中进行筛选。
PagedList实现
public class PagedList<T> : List<T>, IPagedList
{
public PagedList()
{
this.TotalCount = 0;
this.PageSize = 0;
this.PageIndex = 0;
this.TotalPages = 0;
this.CurrentPage = 0;
}
public PagedList(IQueryable<T> source, int pageIndex, int pageSize)
{
this.TotalCount = source.Count();
this.PageSize = pageSize;
this.PageIndex = pageIndex;
this.TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize);
this.CurrentPage = Math.Max(1, pageIndex);
this.AddRange(source.Skip((this.CurrentPage - 1) * pageSize).Take(pageSize).ToList());
}
public PagedList(List<T> source, int index, int pageSize)
{
this.TotalCount = source.Count();
this.PageSize = pageSize;
this.PageIndex = index;
this.TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize);
this.CurrentPage = Math.Max(1, index);
this.AddRange(source.Skip(index * pageSize).Take(pageSize).ToList());
}
public int TotalCount { get; set; }
public int TotalPages { get; set; }
public int PageSize { get; set; }
public int PageIndex { get; set; }
public int CurrentPage { get; set; }
}
分页列表扩展方法:
public static PagedList<T> ToPagedList<T>(this IQueryable<T> query, int pageIndex, int pageSize) where T : class
{
return new PagedList<T>(query, pageIndex, pageSize);
}
你试过简单地做吗
DBContext.Set<Message>().SqlQuery("SELECT * FROM Message WHERE CONTAINS("Content", 'magic')")
.AsQueryable()
.ToPagedList<Message>(50, 2);