1

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

शेखर
  • 17,412
  • 13
  • 61
  • 117
patel.milanb
  • 5,822
  • 15
  • 56
  • 92

2 Answers2

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();
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
1

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()
Grundy
  • 13,356
  • 3
  • 35
  • 55
  • 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
  • 1
    Extension for IEnumerable, not IQueryable – Grundy Oct 17 '13 at 10:49
  • yup... i got it... i have to use AsEnumerable() – patel.milanb Oct 17 '13 at 11:06