how to select records back on foreign keys

Question

I am trying to select records records from a table which match the same foreign key as an individual record. I have written the following SQL which works but I was wondering whether there is a more optimal way of doing it:

SELECT title FROM table1 r INNER JOIN dbo.table2 a ON a.Id = r.AssetStructureId 
                              INNER JOIN table3 s ON s.Id = a.table3id 
                              INNER JOIN dbo.table4 f ON f.Id = s.table4id
WHERE s.table4id = (SELECT f.Id FROM dbo.table4 f 
                                     INNER JOIN table3 s ON s.table4id = f.Id 
                                     INNER JOIN dbo.table2 a ON a.table3id = s.Id 
                                     INNER JOIN dbo.table1 r ON r.table2id = a.Id WHERE r.id = 21803)

I have also written the same in entity framework but have written it over two lines, but I was wondering whether there was a better, more optimal way there too?

var data = _context.table1.Where(x => x.Id == id).Select(x => new { x.table2.table3.table4id }).SingleOrDefault();
var titles = _context.table1.Where(x => x.table2.table3.table4Id == data.table4id).Select(x => new { x.Title });

Any help would be greatly appreciated.

Answer 1

What a pity you forgot to give us your classes and your specifications!

After trying to understand your SQL, it seems that table1 has an AssetStructureId, which is the foreign key to the Table2 that Table1 belongs to. Probably a one-to-many relation.

Table2 has a foreign key Table3Id to the Table3 that your Table2 belongs to, also a one-to-many relation

Finally Table3 has a foreign key Table4Id to the Table4 that Table3 belongs to.

My, you do love proper names for your identifiers, don't you?

So:

• Every Table4Element has zero or more Table3Elements;
• Every Table3Element has zero or more Table2Elements;
• Every Table2Element has zero or more Table1Elements.

Following the entity framework code-first conventions you will have classes similar to:

class Table4Element
{
     public int Id {get; set;}

     // every Table4Element has zero or more Table3Elements:
     public virtual ICollection<Table3Element> Table3Elements {get; set;}

     ... // other properties
}
class Table3Element
{
     public int Id {get; set;}

     // every Table3Element has zero or more Table2Elements:
     public virtual ICollection<Table2Element> Table2Elements {get; set;}

     // every Table3Element belongs to exactly one Table4Element using foreign key
     public int Table4ElementId {get; set;}
     public virtual Table4Element Table4Element {get; set;}

     ...
}

In entity framework the columns of your tables are represented by non-virtual properties; the virtual properties represent the relations between the tables.

Table2 and Table1 are similar:

class Table2Element
{
     public int Id {get; set;}

     // every Table2Element has zero or more Table1Elements:
     public virtual ICollection<Table1Element> Table1Elements {get; set;}

     // every Table2Element belongs to exactly one Table3Element using foreign key
     public int Table3ElementId {get; set;}
     public virtual Table3Element Table3Element {get; set;}

     ...
}

class Table1Element
{
     public int Id {get; set;}

     // every Table1Element belongs to exactly one Table2Element using foreign key
     public int Table2ElementId {get; set;}
     public virtual Table2Element Table2Element {get; set;}

     ...
}

And finally your DbContext:

class MyDbContext : DbContext
{
    public DbSet<Table1Element> Table1Elements {get; set;}
    public DbSet<Table2Element> Table2Elements {get; set;}
    public DbSet<Table3Element> Table3Elements {get; set;}
    public DbSet<Table4Element> Table4Elements {get; set;}
}

This is all that entity framework needs to determine the names of tables and columns and to relations between the tables. If for some reason you would like different table names or columns, you'll need to add attributes or use fluent API.

The most important thing in this is that you've got your virtual properties correct.
Whenever you use the virtual properties, entity framework knows that a (group)join is needed and will do them for you

Now that we've defined your four tables, we can go

Back to your question

Oops, you forgot to specify what you want to select!

Well apparently one of your four tables has a Title. Although you didn't say so, I think Title is a property of Table1. It seems that you have the Id of a Table1Element you want to select the Title of the Table4 that owns this Table1Element

Input: RequestedId = 21803

• Table4Element with Id A has a Title
• Table3Element with foreign key Table4ElementId A has Id B
• Table2Element with foreign key Table3ElementId B has Id C
• Table1Element with foreign key Table3ElementId C has Id 21803

you want Title.

Using the virtual properties makes the query simple. Entity Framework will do the joins for you:

int requestedId = 21803;
var requestedTitle = myDbContext.Table1Elements              // from all Table1 elements
    .Where(table1Element => table1Element.Id = requestedId)  // keep the one with the requestedId
    .Select(table1Element = table1Element                    // and select the Title
                           .Table2Element                    // of the Table4 element
                           .Table3element                    // that it belongs to
                           .Table4Element
                           .Title)
    .FirstOrDefault();  // You know there is only one

Very intuitive and easy to understand.

I don't know why, but some people insist on doing the joins themselves. Using method syntax a join with four tables looks horrific:

 int requestedId = 21803;
var requestedTitle = myDbContext.Table1Elements              // from all Table1Elements
    .Where(table1Element => table1Element.Id = requestedId)  // keep the one with the requestedId
    .Join(myDbContext.Table2Elements,                        // join with Table2Elements
    table1Element => table1Element.Table2ElementId,          // from Table1 take the foreign key
    table2Element => table2Element.Id,                       // from Table2 take the primary key
    (table1Element, table2Element) => table2Element)         // when they match keep the Table2
    .Join(myDbContext.Table3Elements,                        // to join with Table3
    table2Element => table2Element.Table3ElementId,          // foreign key to Table3
    table3Element => table3Element.Id,                       // primary key
    (table2Element, table3Element) => table3Element)         // keep table3Element
    .Join(myDbContext.Table4Elements,                        // to join with Table4
    table3Element => table3Elemen.Table4ElementId,           // foreign key
    table4Element => table4Element.Id,                       // primary key
    (table3Element, table4Element) => table4Element.Title)   // keep the Title
    .FirstOrDefault();                                       // expect only one element

Using query syntax this query is easier, but still not as easy and intuitive as using the virtual properties. Still it is you to convince your boss that the method you select is the best to understand, test and maintain