防止实体框架中的联合排序结果 [英] Prevent sort result of union in entity framework
问题描述
在SQL Server union
中,结果基于主键列进行排序。我想在实体框架中防止这种行为。
在这篇文章中,@ praveen解释了如何在纯SQL中做到这一点。但是我想在实体框架中执行此操作。
我的代码:
In SQL server union
, result is sorted based on primary key column. I want to prevent this behavior in entity framework.
In this post, @praveen has explained how to do this in pure sql. But I want to do this in entity framework.
My code:
public virtual ActionResult Search(string keyword)
{
var products = _db.Products
.Where(x => x.IsActive)
.AsQueryable();
var productExactlyTitle = products.Where(x => x.Title == keyword);
var productStartTitle = products.Where(x => x.Title.StartsWith(keyword));
var productContainsTitle = products.Where(x => x.Title.Contains(keyword)
|| x.Title.Contains(keyword)
|| x.SubTitle.Contains(keyword)
|| x.OtherName.Contains(keyword));
var productList = productExactlyTitle.Union(productStartTitle)
.Union(productContainsTitle)
.Take(10)
.AsEnumerable()
.Select(x => new ProductItemViewModel()
{
Id = x.Id,
Title = x.Title,
Price = x.Price.ToPrice(),
Image = x.Images.FirstOrDefault(y => y.IsCoverPhoto)?.ImageUrl
});
// some code ...
}
I想要显示具有以下顺序的记录:
I want to show records with below order:
第一: productExactlyTitle的记录
第二: productStartTitle的记录
第三: productContainsTitle 的记录
但是结果按 Id排序
列!
有办法吗?
推荐答案
在SQL中,所有未明确设置 order 的查询都被认为是无顺序的。 (并且EF查询翻译成SQL)。因此,如果您要在联合后指定特定的订单,则只需指定它即可。
In SQL all queries without an order by
explicitly set is considered unordered. (and EF queries a translated into SQL). So if you want a specific order after your union just specify it.
var result = q1.Union(q2).OrderBy(x => x.?);
对于您的特定情况:
var p1 = productExactlyTitle.Select(x => new { Item = x, Order = 1 });
var p2 = productStartTitle.Select(x => new { Item = x, Order = 2 });
var p3 = productContainsTitle.Select(x => new { Item = x, Order = 3 });
var productList = p1.Union(p2)
.Union(p3)
.OrderBy(x => x.Order)
.Select(x => x.Item)
.Take(10);
这篇关于防止实体框架中的联合排序结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!