LINQ to Entities sql equivalent "TOP (n) WITH TIES"

Question

I have been looking for LINQ equivalent WITH TIES in SQL Server recently, I came across a couple of things that might not be useful.

I know this question was asked earlier and has an accepted answer, but it does not work the way it is done with ties. A solution using GroupBy() does not match what expected for TOP(3) WITH TIES , given a dataset consisting of {3 2 2 1 1 0} , the result set will be {3 2 2 1 1} , where it should be {3 2 2}

Using the following data examples (taken from this question) :

 CREATE TABLE Person ( Id int primary key, Name nvarchar(50), Score float ) INSERT INTO Person VALUES (1, 'Tom',8.9) INSERT INTO Person VALUES (2, 'Jerry',8.9) INSERT INTO Person VALUES (3, 'Sharti',7) INSERT INTO Person VALUES (4, 'Mamuzi',9) INSERT INTO Person VALUES (5, 'Kamala',9) 

The traditional OrderByDescending(p => p.Score).Take(3) would look like this: Mamuzi , Kamala, and one from Tom (or Jerry ), where it should include BOTH

I know that there is no built-in equivalent, and I found a way to implement it. I do not know if this is the best way to do this and open up alternative solutions.

Answer 1

 var query = (from q in list.OrderByDescending(s => s.Score).Take(3).Select(s => s.Score).Distinct() from i in list where q == i.Score select i).ToList(); 

Edit:

@Zefnus

I was not sure in what order you wanted it, but to change the order, you can put OrderBy (s => s.Score) between select me and ToList ()

I have no way to verify which sql statement would create the linq clause. But your answer is much better, I think. And your question was also very good. I never thought about the top with bundles in linq .;)

Basically, these are only the top 3 ratings from the first list and compare them with the whole list, and I take only those ratings that match the ratings of the first list.

Answer 2

Do not use IEnumerable<T> anything related to the database!

A solution aimed at LinqToSql and LinqToEntities should not use IEnumerable<T> . Your current standalone answer will result in each individual being selected from the database and then requested in memory using LinqToObjects .

To make a decision translated into SQL and executed using a database, you must use IQueryable<T> and Expressions .

 public static class QueryableExtensions { public static IQueryable<T> TopWithTies<T, TComparand>(this IQueryable<T> source, Expression<Func<T, TComparand>> topBy, int topCount) { if (source == null) throw new ArgumentNullException("source"); if (topBy == null) throw new ArgumentNullException("topBy"); if (topCount < 1) throw new ArgumentOutOfRangeException("topCount", string.Format("topCount must be greater than 0, was {0}", topCount)); var topValues = source.OrderBy(topBy) .Select(topBy) .Take(topCount); var queryableMaxMethod = typeof(Queryable).GetMethods() .Single(mi => mi.Name == "Max" && mi.GetParameters().Length == 1 && mi.IsGenericMethod) .MakeGenericMethod(typeof(TComparand)); var lessThanOrEqualToMaxTopValue = Expression.Lambda<Func<T, bool>>( Expression.LessThanOrEqual( topBy.Body, Expression.Call( queryableMaxMethod, topValues.Expression)), new[] { topBy.Parameters.Single() }); var topNRowsWithTies = source.Where(lessThanOrEqualToMaxTopValue) .OrderBy(topBy); return topNRowsWithTies; } public static IQueryable<T> TopWithTiesDescending<T, TComparand>(this IQueryable<T> source, Expression<Func<T, TComparand>> topBy, int topCount) { if (source == null) throw new ArgumentNullException("source"); if (topBy == null) throw new ArgumentNullException("topBy"); if (topCount < 1) throw new ArgumentOutOfRangeException("topCount", string.Format("topCount must be greater than 0, was {0}", topCount)); var topValues = source.OrderByDescending(topBy) .Select(topBy) .Take(topCount); var queryableMinMethod = typeof(Queryable).GetMethods() .Single(mi => mi.Name == "Min" && mi.GetParameters().Length == 1 && mi.IsGenericMethod) .MakeGenericMethod(typeof(TComparand)); var greaterThanOrEqualToMinTopValue = Expression.Lambda<Func<T, bool>>( Expression.GreaterThanOrEqual( topBy.Body, Expression.Call(queryableMinMethod, topValues.Expression)), new[] { topBy.Parameters.Single() }); var topNRowsWithTies = source.Where(greaterThanOrEqualToMinTopValue) .OrderByDescending(topBy); return topNRowsWithTies; } } 

