EF:无法将文本数据类型选为 DISTINCT，因为它没有可比性

EF: The text data type cannot be selected as DISTINCT because it is not comparable(EF:无法将文本数据类型选为 DISTINCT，因为它没有可比性)

问题描述

我收到此错误是因为我有一个包含text"类型列的 SQL Server 表.

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?

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

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();

推荐答案

简单的答案是不要使用文本".

Simple answer is "don't use text".

varchar(max) 几年前，当 SQLServer 2005 发布.

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

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

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

这篇关于EF:无法将文本数据类型选为 DISTINCT，因为它没有可比性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！