LINQ to SQL: Many to many with Multiple primary keys?

Question

Ok this is something a bit simple but as I'm new to Linq and to SQL overall I guess I need a little help. Most tutorials assumed some sql knowledge and therefor weren't as helpful and also hard to search as I do not know this join name ( Inner left? ).

Situation:

TableParent with 2 primaryKeys, ParentKey1 and ParentKey2 TableChild with 1 primaryKey, ChildKey TableConnector with columns ParentKey1, ParentKey2 and ChildKey

This is where I think I should go with my Linq query. Notice I'm fetching all childs belonging to a parent so I have it's keys as parameters.

var query = from conn in db.TableConnector
            join child in db.TableChild on conn.ChildKey equals child.childKey
            join par in db.TableParent on conn.ParentKey1 equals par.parentkey1 into connGroup
            from co in connGroup
            where co.ParentKey1 == Parameter1
            Select child;

Well I tthink this works up to a point, let's say if parent had only one key, am I right?

I guess I have to join some more into a second group but I'm currently lost.

Answer 1

tableParent only needs one primary key (it's own Id) and tableChild needs one (it's Id) the connectorTable only needs two columns to make the many-to-many-relation work: ParentId and ChildId

For each relation between a parent and a child you simply add one row to the connectorTable, and in order to retrieve the results try this:

select * from tableParent
inner join connectorTable
on tableParent.Id = connectorTable.ParentId
inner join tableChild
on connectorTable.ChildId = tableChild.Id

If your reason for multiple keys in the parent table is that you want to create relations between parents as well this needs to be addressed either via a relation field in the parent table (one-to-many) or another relationTable (many-to-many)

Answer 2

Firstly, you don't have two primary keys on your table. You can only have one primary key (hence the name primary). It sounds like you have a composite primary key, which means a key that is composed of more than one column.

I'm not sure what problem you are trying to solve, but it seems to be retrieving all TableChild rows for a given TableParent key. It should be something like this:

db.TableParent
    .Single(parent => parent.ParentKey1 == key1 && parent.ParentKey2 == key2)
    .TableConnectors.Select(connector => connector.TableChild)

If you have your tables mapped correctly on your Linq-to-Sql designer then you don't have to manually join them - that's what the Linq-to-Sql code generation does for you.

For example, when you have a TableConnector you will be able to retrieve the TableChild rows for it using something like this

TableConnector t = db.TableConnectors.First();
List<TableChilds> tableChilds = tableConnector.TableChilds.ToList();