linq distinct field

Question

how set distinct to this query

return _dataContext.ProjectPictures.Include("Projects").Include("ProjectPictureTypes").Where(p => p.ProjectPictureTypes.Id == 1).ToList();

i need something like

return _dataContext.ProjectPictures.Include("Projects").Include("ProjectPictureTypes").Where(p => p.ProjectPictureTypes.Id == 1).Distinct(p=>p.Projects.Id).ToList();

Answer 1

Hi,

as far as I understand you want all projects linked with "ProjectPictureTypes" where the ID is 1.

As far as I think you might want to have a look at grouping and an appropriate aggregate - whatever this is. A grouping on p.Projects.Id then is distinct for p.Projects.Id. The hard time may be the appropriate aggregate - if in sense of business logic there is one.

HTH

Answer 2

Assuming that you have your foreign key relationships set up the way you want them in the database, and assuming that your LINQ-to-SQL classes mirror those relationships exactly, it looks like you're wanting something like:

DataContext.Projects
    .Where(a => a.ProjectPictures.ProjectPictureType_ID == 1)
    .Distinct()
    .Select(a => a.Project_ID)
    .ToList()

I'm assuming that your database structure looks like this:

Projects                      ProjectPictures
========                      ===============
Project_ID (PK, int)          ProjectPicture_ID (PK, int)
                              Project_ID (FK, int)
                              ProjectPictureType_ID (FK, int)

ProjectPictureTypes
===================
ProjectPictureType_ID (PK, int)

Answer 3

You are going to have to write a custom IEqualityComparer

public class ProjectPicturesComparer: IEqualityComparer< ProjectPictures >
    {
        // Products are equal if their names and product numbers are equal.
        public bool Equals(ProjectPictures p, ProjectPictures p2)
        {
            return p.Projects.Id == p2.Projects.Id;
        }

        // If Equals() returns true for a pair of objects,
        // GetHashCode must return the same value for these objects.

        public int GetHashCode(ProjectPictures p)
        {
            // Calculate the hash code for the product.
            return  p.Projects.Id.GetHashCode();
        }

    }

then you would do

return _dataContext.ProjectPictures.Include("Projects").Include("ProjectPictureTypes").Where(p => p.ProjectPictureTypes.Id == 1)..Distinct(new ProjectPicturesComparer()).ToList()