how can I 'walk' the relationships between tables in LINQ?

linq, linq-to-sql, linqpad

Solution

You can use reflection to examine the properties on each type in the context. (In LinqPad, the context is `this`).

- Value and string properties will be scalar fields on the table,

- EntitySet properties will represent [something]-to-many relationships, and

- other types will be [something]-to-one relationships.

If you connect the two sides of the relationships you can figure out what the [something] is in each case. Does that make sense?

Edit

I was just poking around a little, and there's a better approach. The model information is available via the Mapping property. Try this:

var tableData = from t in this.Mapping.GetTables()
                select new 
                {
                    t.TableName,
                    Associations = 
                        from a in t.RowType.Associations
                        select new
                    {
                        a.ThisMember.Name,
                        TypeName = a.ThisMember.Type.Name
                    }
                };
tableData.Dump();

Assuming you've activated Auto Completion, it should be a piece of cake to find the exact data you're interested in by exploring the properties on this meta data.

Problem

Let's say I have three tables: ``` Office ID SalespeopleOffice ID OfficeID PersonID People ID ManagerID ``` In LINQ to SQL, how can I start from the `SalespeopleOffices` table and "walk" from that table to the `People` table or the `Office` table via the relationships between the tables? Specifically without knowing what those relationships are; pull the data about the relationships instead of interacting with the objects directly. I'm looking for a way to programatically analyze table relationships. The database I'm working with has many more tables than this, so in reality it's a lot more complex than this. I'd ideally like to create a LinqPad script to do this.

Original source