Risky Business with LINQ to SQL and OR Designer?
- by Toadmyster
I have two tables with a one to many relationship in SQL 2008. The first table (BBD)
PK | BBDataID | int
| Floor_Qty | tinyint
| Construct_Year | char(4)
| etc, etc
describes the data common to all buildings and the second (BBDCerts)
PK | BBDCertsID | int
| BBDataID | int
| Certification_Type | varchar(20)
| etc, etc
is a collection of certifications for a particular building. Thus, the primary key in BBD (BBDataID) is mapped to the corresponding field in BBDCerts via an FK relationship, but BBDCertsID is the second table's primary key and BBDataID is not because it will not be unique.
My problem is that I want to be able to use the OR generated data context to get at the list of certs when I access a particular record in the BBD table. For instance:
Dim vals = (From q in db.BBD
Where q.BBDataID = x
Select q.Floor_Qty, q.Construct_Year, q.BBDCerts).SingleOrDefault
and later be able to access a particular certification like this:
vals.BBDCerts.Certification_Type.First
Now, the automatic associations created when the SQL tables are dropped on the design surface don't generate the EntityRef associations that are needed to access the other table using the dot notation. So, I have to use the OR designer to make the BBDCerts BBDataID a primary key (this doesn't affect the actual database), and then manually change the association properties to the appropriate OneToMany settings.
There might be a better way to approach this solution but my question is, is the way I've done it safe? I've done a barrage of tests and the correct cert is referenced or updated every time. Frankly, the whole thing makes me nervous.
