LINQ to Entities does not recognize the method

asp.net-mvc-5 c# entity-framework-6 linq

Question

Overview: In CompletedQuestions table, UserId corresponds to the user which completed that question. Id property corresponds to one of the questions in the Questions table. I know, i didn't specify relationships properly. But i'm not very experienced. I just want to finish a project, then i will come back and fix those bad coding practices once i learn more. I couldn't understand the following exception.

LINQ to Entities does not recognize the method 'Riddle.Models.CompletedQuestion LastOrDefault[CompletedQuestion](System.Linq.IQueryable`1[Riddle.Models.CompletedQuestion])' method, and this method cannot be translated into a store expression.

    Line 46:                 if (RiddleCompleted == null)
    Line 47:                 {
    Line 48:                     var lastCompletedQuestion = _db.CompletedQuestions.Where(q => q.UserId == currentUserId) // exception occurs in this line
    Line 49:                                                                              .OrderBy(q => q.QuestionNumber)
    Line 50:                                                                              .LastOrDefault();

The action where the exception occured:

public ActionResult Riddle(int Id)
        {
            string currentUserId = User.Identity.GetUserId();
            var riddle = _db.Riddles.Where(r => r.Id == Id).Single();

            if (riddle.User.Id == User.Identity.GetUserId())
            {
                var question = riddle.Questions.Where(q => q.QuestionNumber == 1).SingleOrDefault();
                if (question == null)
                {
                    return View("NoMoreQuestions");
                }
                return View("RiddleOwner", question);
            }
            else
            {

                var RiddleCompleted = _db.CompletedRiddles.Where(r => r.Id == Id && r.UserId == currentUserId).SingleOrDefault();
                if (RiddleCompleted == null)
                {
                    var lastCompletedQuestion = _db.CompletedQuestions.Where(q => q.UserId == currentUserId)  // exception occurs in this line
                                                                             .OrderBy(q => q.QuestionNumber)
                                                                             .LastOrDefault();
                    if (lastCompletedQuestion == null)
                    {
                        var question = riddle.Questions.Where(q => q.QuestionNumber == 1).Single();
                        return View(question);
                    }

                    else
                    {
                        var question = riddle.Questions.Where(q => q.QuestionNumber == lastCompletedQuestion.QuestionNumber + 1).SingleOrDefault();
                        return View(question);
                    }
                }
                else
                {
                    return View("NoMoreQuestions");
                }
            }
        }

CompletedQuestion model:

public class CompletedQuestion
{
    public int Id { get; set; }
    public string UserId { get; set; }
    public int QuestionNumber { get; set; }
}

Question model:

  public class Question
    {
        public int Id { get; set; }
        public string Body { get; set; }
        public string Answer { get; set; }
        public Riddle Riddle { get; set; }
        [Column(TypeName ="datetime2")]
        public DateTime CreationDate { get; set; }
        public int QuestionNumber { get; set; }
    }
1
1
3/5/2019 12:22:55 AM

Accepted Answer

LastOrDefault() isn't supported by Linq To Entities. So it will work on a collection in memory, but not when you're attempting to query a database .

This is an efficient way to handle it:

var lastCompletedQuestion = 
_db.CompletedQuestions.Where(q => q.UserId == currentUserId)
.OrderByDescending(q => q.QuestionNumber)
.FirstOrDefault() 

                                                                         ;
4
9/25/2016 10:47:38 PM

Popular Answer

Entity framework/linq2sql works by converting your compiled C#/IL into SQL. It can only convert methods it knows. The error is telling you that LastOrDefault isn't one of those.

You can fix this by putting .ToList() before LastOrDefault, which takes it away from the sql-converter into vanilla C# and you get the regular working version of LastOrDefault. Or you can flip the order and use FirstOrDefault, which it can translate.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow