如何使用linq to sql从三个不同的表中获取值? [英] How do you get values from three different tables using linq to sql?

问题描述

这很简单:

我有三个表:
问题:
ID(PK)
机身

I have three tables:
Questions:
ID (PK)
Body

问题和答案:
QuesionID
AnswerID

QuestionsAndAnswers:
QuesionID
AnswerID

答案:
ID
身体
正确

Answers:
ID
Body
IsCorrect

每个都有一个对应的类:

Each has a corresponding Class:

[Table]
public class Questions  
{  
  [Column]public int ID;  
  [Column]public string Body;  
}   

[Table]      
public class QuestionsAndAnswers   
{
  [Column]public int QuestionID;
  [Column]public int AnswerID;  
}  

[Table]
public class Answers
{
  [Column]public int AnswerID;  
  [Column]public string Body;
  [Column]public bool IsCorrect;
}  

我需要回答所有问题.

I need to get a question with all it's answers.

我知道如何获得所有问题:

I know how to get all the questions:

private Table<Questions> questionsTable;  
public SQLQuestionsRepository (string connString)  
{  
  questionsTable=(new DataContext(connString)).GetTable<Questions>();
}  

但是如何将特定的答案与其答案相关联?
我可以在一个命令中执行此操作，还是应该实际使用linq查询?如果是这样，怎么办?

But how do I associate a specific one with it's answers?
Can I do it in one command or should I actually use linq queries? and if so, how?

谢谢.

推荐答案

您可能想要进行联接(请注意，实际上可以使用联接LINQ方法，但在这里我不使用它):

You may want to do a join (note that there is actually a join LINQ method you could use, but I'm not using it here):

using (var dataContext = new DataContext(connString)) {
  questionsTable=
             from q in dataContext.GetTable<Questions>()
             from a in dataContext.GetTable<Answers>()
             from qa in dataContext.GetTable<QuestionsAndAnswers>()
             where qa.QuestionID == q.QuestionID && qa.AnswerID == a.AnswerID
             select new { Question = q, Answer = a};
}

另一种选择是添加关联(导航)属性，以便您可以直接从问题"对象获得所需的信息:

Another option would be to add association (navigation) properties so that you can get to the info you want right off of your Questions object:

[Association(Storage="QuestionsAndAnswers", OtherKey="QuestionID")]
public QuestionsAndAnswers QuestionsAndAnswers ...

然后，您只需获取问题并做

Then you can simply fetch Questions and do

myQuestion.QuestionsAndAnswers[0].Answer.IsCorrect

这篇关于如何使用linq to sql从三个不同的表中获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！