Question

I have a design where we store answers to scripts s questions by given users. One script can have many questions and each question can be answered multiple times by a given user.

管理系统表(删除额外细节)大致如下:

-Scripts
ScriptId int (PK, identity)

-ScriptQuestions
ScriptId int    (PK)
QuestionId int  (PK)

-Questions
QuestionId int (PK, identity)
QuestionText varchar

-Answers
AnswerId int (PK, identity)
QuestionId int
UserId  int
AnswerText varchar

I would like to query this database for a given script and a given user and obtain all questions and the last answer provided for each question (if any). In T-SQL I would do something like this:

SELECT 
    sq.QuestionId,
    q.QuestionText,
    la.AnswerText   
FROM    
    ScriptQuestions sq
        ON s.ScriptId = sq.ScriptId
    INNER JOIN Questions q
        ON sq.QuestionId = q.QuestionId
    LEFT OUTER JOIN (
            SELECT
                QuestionId,
                AnswerText
            FROM Answers
            WHERE AnswerId IN (
                        SELECT 
                            MAX(AnswerId) LastAnswerId
                        FROM Answers
                        WHERE UserId = @userId
                        GROUP BY QuestionId
                    )
            ) la
        ON q.QuestionId = la.QuestionId
WHERE
    sq.ScriptId = @scriptId

(未经过测试,但我认为它接近我将做的事情)

我正在使用Linq ToEF进行3号MVC申请,并利用这些结果:

        var questions = from sq in script.ScriptQuestions
                        select new QuestionsAnswers
                                   {
                                       QuestionId = sq.QuestionId,
                                       QuestionText = sq.Question.QuestionText,
                                       LastAnswer = sq.Question.Answers
                                           .Where(a => a.UserId == userId)
                                           .OrderByDescending(a => a.AnswerId)
                                           .Select(a => a.AnswerText)
                                           .FirstOrDefault()
                                   };

当我从VS 2010接任英特尔利特人简介会时,我确实取得了同样的结果。我可以看到,林克将这段话翻译成发送了在文字上的埃VERY问题说明,然后是ANOTHER SlectT。因此,如果文字有20个问题,就会问数据库至少40次,而不是像上文那样只发送一份内容说明。

我试图改变我的发言方式,但我未能克服<条码>SlectT发言问题。

这不能是正确的方式,也不能是?

Answer 1

我的猜测是,由于你的问询开始采用<代码>的文字,你问问问问题<<>。看来不是<代码>。 IQueryable。因此,收集工作在记忆中进行,每进入时,请查阅sq. 问题和sq 问题。答案导航特性。如果能够每当你获得这些财产时,就会向亚洲开发银行发出新的询问,以了解这些财产。页: 1 回答收集工作在收集全部资料时进行。



You can try to change it the following way:

    var questions = from sq in context.ScriptQuestions
                    where sq.ScriptId == script.ScriptId
                    select new QuestionsAnswers
                               {
                                   QuestionId = sq.QuestionId,
                                   QuestionText = sq.Question.QuestionText,
                                   LastAnswer = sq.Question.Answers
                                       .Where(a => a.UserId == userId)
                                       .OrderByDescending(a => a.AnswerId)
                                       .Select(a => a.AnswerText)
                                       .FirstOrDefault()
                               };

这应当只是一个单一的数据库查询,因为现在该数据库向有<代码>的实体提供LINQ。 IQueryable。

友情链接