linq to sql是否具有with ties选项? [英] Does linq to sql have a with ties option?
问题描述
我正在尝试将以下查询移至Linq-to-sql,这可能吗?
I'm trying to move the following query to Linq-to-sql, is it possible?
select * from (
Select top (@Percent) percent with ties *
from(
Select distinct
LoanNumber as LoanNo
From CHE
Left Join RecordingInfo as Rec
On CHE.LoanNumber = Rec.LoanNo
Where Channel = 'LINX'
and CHE.Doc in ('MTG','MOD')
and Rec.LoanNo is null
and LoanNumber >= '@LoanNo'
) A
order by LoanNo @Order
) B
order by LoanNo
我还没有发现与linq中的关系有关.
I have not seen anyway to do with ties in linq.
推荐答案
我认为类似的方法对您有用.
I think something like this will work for you.
public static IQueryable<T> TopPercentWithTies<T, TKey>(this IOrderedQueryable<T> query, Expression<Func<T, TKey>> groupByExpression, double percent)
{
var groupedQuery = query.GroupBy(groupByExpression);
int numberToTake = groupedQuery.Count() * percent / 100;
return groupedQuery.Take(numberToTake).SelectMany(t => t);
}
我仅使用IEnumerable对其进行了测试,因此我不确定它是否可以与IQueryable一起正常使用.在调用TopPercentWithTies()之前,我还对列表进行了排序.
I only tested it with IEnumerable, so I don't know for sure that it'll work properly with IQueryable. I also sorted the list before calling TopPercentWithTies().
这是我用来测试的代码.
Here's the code I used to test it.
int percent = 50;
var people = new []
{
new { Age = 99, Name = "Adam" },
new { Age = 99, Name = "Andrew" },
new { Age = 89, Name = "Bob" },
new { Age = 50, Name = "Cecil" },
new { Age = 50, Name = "Doug" },
new { Age = 50, Name = "Everett" },
new { Age = 35, Name = "Frank" },
new { Age = 25, Name = "Greg" },
new { Age = 15, Name = "Hank" }
};
var sortedPeople = people.AsQueryable().OrderByDescending(person => person.Age);
var results = sortedPeople.TopPercentWithTies(person => person.Age, percent);
foreach (var person in results)
Console.WriteLine(person);
希望它可以帮助或至少使您朝正确的方向发展.您可能需要调整用于计算numberToTake的逻辑.
Hope it helps or at least gets you in the right direction. You may want to tweak the logic for calculating numberToTake.
这篇关于linq to sql是否具有with ties选项?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!