LINQ to Entities does not recognize the method: LastOrDefault

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

score 31 · Accepted Answer · answered Sep 25 '16 at 22:47

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

                                                                         ;

score 4 · Answer 2 · answered Sep 25 '16 at 22:43

4

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.

answered Sep 25 '16 at 22:43

Pxtl

880
8
18

just wanted to highlight that even after converting to list and then using LastOrDefault did not work for me and neither did flipping the list and then using FirstOrDefault but it was good to know that LINQ to Entities is not supporting these fns. – Somu Mar 17 '21 at 01:09
This will work, but you need to be careful because .ToList() will load all of the entities from the data store before then choosing the first one from the list in memory. It's better if you want to continue to work with other items in the list, though. – Chad McGrath May 10 '22 at 18:45

LINQ to Entities does not recognize the method: LastOrDefault

2 Answers2

Linked

Related