问题描述
在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);
这篇关于防止实体框架中的联合排序结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!