get the entire row using distinct on one column in LINQ

Question

I need to retrieve the entire rows collection using distinct on one column in LINQ.

i am getting the Distinct using GroupBy but it only selects the one column values instead i need entire row.

**this list returns the collection which is unique in ThumbnailAltText column BUT only returns that column rather than entire row values. so i had to use var instead of type

 var catlist = _db.ac_Categories
    .Where(c => c.VisibilityId == 0 && c.ThumbnailAltText != null
        && (!c.ThumbnailAltText.StartsWith("gifts/")
        && !c.ThumbnailAltText.StartsWith("email/")
        && !c.ThumbnailAltText.StartsWith("news/")
        && !c.ThumbnailAltText.StartsWith("promotions/")
        && !c.ThumbnailAltText.StartsWith("the-knowledge/"))).ToList()
        .GroupBy(c=> c.ThumbnailAltText.Trim().Distinct()).ToList();
                                .GroupBy(c=> c.ThumbnailAltText.Trim().Distinct()).ToList();

same thing does not work with type like this and i am getting error.

List<ac_Categories> catlist = _db.ac_Categories
        .Where(c => c.VisibilityId == 0 && c.ThumbnailAltText != null
            && (!c.ThumbnailAltText.StartsWith("gifts/")
            && !c.ThumbnailAltText.StartsWith("email/")
            && !c.ThumbnailAltText.StartsWith("news/")
            && !c.ThumbnailAltText.StartsWith("promotions/")
            && !c.ThumbnailAltText.StartsWith("the-knowledge/"))).ToList()
            .GroupBy(c=> c.ThumbnailAltText.Trim().Distinct()).ToList();
                                    .GroupBy(c=> c.ThumbnailAltText.Trim().Distinct()).ToList();

ERROR: The 'Distinct' operation cannot be applied to the collection ResultType of the specified argument.

EDIT: I need a collection and not the first record, ID and other columns contains diff values only ThumbnailAltText my contain duplicates

its a entity created using database first approach... its a very large table so cant provide that in here... — patel.milanb, Oct 17 '13 at 09:44

Tim Schmelter · Accepted Answer · 2013-10-17T10:29:18.350

3

Distinct on a string returns unique characters. I assume you want to return all rows, but every row should be unique according to the ThumbnailAltText, is that correct?

Then this should work, it returns simply the first row of each group:

var catlist = _db.ac_Categories
    .Where(c => c.VisibilityId == 0 && c.ThumbnailAltText != null
        && (!c.ThumbnailAltText.StartsWith("gifts/")
        && !c.ThumbnailAltText.StartsWith("email/")
        && !c.ThumbnailAltText.StartsWith("news/")
        && !c.ThumbnailAltText.StartsWith("promotions/")
        && !c.ThumbnailAltText.StartsWith("the-knowledge/")))
     .GroupBy(c=> c.ThumbnailAltText.Trim())
     .ToList()
     .Select(g => g.First())
     .ToList();

edited Oct 17 '13 at 10:29

answered Oct 17 '13 at 09:16

Tim Schmelter

450,073
74
686
939

is this going to return entire row or just one column values? – patel.milanb Oct 17 '13 at 09:27
1

@patel.milanb: it returns the entire row. – Tim Schmelter Oct 17 '13 at 09:29
let me try it and will let you know ... so if it returns the entire row i can use the return type as List right? instead of var – patel.milanb Oct 17 '13 at 09:30
this gives me error: The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead. – patel.milanb Oct 17 '13 at 10:16
@patel.milanb: Then try to prepend the `ToList` before the `Select` as you've already done in your code (edited my answer). – Tim Schmelter Oct 17 '13 at 10:29

score 1 · Answer 2 · answered Oct 17 '13 at 09:54

also you may use Distinct(IEnumerable, IEqualityComparer) extension like this

public class CategotiesEqualityComparer : IEqualityComparer<ac_Categories>
{
    public bool Equals(ac_Categories x, ac_Categories y)
    {
        return x.ThumbnailAltText.Trim() == y.ThumbnailAltText.Trim();
    }

    public int GetHashCode(ac_Categories obj)
    {
        return obj.ThumbnailAltText.Trim().GetHashCode();
    }
}

List<ac_Categories> catlist = _db.ac_Categories
    .Where(c => c.VisibilityId == 0 && c.ThumbnailAltText != null
        && (!c.ThumbnailAltText.StartsWith("gifts/")
        && !c.ThumbnailAltText.StartsWith("email/")
        && !c.ThumbnailAltText.StartsWith("news/")
        && !c.ThumbnailAltText.StartsWith("promotions/")
        && !c.ThumbnailAltText.StartsWith("the-knowledge/")))
    .Distinct(new CategotiesEqualityComparer())
    .ToList()

Error: LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[WebMgr.ac_Categories] Distinct[ac_Categories](System.Linq.IQueryable`1[WebMgr.ac_Categories], System.Collections.Generic.IEqualityComparer`1[WebMgr.ac_Categories])' method, and this method cannot be translated into a store expression. — patel.milanb, Oct 17 '13 at 10:20

get the entire row using distinct on one column in LINQ

2 Answers2