Linq take distinct

Question

I am trying to implement a Linq to SQL operator (EF core)

q.TakeDistinct(n, src => src.Field)

So that it returns the first n elements from q only counting distinct Field

Basically I want a Take that will not count elements which do not differ in Field

For example:

Job Status
10   start
10   progress
10   alert
10   done
12   start
12   error
12   done
32   start
32   info
32   done

then

ctx.Table.TakeDistinct(2, v => v.Job)

will return:

10   start
10   progress
10   alert
10   done
12   start
12   error
12   done

I tried various combinations of Take and Distinct but no luck .. any leads appreciated.

Note I am looking for something that will translate to a reasonable efficient SQL by Linq-to-SQL (not in memory processing)

EDIT:

This works:

ctx.Table.Where(z => 
  Table.Select(x => x.Field).Distinct().Take(n)
  .Contains(z.Field)
);

If is however really slow (in LinqPad) .. is there a better way ?

@Llama - I strongly suspect OP is looking for SQL equivalent of https://stackoverflow.com/questions/489258/linqs-distinct-on-a-particular-property (distinct objects by property). — Alexei Levenkov, Sep 08 '21 at 03:16
@Llama `Distinct` actually will not give one objects anyway... You'd want DistinctBy from MoreLinq (or something similar). — Alexei Levenkov, Sep 08 '21 at 03:19

Le Vu · Answer 1 · 2021-09-08T09:20:27.513

I understand your idea. I wrote this extensions method for implement. But it need to optimize because abuse LinQ may impact performance.

public static IEnumerable<T> TakeDistinct<T, TProperty>(this IEnumerable<T> source, int take, Expression<Func<T, TProperty>> distinctBy)
    {
        var orderList = source.AsQueryable().GroupBy(distinctBy).OrderBy(x=>x.Key).Take(take).Select(x=>x.Key).ToList();
        var mappingFunc = distinctBy.Compile();
        return source.Where(x => orderList.Contains(mappingFunc.Invoke(x)));
    }

static void Main()
    {

        var jobLines = @"10   start
10   progress
10   alert
10   done
12   start
12   error
12   done
32   start
32   info
32   done";

        var jobs = jobLines.Split('\n').Select(line =>
        {
            var splited = line.Split(new[] { "   " }, StringSplitOptions.RemoveEmptyEntries);
            return new JobItem
            {
                Job = Convert.ToInt32(splited[0]),
                Status = splited[1]
            };
        });

        var newJobs = jobs.TakeDistinct(2, x => x.Job);
        foreach (var job in newJobs)
        {
            Console.WriteLine($"{job.Job} - {job.Status}");
        }
        Console.ReadLine();
    }

    class JobItem
    {
        public int Job { get; set; }
        public string Status { get; set; }
    }

    public static IEnumerable<T> TakeDistinct<T, TProperty>(this IEnumerable<T> source, int take, Expression<Func<T, TProperty>> distinctBy)
    {
        var orderList = source.AsQueryable().GroupBy(distinctBy).OrderBy(x=>x.Key).Take(take).Select(x=>x.Key).ToList();
        var mappingFunc = distinctBy.Compile();
        return source.Where(x => orderList.Contains(mappingFunc.Invoke(x)));
    }

--- UPDATED----

This update is to support LinQ to SQL

public static IQueryable<T> TakeDistinct<T, TProperty>(this IQueryable<T> source, int take, Expression<Func<T, TProperty>> distinctBy)
{
    var orderList = source.AsQueryable().GroupBy(distinctBy).OrderBy(x => x.Key).Take(take).SelectMany(x => x);
    return orderList;
}

--- UPDATED ----

I add generated SQL from statement above. I've created fake table name JobItems with Columns: Id, Job and Status

SELECT 
    [Extent2].[Id] AS [Id], 
    [Extent2].[Job] AS [Job], 
    [Extent2].[Status] AS [Status]
    FROM   (SELECT TOP (2) [Distinct1].[Job] AS [Job]
        FROM ( SELECT DISTINCT 
            [Extent1].[Job] AS [Job]
            FROM [dbo].[JobItems] AS [Extent1]
        )  AS [Distinct1]
        ORDER BY [Distinct1].[Job] ASC ) AS [Limit1]
    INNER JOIN [dbo].[JobItems] AS [Extent2] ON [Limit1].[Job] = [Extent2].[Job]

