sql server - 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