Risky Business with LINQ to SQL and OR Designer?
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, etcdescribes the data common to all buildings and the second (BBDCerts)
PK | BBDCertsID | int
| BBDataID | int | Certification_Type | varchar(20) | etc, etcis 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 cer开发者_如何转开发t is referenced or updated every time. Frankly, the whole thing makes me nervous.
Thanks for the vote of confidence, Tom. I was beginning to think I was crazy.
Apparently the key (no pun intended) is the primary key on the child table. Turns out I didn't have a primary key set on the child table at all. I added (re-added?) the key for BBDCertsID, rebuilt the data context and it's working now. Not sure why it was working with my OR-only PK declaration but now the configuration makes sense and doesn't require manual tinkering.
精彩评论