I have the below query. For each row in the outer query, there are multiple choices in the inner query. I get the results from the outer query, but the Choices collection is always empty. I can run the SQL and get the correct results. So it seems the SQL generation is correct, but after retrieving the results, the mapping that populates the Choices collections for each item is failing to match items. If I simplify the query eliminating the AssessmentChoiceVersion, and only retrieving Choices, then it works fine. The ChoiceVersion is unique here because I only want the one that matches on both QuestionVersionId and QuestionChoiceId, so it is a composite join. LLBLGen did not like when I had the multi key join in the nested subquery, so the criteria is in the where.
Since the SQL is correct and returns the correct results, hopefully you can give me some insight into how LLBLGen then maps these results to objects. In this case it should be matching qv.Id and q.Id in the outer query with cv.QuestionVersionId and c.QuestionId respectively in the inner query.
I made sure that both c.QuestionID and cv.QuestionVersionId were both explicitly included in the result of the inner query:
(
from qpqv in db.AssessmentQuestionPoolQuestionVersion// p.AssessmentQuestionPoolQuestionVersions
from proctQ in qpqv.AssessmentProctoredVersionQuestions
join qv in db.AssessmentQuestionVersion on qpqv.QuestionVersionId equals qv.Id
join q in db.AssessmentQuestion on qv.QuestionId equals q.Id
where proctQ.ProctoredVersionId == proctoredVersionId
select new {
QuestionId= q.Id,
qpqvid= qpqv.Id,
QuestionVersionId= qv.Id,
Choices = (
from c in db.AssessmentChoice
join cv in db.AssessmentChoiceVersion on c.Id equals cv.ChoiceId
where q.Id == c.QuestionId &&
cv.QuestionVersionId == qv.Id
select new{ c.Id, cvId= cv.Id, cv.QuestionVersionId, c.QuestionId}
).ToList()
})
Results in SQL:
SELECT [LPA_L4].[Id] AS [QuestionId], [LPA_L1].[Id] AS [qpqvid], [LPA_L3].[Id] AS [QuestionVersionId], 1 AS [LPFA_18], [LPA_L4].[Id]
FROM ((( [Nucleus].[Assessment].[QuestionPoolQuestionVersion] [LPA_L1]
INNER JOIN [Nucleus].[Assessment].[ProctoredVersionQuestion] [LPA_L2] ON [LPA_L1].[Id]=[LPA_L2].[QuestionPoolQuestionVersionId])
INNER JOIN [Nucleus].[Assessment].[QuestionVersion] [LPA_L3] ON [LPA_L1].[QuestionVersionId] = [LPA_L3].[Id])
INNER JOIN [Nucleus].[Assessment].[Question] [LPA_L4] ON [LPA_L3].[QuestionId] = [LPA_L4].[Id])
WHERE ( ( ( ( ( [LPA_L2].[ProctoredVersionId] = @p1)))))
SELECT [LPA_L1].[Id], [LPA_L2].[Id] AS [cvId], [LPA_L2].[QuestionVersionId], [LPA_L1].[QuestionId]
FROM ( [Nucleus].[Assessment].[Choice] [LPA_L1]
INNER JOIN [Nucleus].[Assessment].[ChoiceVersion] [LPA_L2] ON [LPA_L1].[Id] = [LPA_L2].[ChoiceId])
WHERE ( ( ( EXISTS
(SELECT [LPA_L7].[Id]
FROM ((( [Nucleus].[Assessment].[QuestionPoolQuestionVersion] [LPA_L4]
INNER JOIN [Nucleus].[Assessment].[ProctoredVersionQuestion] [LPA_L5] ON [LPA_L4].[Id]=[LPA_L5].[QuestionPoolQuestionVersionId])
INNER JOIN [Nucleus].[Assessment].[QuestionVersion] [LPA_L6] ON [LPA_L4].[QuestionVersionId] = [LPA_L6].[Id])
INNER JOIN [Nucleus].[Assessment].[Question] [LPA_L7] ON [LPA_L6].[QuestionId] = [LPA_L7].[Id])
WHERE ( ( ( ( ( [LPA_L5].[ProctoredVersionId] = @p1)))) AND [LPA_L7].[Id] = [LPA_L1].[QuestionId] AND [LPA_L2].[QuestionVersionId] = [LPA_L6].[Id])))))
;
For example, both queries each contain a record that matches on the QuestionVersionId & QuestionId:
First query:
Id cvId QuestionVersionId QuestionId
D428FFE3-946A-47C5-ADB5-119588B5E8FC 6D3EB5F9-11C5-4495-994E-1BDD00725CF0 10781EDE-F6EE-464D-ACE9-DCC1B789ABE9 19182871-C798-47E8-8268-5ADF7BB95835
Second query, just one of the matching results, but there are multiple for that QuestionId/QuestionVersionId:
QuestionId qpqvid QuestionVersionId LPFA_18 Id
19182871-C798-47E8-8268-5ADF7BB95835 FA8808C6-5513-42B5-AD7C-0A9CBB7E755D 10781EDE-F6EE-464D-ACE9-DCC1B789ABE9 1 19182871-C798-47E8-8268-5ADF7BB95835
Sorry, couldn't figure out how to get a PRE tag in there so the tabbed data formats better.
So in my results in C#, I see the item for the first query, but it's .Choices is empty, as it didn't find the matching record in the second query for some reason even though it is in the SQL results.
If I simplify the query, eliminating the ChoiceVersion, then the .Choices gets populated:
(
from qpqv in db.AssessmentQuestionPoolQuestionVersion
from proctQ in qpqv.AssessmentProctoredVersionQuestions
join qv in db.AssessmentQuestionVersion on qpqv.QuestionVersionId equals qv.Id
join q in db.AssessmentQuestion on qv.QuestionId equals q.Id
where proctQ.ProctoredVersionId == proctoredVersionId
select new {
QuestionId= q.Id,
QuestionVersionId= qv.Id,
qpqvid= qpqv.Id,
Choices = (
from c in db.AssessmentChoice
where q.Id == c.QuestionId
select new{c.QuestionId, c.Id}
).ToList()
})
