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.
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>;
}
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.
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();
}
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).
neoxgeforce
Member
5 Points
8 Posts
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()
How to resolve this problem ?
jsiahaan
Contributor
2304 Points
588 Posts
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
Indonesian Humanitarian Foundation
francesco ab...
All-Star
20910 Points
3278 Posts
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.
Mvc Controls Toolkit | Data Moving Plug-in Videos
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(); }Blog | Twitter : @Hattan
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.
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?
Blog | Twitter : @Hattan
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 }ignatandrei
All-Star
134913 Points
21619 Posts
Moderator
MVP
Re: Nested LINQ and IList
May 01, 2012 04:21 AM|LINK
do not put IList, put List<>
http://haacked.com/archive/2008/10/23/model-binding-to-a-list.aspx