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

Question

I get this error because I have a SQL Server table with a column of type "text".

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

Any way to resolve this without changing the data type?

Here is my linq statement (it's long):

                var query = (from s in db.tblSuppliers                              join p in                                  (                                     from p1 in db.tblSupplierPricingSchemes                                     select new                                     {                                         p1.SupplierID,                                         p1.PSLangPairID,                                         p1.CustomerID,                                         p1.PSLanguageStatus,                                         p1.PSPriceBasis,                                         p1.PSMinFlatCharge,                                         p1.PSTrxPrf,                                         p1.PSNoMatch,                                         p1.PSFuzzy,                                         p1.PS100Match_Rep,                                         p1.PSTrxOnly,                                         p1.PSPrfOnly,                                         p1.PSLinquisticHourlyRate,                                         p1.PSDTPType,                                         p1.PSDTPRate,                                         p1.PS_FZ50,                                         p1.PS_FZ75,                                         p1.PS_FZ85,                                         p1.PS_FZ95,                                         p1.PS_FZ100,                                         p1.PS_FZREPS,                                         p1.PSPerfectMatch                                     }                                  ) on s.SupplierID equals p.SupplierID                              join p2 in                                  (                                         from p in db.tblSupplierPricingSchemes                                         where custID.Contains(p.CustomerID) && p.PSLangPairID == languagePairID                                         group p by new { p.SupplierID, p.PSLangPairID, p.PSPriceBasis } into g                                         let CustomerID = g.Max(uh => uh.CustomerID)                                         select new                                         {                                             g.Key.SupplierID,                                             g.Key.PSLangPairID,                                             g.Key.PSPriceBasis,                                             CustomerID                                         }                                  ) on p.SupplierID equals p2.SupplierID                              join b in db.tblPricingBasis on p.PSPriceBasis equals b.PricingBasisID                              join ss in db.tblSupplierStatus on p.PSLanguageStatus equals ss.SupplierStatusID into g1                              from ss in g1.DefaultIfEmpty()                              join l in db.tblLangPairs on p.PSLangPairID equals l.ProductID                              where l.ProductID == languagePairID                                 && p.PSLangPairID == p2.PSLangPairID                                 && p.CustomerID == p2.CustomerID                                 && p.PSPriceBasis == p2.PSPriceBasis                              select new PreferredSupplier                              {                                  SupplierID = s.SupplierID,                                  //SupplierName = s.CompanyName != null ? s.CompanyName + "-" + s.SupplierFirstName + " " + s.SupplierLastName                                  //                                     : s.SupplierFirstName + " " + s.SupplierLastName,                                   SupplierName = s.CompanyName != null                                             ? s.SupplierFirstName != null || s.SupplierLastName != null                                                     ? s.CompanyName + "-" + s.SupplierFirstName + " " + s.SupplierLastName                                                     : s.CompanyName                                             : s.SupplierFirstName + " " + s.SupplierLastName,                                   CompanyName = s.CompanyName,                                  SupplierFirstName = s.SupplierFirstName,                                  SupplierLastName = s.SupplierLastName,                                   SupplierStatus = p.CustomerID == customerID ? "Team Member" : ss.SupplierStatus,                                  Email = (string)s.SupplierEmails,                                   Rate = (s.VolumeDiscountType == 1 ? // Percentage                                      //if the volume discount is as percentage then get the rate and multiple it by 1 - the discount percentage                                         ((words > s.VolumeDiscountAmount && (task == "TM No Match" || task == "Translation/Proofreading")) ? 1 - s.VolumeDiscountValue : 1) *                                                     (                                                         rateField == "PSTrxPrf" ? p.PSTrxPrf :                                                         rateField == "PSNoMatch" ? p.PSNoMatch :                                                         rateField == "PSFuzzy" ? p.PSFuzzy :                                                         rateField == "PS100Match_Rep" ? p.PS100Match_Rep :                                                         rateField == "PSLinquisticHourlyRate" ? p.PSLinquisticHourlyRate :                                                         rateField == "PSDTPRate" ? p.PSDTPRate :                                                         rateField == "PS_FZ50" ? p.PS_FZ50 :                                                         rateField == "PS_FZ75" ? p.PS_FZ75 :                                                         rateField == "PS_FZ85" ? p.PS_FZ85 :                                                         rateField == "PS_FZ95" ? p.PS_FZ95 :                                                         rateField == "PS_FZ100" ? p.PS_FZ100 :                                                         rateField == "PS_FZREPS" ? p.PS_FZREPS :                                                         rateField == "PSPerfectMatch" ? p.PSPerfectMatch : null                                                     ) :                                      // Discount in Amount                                      // Take the Rate and substract the amount to discount                                                    (                                                         rateField == "PSTrxPrf" ? p.PSTrxPrf :                                                         rateField == "PSNoMatch" ? p.PSNoMatch :                                                         rateField == "PSFuzzy" ? p.PSFuzzy :                                                         rateField == "PS100Match_Rep" ? p.PS100Match_Rep :                                                         rateField == "PSLinquisticHourlyRate" ? p.PSLinquisticHourlyRate :                                                         rateField == "PSDTPRate" ? p.PSDTPRate :                                                         rateField == "PS_FZ50" ? p.PS_FZ50 :                                                         rateField == "PS_FZ75" ? p.PS_FZ75 :                                                         rateField == "PS_FZ85" ? p.PS_FZ85 :                                                         rateField == "PS_FZ95" ? p.PS_FZ95 :                                                         rateField == "PS_FZ100" ? p.PS_FZ100 :                                                         rateField == "PS_FZREPS" ? p.PS_FZREPS :                                                         rateField == "PSPerfectMatch" ? p.PSPerfectMatch : null                                                      ) - (s.VolumeDiscountValue == null ? 0 : s.VolumeDiscountValue)),                                  //PSMinFlatCharge = p.PSMinFlatCharge,                                 MinimumFee = p.PSMinFlatCharge,                                   //Basis = b.PricingBasisDesc,                                  Basis = task == "DTP" || task == "DTP Edit" ? p.PSDTPType : b.PricingBasisDesc,                                   StatusOrder = p.CustomerID == customerID ? 0 : p.PSLanguageStatus == null ? 1000 : p.PSLanguageStatus                              }).Distinct(); 

Answer 1

Simple answer is "don't use text".

It was deprecated for varchar(max) years ago when SQL Server 2005 was released.

The code you have is issuing SELECT DISTINCT.
You need to fix the model/tables so it isn't text datatype