This creates queries of the following form:

 SELECT [t0].[Id], [t0].[Name], [t0].[Score] FROM [Person] AS [t0] WHERE [t0].[Score] >= (( SELECT MIN([t2].[Score]) FROM ( SELECT TOP (3) [t1].[Score] FROM [Person] AS [t1] ORDER BY [t1].[Score] DESC ) AS [t2] )) ORDER BY [t0].[Score] DESC 

This query is only 50% worse than the base query :

 SELECT TOP (3) WITH TIES [t0].[Id], [t0].[Name], [t0].[Score] FROM [Person] AS [t0] ORDER BY [t0].[Score] desc 

With a data set consisting of your initial 5 records and an additional 10,000 records, all with estimates less than the original, both of which are more or less instantaneous (less than 20 milliseconds).

The IEnumerable<T> approach took a full 2 minutes !

---

If the construction of the expression and the reflection seems intimidating, then the same thing can be achieved using a join:

 public static IQueryable<T> TopWithTiesDescendingJoin<T, TComparand>(this IQueryable<T> source, Expression<Func<T, TComparand>> topBy, int topCount) { if (source == null) throw new ArgumentNullException("source"); if (topBy == null) throw new ArgumentNullException("topBy"); if (topCount < 1) throw new ArgumentOutOfRangeException("topCount", string.Format("topCount must be greater than 0, was {0}", topCount)); var orderedByValue = source.OrderByDescending(topBy); var topNValues = orderedByValue.Select(topBy).Take(topCount).Distinct(); var topNRowsWithTies = topNValues.Join(source, value => value, topBy, (x, row) => row); return topNRowsWithTies.OrderByDescending(topBy); } 

As a result of query as a result (with approximately the same performance):

 SELECT [t3].[Id], [t3].[Name], [t3].[Score] FROM ( SELECT DISTINCT [t1].[Score] FROM ( SELECT TOP (3) [t0].[Score] FROM [Person] AS [t0] ORDER BY [t0].[Score] DESC ) AS [t1] ) AS [t2] INNER JOIN [Person] AS [t3] ON [t2].[Score] = [t3].[Score] ORDER BY [t3].[Score] DESC 

Answer 3

Another solution, which is probably not as efficient as another solution , is to get TOP(3) Scores and get rows with Score values ​​contained in TOP(3) .

We can use Contains() as follows:

 orderedPerson = datamodel.People.OrderByDescending(p => p.Score); topPeopleList = ( from p in orderedPerson let topNPersonScores = orderedPerson.Take(n).Select(p => p.Score).Distinct() where topNPersonScores.Contains(p.Score) select p ).ToList(); 

What is the use of this implementation is that the extension method TopWithTies() can be easily implemented as:

 public static IEnumerable<T> TopWithTies<T, TResult>(this IEnumerable<T> enumerable, Func<T, TResult> selector, int n) { IEnumerable<T> orderedEnumerable = enumerable.OrderByDescending(selector); return ( from p in orderedEnumerable let topNValues = orderedEnumerable.Take(n).Select(selector).Distinct() where topNValues.Contains(selector(p)) select p ); } 

Answer 4

I think that maybe you can do something like:

 OrderByDescending(p => p.Score).Skip(2).Take(1) 

Count the number of occurrences of this element, and then:

 OrderByDescending(p => p.Score).Take(2 + "The select with the number of occurrences for the third element") 

I think that maybe this works;) This is just an idea!

Answer 5

I found a solution that takes the value of the Score field for row N th (in this case the 3rd row) using .Skip(n-1).Take(1) and selecting all rows with a score greater than or equal to the following:

 qryPeopleOrderedByScore = datamodel.People.OrderByDescending(p => p.Score); topPeopleList = ( from p in qryPeopleOrderedByScore let lastPersonInList = qryPeopleOrderedByScore.Skip(2).Take(1).FirstOrDefault() where lastPersonInList == null || p.Score >= lastPersonInList.Score select p ).ToList();