c# - The text data type cannot be selected as DISTINCT because it is not comparable. Not doing distinct operation

Question

In SQL Server table, one of the columne "question" has data type as "text".

Now, when i make query in sql then, use casting to varchar.

Now, in linq following is the query where retrieving specific result data.

The problem is, it gives me below error when i try to iterate result set in FOR OR FOREACH loop. I googled but, it says , that happen when do distinct but, i am not doing distinct operation.

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

from t in db.Table
group t by new 
          {
           t.Category, 
           t.Question
          } into g
order by g.Category
select new 
{
  CategoryName = t.FirstOrDefault().Category, //might be required to handle null here
  Question = t.FirstOrDefault().Question, //might be required to handle null here
  TotalCount = t.Count(),
  AnsLessEqual3 = t.Where(d => d.Answer<=3).Count(),
  Ans5 = t.Where(d => d.Answer = 5).Count(),
  Ans789 = t.Where(d => d.Answer = 7 || d.Answer = 8 || d.Answer =     9).Count()
 }

Please guide how to fix this.

Answer 1

You'll have to get LINQ to convert your text field to a varchar, grabbing the entire Substring should do the trick. Also, you should stick within your group, otherwise you will be summarizing the entire table, assuming LINQ would allow it.

from t in db.Table
group t by new 
          {
           Category = t.Category.Substring(0), 
           t.Question
          } into g
orderby g.Key.Category
select new 
{
    CategoryName = g.Key.Category,
    Question = g.Key.Question,
    TotalCount = g.Count(),
    AnsLessEqual3 = g.Count(d => d.Answer <= 3),
    Ans5 = g.Count(d => d.Answer = 5),
    Ans789 = g.Count(d => d.Answer = 7 || d.Answer = 8 || d.Answer = 9)
 }