Thanks! I don't think this will translate well to SQL though :( — kofifus, Sep 08 '21 at 03:30
@kofifus: Please refer to my update at the end for Linq to SQL support. — Le Vu, Sep 08 '21 at 04:13
@kofifus: It depend on `distinctBy` parameters. `Key` is property of `IGrouping`. In this case, `Key` is `Job`. — Le Vu, Sep 08 '21 at 09:10
I can't get this to work, I tried quite a bit both in my code and in LinqPad (EFcore 6) : `MyTable.GroupBy(x=>x.Job).OrderBy(x => x.Key).Take(5).SelectMany(x => x)` but I get: "The LINQ expression 'x => x' could not be translated." — kofifus, Sep 08 '21 at 23:43

ataboo · Answer 2 · 2021-09-08T04:53:46.267

In SQL, I'd be looking at a WHERE IN subquery.

SELECT JobId, Status
FROM Jobs
WHERE Status In (SELECT DISTINCT TOP 2 Status FROM Jobs)

Not a lot of experience with LinqToSQL but this might work directly:

var TakeJobs = db.Jobs.Select(j => j.JobId).Distinct().Take(2);
var entries = db.Jobs.Where(j => TakeJobs.Contains(j.JobId));

Apparently this might be cheaper with a join to the subquery. I'm not sure how that looks in Linq.

SELECT JobId, Status FROM Jobs j
RIGHT JOIN (SELECT DISTINCT TOP 2 JobId FROM Jobs) t ON t.JobId = j.JobId;

score 1 · Answer 3 · answered Sep 08 '21 at 09:54

The following extension generates expression tree which makes Distinct().Take(x) and generates join to original query. It accepts also x => new { x.Id, x.Id2 } argument as distinct key.

public static class QueryableExtensions
{
    public static IQueryable<T> TakeDistinct<T, TKey>(this IQueryable<T> source, int take,
        Expression<Func<T, TKey>> distinctBy)
    {
        var distinctQuery = source.Select(distinctBy).Distinct().Take(take);

        var distinctParam = Expression.Parameter(typeof(TKey), "d");
        var entityParam   = distinctBy.Parameters[0];

        var mapping = MapMembers(distinctBy.Body, distinctParam).ToList();

        var whereExpr = mapping.Select(t => Expression.Equal(t.Item1, t.Item2))
            .Aggregate(Expression.AndAlso);
        var whereLambda = Expression.Lambda(whereExpr, entityParam);

        var selectManySelector =
            Expression.Lambda(
                Expression.Convert(
                    Expression.Call(typeof(Queryable), nameof(Queryable.Where), new[] { typeof(T) },
                        source.Expression,
                        whereLambda),
                    typeof(IEnumerable<T>)
                ),
                distinctParam
            );

        var selectManyQuery = Expression.Call(typeof(Queryable), nameof(Queryable.SelectMany),
            new[] { typeof(TKey), typeof(T) }, distinctQuery.Expression, selectManySelector);


        return source.Provider.CreateQuery<T>(selectManyQuery);
    }

    private static IEnumerable<Tuple<Expression, Expression>> MapMembers(Expression expr, Expression projectionPath)
    {
        switch (expr.NodeType)
        {
            case ExpressionType.New:
            {
                var ne = (NewExpression)expr;

                for (int i = 0; i < ne.Arguments.Count; i++)
                {
                    foreach (var e in MapMembers(ne.Arguments[i], Expression.MakeMemberAccess(projectionPath, ne.Members[i])))
                    {
                        yield return e;
                    }
                }
                break;
            }

            default:
                yield return Tuple.Create(projectionPath, expr);
                break;
        }
    }
}

What do you mean? It should translate to the SQL. Or you need instructions how to enable logging? — Svyatoslav Danyliv, Sep 09 '21 at 03:17
Tanks a lot. Is there a way to provide a use case and the SQL created for EF core ? I don't believe I can paste this into LinqPad ? — kofifus, Sep 13 '21 at 10:42
Why not? LINQPad just executes C# compiler and then your code. — Svyatoslav Danyliv, Sep 13 '21 at 10:56

Linq take distinct

3 Answers3

Linked