CODEWITHSUNDEEP
linqentity-frameworklinq-to-entitiesdistinct
tcode
tcode

Reputation: 5105

Linq To Entities Optional Distinct

Earlier I put a question on Stackoverflow about how to remove duplicate records in a list of objects, based on a particular property within each object.

I got the answer I was looking for (see below), a query which returns a distinct list of objects using MainHeadingID as the property to remove duplicates.

public IList<tblcours> GetAllCoursesByOrgID(int id)
{
    return _UoW.tblcoursRepo.All.
        Where(c => c.tblCourseCategoryLinks.Any(cl => cl.tblUnitCategory.tblUnit.ParentID == id))
        .GroupBy(c => c.MainHeadingID)
        .Select(g => g.FirstOrDefault())
        .ToList();
}

However, now I need more help! Is there anyway of amending the query above so that, it only removes duplicate values when MainHeadingID is not equal to 180. I tried amending GroupBy line to

.GroupBy(c => c.MainHeadingID != 180)

However, this didn't work.

Any help would be much appreciated with this.

Thanks.

Upvotes: 2

Views: 2079

Answers (2)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236248

Following works for LINQ to SQL:

return _UoW.tblcoursRepo.All
   .Where(c => c.tblCourseCategoryLinks.Any(cl => cl.tblUnitCategory.tblUnit.ParentID == id))
   .GroupBy(c => c.MainHeadingID)
   //.SelectMany(g => g.Key == 180 ? g : g.Take(1))
   .SelectMany(g => g.Take(g.Key == 180 ? Int32.MaxValue : 1))
   .ToList();

Comments: SelectMany in query above selects all items from group where MainHeadingID equals to 180, but it takes only one item form other groups (i.e. distinct result). Linq to SQL cannot translate commented out part, but thanks to @usr there is way around.

Linq to Entities cannot translate even simplified query. I think only option for you in this case is simple concating result of two queries:

Expression<Func<tblcours, bool>> predicate = x =>
  x.tblCourseCategoryLinks.Any(cl => cl.tblUnitCategory.tblUnit.ParentID == id)
int headingId = 180;

return _UoW.tblcoursRepo.All
   .Where(c => c.MainHeadingID != headingId)
   .Where(predicate)
   .GroupBy(c => c.MainHeadingID)
   .Select(g => g.FirstOrDefault())
   .Concat(_UoW.tblcoursRepo.All
               .Where(c => c.MainHeadingID == headingId)
               .Where(predicate))
   .ToList();

Upvotes: 2

usr
usr

Reputation: 171188

lazyberezovsky's answer fails due to an EF bug (which is not surprising given the quality of EF's LINQ support). It can be made to work with a hack:

.SelectMany(g => g.Key == 180 ? g.Take(int.MaxValue) : g.Take(1))

or

.SelectMany(g => g.Take(g.Key == 180 ? int.MaxValue : 1))

Note that performance will not be particularly good due to the way this is translated to SQL.

Upvotes: 2

Related Questions