linq sql错误:无法将文本数据类型选择为不同的,因为它不可比较 [英] linq sql error : the text data type cannot be selected as distinct because it is not comparable
问题描述
我想从问题表中选择所有尚未包含在特定测验中的问题. 我的问题是,为什么以下代码失败并显示以下消息:
I want to select all questions from the questions table that haven't been included already for a particular quiz. My question is why does the below code fail with the message :
文本数据类型不能选择为DISTINCT,因为它不可比较. 文本类型和文本类型在is运算符中不兼容.
The text data type cannot be selected as DISTINCT because it is not comparable. The data types text and text are incompatible in the is operator.
var allQuestions = from q in my.Questions
select new
{
Select = new Boolean(),
Id = q.QuestionId,
QuestionName = q.Name,
QuestionText = q.Text,
Topic = q.Topic.Title
};
var currentQuestions = from cq in my.QuizQuestions
where cq.Quiz.quizId == quizId
select new
{
Select = new Boolean(),
Id = cq.Questions.QuestionId,
QuestionName = cq.Questions.Name,
QuestionText = cq.Questions.Text,
Topic = cq.Questions.Topic.Title
};
var selectQuestions = allQuestions.Except(currentQuestions);
可以正常使用的地方:
var allQuestions = (from q in my.Questions
select new
{
Select = new Boolean(),
Id = q.QuestionId,
QuestionName = q.Name,
QuestionText = q.Text,
Topic = q.Topic.Title
}).ToList();
var currentQuestions = (from cq in my.QuizQuestions
where cq.Quiz.quizId == quizId
select new
{
Select = new Boolean(),
Id = cq.Questions.QuestionId,
QuestionName = cq.Questions.Name,
QuestionText = cq.Questions.Text,
Topic = cq.Questions.Topic.Title
}).ToList();
int allquestionsCount = allQuestions.Count();
for (int i = allquestionsCount; i < 0; i--)
{
foreach(var question in currentQuestions){
if (question.Id.Equals(allQuestions.ElementAt(i - 1).Id))
{
allQuestions.RemoveAt(i - 1);
}
}
}
推荐答案
这实际上是一个SQL问题,而不是LINQ问题,除了Except
LINQ调用显然最终转换为DISTINCT
子句这一事实之外在SQL中.
This is really a SQL question rather than a LINQ question, other than the fact that an Except
LINQ call apparently ends up translated as a DISTINCT
clause in SQL.
您可以使LINQ to Objects为您完成"except"部分:
You could get LINQ to Objects to do the "except" part for you:
var allQuestions = // code as before in first example
var currentQuestions = // code as before in first example
var selectQuestions = allQuestions.AsEnumerable()
.Except(currentQuestions);
对AsEnumerable
的调用只是强制它使用LINQ to Objects进行进程中的后续操作,而不是将其全部转换为SQL查询.
The call to AsEnumerable
just forces it to do the subsequent operations in-process with LINQ to Objects instead of converting it all into a SQL query.
显然这效率不高,但是比您目前拥有的可能的O(n ^ 3)循环要好:)
Obviously that's pretty inefficient, but it's better than the potentially O(n^3) loop you've got at the moment :)
搜索错误消息(第一部分)在SQL方面给出了一些潜在的有用结果.我建议您记录正在生成的SQL,然后对其进行检查并阅读这些命中信息-他们可能会建议一些更改架构的方法,以允许在数据库中完成所有操作.
A search for the error message (first part) gives some potentially helpful results on the SQL side though. I suggest you log the SQL being generated, then examine it and read those hits - they may suggest ways of altering your schema to allow it all to be done in the database.
这篇关于linq sql错误:无法将文本数据类型选择为不同的,因为它不可比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!