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

Question

我收到此错误是因为我有一个包含“文本”类型列的 SQL Server 表。

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

有什么方法可以在不改变数据类型的情况下解决这个问题吗？

这是我的 linq 语句(很长):

                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

简单的答案是“不要使用文本”。

它已被弃用 varchar(max)几年前，当 SQL Server 2005 发布时。

您拥有的代码正在发出 SELECT DISTINCT。
您需要修复模型/表格，使其不是 text 数据类型