Linq - Retrieve value from a linked linked table

Question

Hi,

I have the following model and relationship:

Table: Granddad
GranddadID
GranddadName

Table: Father
FatherID
GranddadID
FatherName

Table: Son
SonID
FatherID
SonName

in the Granddad controller:

public ActionResult Edit(int tmpgranddadid)
{
   var q = (from g in _e.Grandad
           where g.GrandadID == tmpgranddadid
           select g).FirstOrDefault();

   string son_name = q.Father.Son.SonName.ToString(); // <- is wrong, how to do this?

   return View(q);
}

How do you retrieve the value from a linked linked table?

Thank you

Answer 1

Check out joins:

http://www.hookedonlinq.com/JoinOperator.ashx

Answer 2

Perhaps you want any of the possibly many sons?

q.Fathers.First().Sons.First().SonName.ToString();

Be careful though because First() can throw an exception if a father has no sons. This will handle that case:

string sonName = null;
Father father = q.Fathers.FirstOrDefault();
if (father != null) {
    Son son = father.Sons.FirstOrDefault();
    if (son != null) {
        sonName = son.SonName.ToString();
    }
}

Answer 3

Assuming your data source looks something like this:

public class SomeDataSource
{
    public List<Granddad> Granddad;
    public List<Father> Father;
    public List<Son> Son;
}

And assuming you're actually after the first grandson for a granddad then:

var firstGrandson = (from son in _e.Son
               join father in _e.Father on son.FatherID equals father.FatherID
               join granddad in _e.Granddad on father.GranddadID equals granddad.GranddadID
               where granddad.GranddadID == tmpgranddadid
               select son).FirstOrDefault();

if (firstGrandson == null)
    throw new Exception("This granddad has no grandsons.");

string son_name = firstGrandson.SonName;

Is this what you needed?