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; }
}
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()
;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With