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
相关文章