linq sql error : the text data type cannot be selected as distinct because it is not comparable.

Question

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 :

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

Where as this works fine:

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

Answer 1

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.

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

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.

Obviously that's pretty inefficient, but it's better than the potentially O(n^3) loop you've got at the moment :)

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.