The text data type cannot be selected as DISTINCT because it is not comparable

Question

I'm having a problem because there's an old third party database I'm trying to connect to and retrieve data from that uses the now deprecated text field. However I can't change the database fields so it's causing me problems when I try to return data through LINQ. Example code below:

var query = from s in db.tSearches
            join c in db.tCompanies on s.CompanyGUID equals c.GUID
            join cl in db.tCompanyLocations on s.LocationGUID equals cl.GUID
            join st in db.tSearchTypes on s.SearchTypeGUID equals st.GUID
            where s.DateClosed == null                        
            select new
            {
                Id = s.GUID,
                Type = st.GUID,
                Location = cl.LocationName,
                Company = (s.Confidential) ? String.Empty : c.CompanyName,
                DateOpened = s.DateOpened,
                Notes = s.PlacementNotes,
                Closed = s.DateClosed != null
            };

Some more filtering is done on this info before I ultimately try to do this:

        return query.Select(x => new VacancySummary
        {
            Id = x.Id,
            Departments = "",
            Location = x.Location,
            Company = x.Company,
            DateOpened = x.DateOpened,
            Notes = x.Notes,
            Closed = x.Closed
        }).Distinct().Skip(skip).Take(take);

And get the exception above. I'd rather not have to execute the whole SQL statement first and return the results before then running the Distinct as there's a performance hit with the server bandwidth if each user retrieved all the records for each of the searches which is why only a section of data is returned.

Any ideas?

Answer 1

Which columns are the text type?

One idea may be to not write the Distinct() such that it does it in SQL. You could query out the data into a concrete set of objects, and then use LINQ to Objects to attempt the Distinct().

Starting with what you have, turn that original query into a list of Vacancy Summaries:

var query = (from s in db.tSearches
            join c in db.tCompanies on s.CompanyGUID equals c.GUID
            join cl in db.tCompanyLocations on s.LocationGUID equals cl.GUID
            join st in db.tSearchTypes on s.SearchTypeGUID equals st.GUID
            where s.DateClosed == null                        
            select new VacancySummary()
            {
                Id = s.GUID,
                Departments = string.Empty,
                Type = st.GUID,
                Location = cl.LocationName,
                Company = (s.Confidential) ? String.Empty : c.CompanyName,
                DateOpened = s.DateOpened,
                Notes = s.PlacementNotes,
                Closed = s.DateClosed != null
            }).ToList();

Your query object will now be a List<VacancySummary> instead of an IQueryable<anonymous type>. You may need to override Equals() on your VacancySummary class in order for the distinct to work, but your return simply becomes:

return query.Distinct().Skip(skip).Take(take);

UPDATE: Looks like you will have to approach this as you did in your other question about the same issue.

UPDATE 2: Ever heard of Model Defined Functions in the Entity Framework? Check out this article. You can create conceptual functions (e.g. one that knows how to convert your text column to a varchar(max) (i.e. CONVERT(varchar(max), PlacementNotes))). You can then create some code to use in your LINQ query that will get included in the generated SQL. No idea if it will work for you but maybe worth a shot!

Answer 2

Not a linq user, but in tsql we would just cast it to varchar which is comparable. Try using ToString() or Expression.Convert and, if necessary, truncating the field if it's large, like over 8000 characters.