Linq With Entities List within Distinct List

Question

I have a SQL Query that gets Distinct rows of Vendor Contacts, which is returning the proper number of rows:

SELECT  DISTINCT v.VendorID, c.ContactID, 
    v.VendorName, 
    c.FirstName, c.MiddleName, c.LastName,      
FROM VendorContacts vc
INNER JOIN Contact c
    ON c.ContactID = vc.ContactID
INNER JOIN Vendor v
    ON v.VendorID = vc.VendorID
LEFT JOIN [ContactServices] psvc
    ON psvc.ContactID = c.ContactID
    AND psvc.VendorID = v.VendorID

I have a method that I want to return of custom type, based on the above query:

public List<ProviderContactInfo> GetProviderContactInfo(ProviderContactInfo searchInfo)
{
    using (var db = new MyContext()) 
    {
        var providerContactInfo =
              (from vc in db.VendorContacts
               join ps in db.ContactServices on new { vc.ContactID, vc.VendorID } equals new { ps.ContactID, ps.VendorID } into ps_join
               from ps in ps_join.DefaultIfEmpty()
               join c in db.Contacts on vc.ContactID equals c.ContactID
               join v in db.Vendors on vc.VendorID equals v.VendorID                                           
               orderby vc.ContactID descending

               select new ProviderContactInfo()
               {
                VendorName = v.VendorName,
                FirstName = c.FirstName,
                MiddleName = c.MiddleName,
                LastName = c.LastName,
                Services = (from o in db.ContactServices
                            join cps in db.Contacts on o.ContactID equals cps.ContactID
                            join vps in db.Vendors on o.VendorID equals vps.VendorID
                            join s in db.Services on o.ServiceID equals s.ServiceID
                            where ps.ServiceID == o.ServiceID
                            && o.ContactID == c.ContactID
                            && o.VendorID == v.VendorID
                            select s).ToList()
               }).Distinct().ToList();

        return providerContactInfo;
    }
}

I'm getting the error:

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

Everything works fine when I remove the Services property from the new ProviderContactInfo so I'm sure it's in the way I'm trying to populate that property (which is of type List<Service>)

I know there are a lot of questions regarding Linq with Distinct etc but I couldn't find anything on this specific problem.

Please help!

EDIT This code works:

public List<ProviderContactInfo> GetProviderContactInfo(ProviderContactInfo searchInfo)
    {
        using (var db = new MyContext()) 
        {
            var providerContactInfo =
                  (from vc in db.VendorContacts
                   join c in db.Contacts on vc.ContactID equals c.ContactID
                   join v in db.Vendors on vc.VendorID equals v.VendorID                                           
                   orderby vc.ContactID descending

                   select new ProviderContactInfo()
                   {
                    VendorName = v.VendorName,
                    FirstName = c.FirstName,
                    MiddleName = c.MiddleName,
                    LastName = c.LastName,
                    Services = (from o in db.ContactServices
                                join cps in db.Contacts on o.ContactID equals cps.ContactID
                                join vps in db.Vendors on o.VendorID equals vps.VendorID
                                join s in db.Services on o.ServiceID equals s.ServiceID
                                where o.ContactID == c.ContactID
                                && o.VendorID == v.VendorID
                                select s).ToList()
                   }).Distinct().ToList();

            return providerContactInfo;
        }
    }

Answer 1

You should add one more step to the process, to avoid applying Distinct() on the objects that have a collection (Services) property, the LINQ provider does not know how to handle it.

var providerContactInfo = from vc in db.VendorContacts
                          join ps in db.ContactServices on new { vc.ContactID, vc.VendorID } equals new { ps.ContactID, ps.VendorID } into ps_join
                          from ps in ps_join.DefaultIfEmpty()
                          join c in db.Contacts on vc.ContactID equals c.ContactID
                          join v in db.Vendors on vc.VendorID equals v.VendorID                                           
                          orderby vc.ContactID descending
                          group ps by new
                          {
                              v.VendorName,
                              c.FirstName,
                              c.MiddleName,
                              c.LastName,
                              c.ContactID,
                              v.VendorID
                          } into g
                          select new ProviderContactInfo()
                          {
                              VendorName = g.Key.VendorName,
                              FirstName = g.Key.FirstName,
                              MiddleName = g.Key.MiddleName,
                              LastName = g.Key.LastName,
                              Services = (from e in g
                                          from o in db.ContactServices
                                          join cps in db.Contacts on o.ContactID equals cps.ContactID
                                          join vps in db.Vendors on o.VendorID equals vps.VendorID
                                          join s in db.Services on o.ServiceID equals s.ServiceID
                                          where e.ServiceID == o.ServiceID
                                          && o.ContactID == g.Key.ContactID
                                          && o.VendorID == g.Key.VendorID
                                          select s).ToList()
                          }

Answer 2

Wouldn't this be much simplier:

public IQueryable<VendorContact> GetProviderContactInfo(ProviderContactInfo searchInfo)
{
  using (var db = new MyContext()) 
  {
    return providerContactInfo=db.VendorContacts
      .Include(vc=>vc.Contacts)
      .Include(vc=>vc.Services)
      .Include(vc=>vc.Vendor)
      .OrderByDescending(vc=>vc.ContactID);
  }
}