How do you get values from three different tables using linq to sql?

Question

This is quite straightforward:

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

QuestionsAndAnswers:
QuesionID
AnswerID

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>();
}

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?

Thank you.

Jeff · Accepted Answer · 2010-10-01 22:41:14Z

3

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

answered Oct 1, 2010 at 22:41

Jeff

36.7k16 gold badges117 silver badges236 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oren A Over a year ago

Should I use EntitySet<..> / EntityRef<..> ?

Jeff Over a year ago

If you're using the LINQ to SQL designer, it should add the attributes to the model classes automatically. Usually the private field it creates for -> 1 relationships is of type EntityRef<T>, but the public property is of type T (the entity). -> Many relationships are usually created as EntitySet<T>. But again, if you're using the designer, it should handle all of this for you...

Collectives™ on Stack Overflow

How do you get values from three different tables using linq to sql?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related