Nested LINQ and IList : The Official Microsoft ASP.NET Forums

Reply

Nested LINQ and IList

Apr 30, 2012 01:12 PM|LINK

I have some classes in DbContext

public class Question
    {
        public int Id { get; set; }
        public string Text { get; set; }
        public float Weight { get; set; }
        public QuestionLevel Level { get; set; }
        public bool IsMultiSelected { get; set; }

        public ICollection<Answer> Answers { get; set; }
        public ICollection<QuestionGroup> QuestionGroups { get; set; }
    }
    public class Answer
    {
        public int Id { get; set; }
        public string Text { get; set; }
        public float Weight { get; set; }
        public Question Question { get; set; }
    }
    public class QuestionGroup
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<Question> Questions { get; set; }
    }
    public class QuestionLevel
    {
        public int Id { get; set; }
        public int Level { get; set; }
    }

And ViewModels

   public class QuestionViewModel
    {
        public string Text { get; set; }
        public int Id { get; set; }
        public bool IsMultiSelected { get; set; }
        public IList<AnswerViewModel> AnswerViewModels { get; set; }
    }
    public class AnswerViewModel
    {
        public string Text { get; set; }
        public int Id { get; set; }
        public bool Checked { get; set; }
    }

I have method for getting data from Db

 public List<QuestionViewModel> GetQuesitonForLevel(int level)
        {
            var questions = from q in _context.Questions
                            where q.Level.Level == level
                            select new QuestionViewModel
                                       {
                                           Id = q.Id,
                                           IsMultiSelected = q.IsMultiSelected,
                                           Text = q.Text,
                                           AnswerViewModels = (from a in q.Answers
                                                               select new AnswerViewModel
                                                                          {
                                                                              Text = a.Text,
                                                                              Id = a.Id,
                                                                              Checked = false
                                                                          }).ToList()
                                       };
            return questions.ToList();
        }

But i get error in questions.ToList()

LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[ExpertApplication.ViewModels.AnswerViewModel] ToList[AnswerViewModel](System.Collections.Generic.IEnumerable`1[ExpertApplication.ViewModels.AnswerViewModel])' method, and this method cannot be translated into a store expression. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[ExpertApplication.ViewModels.AnswerViewModel] ToList[AnswerViewModel](System.Collections.Generic.IEnumerable`1[ExpertApplication.ViewModels.AnswerViewModel])' method, and this method cannot be translated into a store expression.

How to resolve this problem ?

Reply

Re: Nested LINQ and IList

Apr 30, 2012 01:43 PM|LINK

Hi,

THis code can help you:

public List<QuestionViewModel> GetQuesitonForLevel(int level)
        {
            var questions = from q in _context.Questions
                            where q.Level.Level == level
                            select new QuestionViewModel
                                       {
                                           Id = q.Id,
                                           IsMultiSelected = q.IsMultiSelected,
                                           Text = q.Text,
                                           AnswerViewModels = (from a in q.Answers
                                                               select new AnswerViewModel
                                                                          {
                                                                              Text = a.Text,
                                                                              Id = a.Id,
                                                                              Checked = false
                                                                          }).ToList()
                                       };
            return questions as List<QuestionViewModel>;
        }

Have fun

Jannen Siahaan
Indonesian Humanitarian Foundation

Reply

Re: Nested LINQ and IList

Apr 30, 2012 03:30 PM|LINK

Quite complex to explain...anyway. The point is that you can't use the ToList operaqtor on a nested IQueryable. The reason is simple IQueryable are transformed into db queries and there is no translation into sql for the ToList operation.

That said, if you need a list in the nexted collection, you need first to transform the IQueryable into an IEnumerable by executing the query.

Query immediate execution can be forced with a ToList on the outer collection. This ToList is allowed because it is executed after the query execution, that is it is executed on in memory data. Moreover, it forces the query execution. The inner ToList, instead is simply inpossible to handle...since it is nested into the query and cannot be executed after query execution.

That said ...after the ToList on the outer collection, you get an IEnumerable that you can query again...but this time in memory, so you can apply the ToList to the inner collection.

So in the Select DO NOT USE QuestionViewModel...but a DTO...or whatever can accept:

(from a in q.Answers
select new AnswerViewModel
{
Text = a.Text,
Id = a.Id,
Checked = false
})

