Can I have a select like this with a LINQ to Entitites query?

Question

I am trying the following:

     var questions = _questionsRepository
            .GetAll()
            .Where(q => q.Problem.SubTopicId == subTopicId || subTopicId == 0)
            .Where(q => q.QuestionStatusId == questionStatusId || questionStatusId == 0)
            .Where(q => q.AssignedTo == assignedTo || assignedTo == "0")
            .Where(q => q.ModifiedBy == modifiedBy || modifiedBy == "0")
            .Include(q => q.Problem)
            .Include(q => q.Answers)
            .Select(x=>new Question {
                QuestionId = x.QuestionId,
                QuestionUId = x.QuestionUId,
                Answers = x.Answers,
                Problem = new Problem {
                    ProblemId = x.Problem.ProblemId
                }
            }).ToList();

But getting an error message saying:

System.NotSupportedException was unhandled by user code
  HResult=-2146233067
  Message=The entity or complex type 'Models.Contexts.Question' cannot be constructed in a LINQ to Entities query.
  Source=EntityFramework
  StackTrace:

score 1 · Accepted Answer · answered Jan 07 '14 at 07:47

Your class Question doesn't seem to be part of the repository/context, so EF cannot construct an object of this class in SQL. You can however take all the necessary data in SQL, and construct the object in .net once you get all the data there. To indicate this switch-to-.net you can use the AsEnumerable() method.

var questions = _questionsRepository
    .GetAll()
    .Where(q => q.Problem.SubTopicId == subTopicId || subTopicId == 0)
    .Where(q => q.QuestionStatusId == questionStatusId || questionStatusId == 0)
    .Where(q => q.AssignedTo == assignedTo || assignedTo == "0")
    .Where(q => q.ModifiedBy == modifiedBy || modifiedBy == "0")
    .Include(q => q.Problem)
    .Include(q => q.Answers)
    .Select(x => new {
        QuestionId = x.QuestionId,
        QuestionUId = x.QuestionUId,
        Answers = x.Answers,
        ProblemId = x.Problem.ProblemId
    })
    .AsEnumerable()
    .Select(x => new Question {
        QuestionId = x.QuestionId,
        QuestionUId = x.QuestionUId,
        Answers = x.Answers,
        Problem = x.ProblemId
    })
    .ToList();

score 1 · Answer 2 · edited May 23 '17 at 11:49

Duplicate with The entity The entity cannot be constructed in a LINQ to Entities query. Check this post to understand that you cannot instanciate an entity directly in your select close. You have to create a DTO class to work with.

The solution would also be :

var questions = _questionsRepository
        .GetAll()
        .Include("Problem")
        .Include("Answers")
        .Where(q => (q.Problem.SubTopicId == subTopicId || subTopicId == 0) 
               && (q.QuestionStatusId == questionStatusId || questionStatusId == 0)
               && (q.AssignedTo == assignedTo || assignedTo == "0")
               && (q.ModifiedBy == modifiedBy || modifiedBy == "0")).ToList();

You should obtain what you tried to.

Can I have a select like this with a LINQ to Entitites query?

2 Answers2