Member Avatar for Mike Askew 
IQueryable<CustomObject> obj = (from table in db.TableName
                                                        orderby table.ObjectId descending
                                                        select
                                                            new CustomObject
                                                            {
                                                                 //Properties mapped here
                                                            }).Skip((pageNumber - 1) * pageSize).Take(pageSize);

The above is a stripped down version of the query I am running. It is missing about 10 joins and then just the bulk mapping of the object, which I have stripped out for confidentiality.

When run:
- pageNumber = 1
- pageSize = 500

table.ObjectId is a PK and so assumable to be unique

The query returns 500 objects into the IQueryable object, however instead of these 500 entries being unique I appear to get about 19 rows from the database duplicated across the 500 rows.

Is there an obvious reason as to why as I cannot find anything on Google regarding it.

Results in the object fit the following kind of pattern:

DBRow1
DBRow1
DBRow1
DBRow1
DBRow1
DBRow1
DBRow1
DBRow1
DBRow1
DBRow1
DBRow2
DBRow2
DBRow2
DBRow2
DBRow2
DBRow2
DBRow2
DBRow2
DBRow2
DBRow3
etc..

Edited by Mike Askew
  • 1 Contributor
  • 1 Reply
  • 209 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by Mike Askew

Dani AI

Generated

Good catch by — duplicate DTO rows almost always come from joining a one‑to‑many child table: the parent row is repeated once per matching child row, and a flat projection will produce repeated objects. Using a custom IEqualityComparer with Distinct() removes the duplicates, but note that the Distinct overload that accepts a comparer is an Enumerable (in‑memory) operation. That means the query must be materialized (AsEnumerable/ToList) before deduplication, which can be expensive if the joined result set is large.

A safe comparer for a DTO keyed by the primary key looks like this:

class CustomObjectComparer : IEqualityComparer<CustomObject>
{
    public bool Equals(CustomObject x, CustomObject y)
    {
        if (ReferenceEquals(x, y)) return true;
        if (x == null || y == null) return false;
        return x.ObjectId == y.ObjectId;
    }

    public int GetHashCode(CustomObject obj)
    {
        return obj == null ? 0 : obj.ObjectId.GetHashCode();
    }
}

A more scalable pattern is two‑step paging: first page the distinct primary keys on the server, then fetch the full objects for those keys. This keeps paging server‑side and avoids returning multiplied rows:

var keys = db.TableName
    .OrderByDescending(t => t.ObjectId)
    .Select(t => t.ObjectId)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();

var items = db.TableName
    .Where(t => keys.Contains(t.ObjectId))
    .OrderByDescending(t => t.ObjectId)
    .Select(t => new CustomObject { /* map parent fields only */ })
    .ToList();

Notes and gotchas: reapply ordering in the second query if result order matters; joining to children in the second query can still produce duplicates unless children are loaded separately and attached in memory (or aggregated server‑side). Contains becomes an SQL IN clause — very large key lists hit SQL parameter limits, so use batching, TVPs or a temp table for big pages. For small page sizes the two‑step approach is typically the cleanest and most predictable solution.

Member Avatar for Mike Askew

The issue turned out to be my misunderstanding of how joins work with .Net and LINQ.

The duplicate rows were the rows joined to the original table. To remove them I had to implement a custom IEqualityComparer to provide to the .Distinct() call.

I can pad out the explanation a bit if anyone needs it.

Edited by Mike Askew
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.