1

I'm trying to retrieve this from a LINQ-query:

 Question 1 - Answer 1
            - Answer 2 (Selected)
            - Answer 3

 Question 2 - Answer 1
            - Answer 2
            - Answer 3 (Selected)
 etc..

My tables look like this:

Question (with attached multilang support which I'll leave out for now)
QuestionAnswer
Answer (also with multilang)
Response (where the user's response is kept (aka which answer he took -> a specif QuestionAnswer row)
Questionnaire (where all the questionanswers for a single questionnaire are kept)

I've tried the following, but I get an exception saying that .ToList() can't be translated into a stored proc when I run it (so at Excecution time, not at compile time) (note this is translated):

(from culture in DbContext.Culture    
from questionanswer in DbContext.QuestionAnswer
join questionnaire in DbContext.Questionnaire on questionanswer .QuestionnaireID equals questionnaire.QuestionnaireID 

where culture.TwoLetterISO.Equals(cultureCode) &&
    questionnaire.QuestionnaireID == id

select new QuestionnaireSectionInformation()
{   
    // Additional data is retrieved here, but thats not important for this question
     Questions = 
        ((from question in DbContext.Question
           join qmultilang in DbContext.QuestionMultiLang on question.ID equals qMultiLang.Id
           join response in DbContext.Response on questionanswer.ID equals response.questionanswerId into possibleReponse

           where question.ID == questionanswer.QuestionID &&
               qMultiLang.CultureId == culture.ID
           select new Topic()
           {
               Question = qMultiLang.Vraag,
               Opmerking = possibleResponse.Any() ? possibleResponse.FirstOrDefault().Commentaar : null,
               Answers= 
                ((from answer in DbContext.Answer
                  join aMultiLang in DbContext.AnswerMultiLang on answer.ID equals aMultiLang.Id
                  where aMultiLang.CultureId == culture.ID
                  select new Answer()
                  {
                     Answer= aMultiLang.Answer,
                     Selected = possibleAnswer.Any()
                  }).ToList())
           }).ToList())
}).ToList();

I'm trying to learn some more LINQ, that's why I'm not pulling this apart (by just retrieving the data and extracting the questions and answers out of it).

so the problem is: I get an exception saying that .ToList() can't be translated into a stored proc when I run it. How can I get all the child elements of the questions if I can't call .ToList() on them?

Team-JoKi
  • 1,766
  • 3
  • 15
  • 23
  • *I hope the question is clear* - I don't have a clue what your question is (maybe it's just me). Also, the db schema you posted is not a schema, it's just a list of table names. – vgru Nov 15 '12 at 11:37
  • What's the reason for the several `ToList()`'s that you are doing? – Arran Nov 15 '12 at 11:40
  • http://stackoverflow.com/questions/12515575/is-there-a-neat-way-of-doing-a-tolist-within-a-linq-query-using-query-syntax – m4ngl3r Nov 15 '12 at 11:41
  • Don't call ToList on the inner query . Note that you can call ToList on the end of the query, as that part doesn't need to be translated to T-SQL. – m4ngl3r Nov 15 '12 at 11:42
  • Entity framework - code first – Team-JoKi Nov 15 '12 at 11:54
  • @m4ngl3r how can I get all the child elements of the questions if I can't use ToList? – Team-JoKi Nov 15 '12 at 11:58
  • well, you might be using AsQueryable() instead and only at last step using ToList() – m4ngl3r Nov 15 '12 at 12:28
  • @m4ngl3r hey, you're right :) I used AsEnumerable() instead of ToList..could you explain what the difference is? – Team-JoKi Nov 15 '12 at 12:50

1 Answers1

2

Don't call ToList on the inner query . Note that you can call ToList on the end of the query, as that part doesn't need to be translated to T-SQL.

you might be using AsQueryable() instead and only at last step using ToList()

so:

AsQueryable just creates a query, the instructions needed to get a list. You can make futher changes to the query later such as adding new Where clauses that get sent all the way down to the database level.

AsList returns an actual list with all the items in memory. If you add a new Where cluse to it, you don't get the fast filtering the database provides. Instead you get all the information in the list and then filter out what you don't need in the application, it will be already presented as final entity, so no further modification will be allowed

m4ngl3r
  • 552
  • 2
  • 17