into its .AnswersModels properties WITHOUT transforming it into a List. You can use an IEnumerable for its AnswersModels or also a Collection...This two datatypes accept the query as it is without the to list.

Then if you need Lists you query the IEnumerable, say "en" you get with the outer ToList like this:

from x in en Select new QuestionViewModel{.....AnswersModels=x.AnswersModels.ToList().....

and this way you can transform the inner collection into a list...now you can do it because you are operating in memory.

Francesco Abbruzzese http://www.dotnet-programming.com/
Mvc Controls Toolkit | Data Moving Plug-in Videos

Marked as answer by neoxgeforce on Apr 30, 2012 09:06 PM

Reply

CodeHobo

All-Star

18647 Points

2647 Posts

Re: Nested LINQ and IList

Apr 30, 2012 03:54 PM|LINK

In addition to Francesco's explanation (which is spot on), you need to make two changes to your code to get it to work. First you need to change QuestionViewModel to accept an IEnumerable instead of an IList and you need to remove the two list in the nested query (which EF can't translate to SQL).

public class QuestionViewModel
    {
        public string Text { get; set; }
        public int Id { get; set; }
        public bool IsMultiSelected { get; set; }
        public IEnumerable<AnswerViewModel> AnswerViewModels { get; set; }
    }
    public class AnswerViewModel
    {
        public string Text { get; set; }
        public int Id { get; set; }
        public bool Checked { get; set; }
    }

And

 public List<QuestionViewModel> GetQuesitonForLevel(int level)
        {
            var questions = from q in _context.Questions
                            where q.Level.Level == level
                            select new QuestionViewModel
                                       {
                                           Id = q.Id,
                                           IsMultiSelected = q.IsMultiSelected,
                                           Text = q.Text,
                                           AnswerViewModels = (from a in q.Answers
                                                               select new AnswerViewModel
                                                                          {
                                                                              Text = a.Text,
                                                                              Id = a.Id,
                                                                              Checked = false
                                                                          })
                                       };
            return questions.ToList();
        }

Hattan Shobokshi
Blog | Twitter : @Hattan

Marked as answer by neoxgeforce on Apr 30, 2012 09:06 PM

Reply

neoxgeforce

Member

5 Points

8 Posts

Re: Nested LINQ and IList

Apr 30, 2012 05:24 PM|LINK

I need IList<AnswerViewModel> for correct and clarify passing data from form.

Reply

CodeHobo

All-Star

18647 Points

2647 Posts

Re: Nested LINQ and IList

Apr 30, 2012 05:29 PM|LINK

Then you need to evaluate the data before it's sent to the database, however this will have performance implications. I would recommend looking at your view and to see if you can change the references from IList to IEnumerable (which is a base class of IList).

What does your view look like?

Hattan Shobokshi
Blog | Twitter : @Hattan

Reply

neoxgeforce

Member

5 Points

8 Posts

Re: Nested LINQ and IList

Apr 30, 2012 07:50 PM|LINK

I did as you advised. I created form and filled it data (from IList<,,> GetQuestions(...)).

@using ExpertApplication.ViewModels
@model IList<QuestionViewModel>
@{
    ViewBag.Title = "Start Test";
    
}
@if(Model != null)
{
    <h2>Please set answer for quesitons</h2>
    using(Html.BeginForm("ProcessAnswers", "Home", FormMethod.Post))
    {
        foreach(QuestionViewModel questionViewModel in Model)
        {
    <h2>@questionViewModel.Text</h2>
            var answersForQuestion = questionViewModel.AnswerViewModels;
    <div id="answer">
        @foreach(AnswerViewModel answerViewModel in answersForQuestion)
        {
            @(questionViewModel.IsMultiSelected
            ? Html.CheckBox(answerViewModel.Text, answerViewModel.Checked)
                       : Html.RadioButton(questionViewModel.Id.ToString(), answerViewModel.Checked))
            @Html.Label(answerViewModel.Text)
            <br />
        }
    </div>
        }
    <input type="submit" value="Send" />
    }
}
else
{
    <h1>No questions</h1>
}

When I click on submit button in ProcessAnswers I have null reference

        [HttpPost]
        public ActionResult ProcessAnswers(IList<QuestionViewModel> answerForQuesiton)
        {
           //answerForQuestion is null
        }