Solved

Problem with LINQ to SQL Associations

Posted on 2009-05-07
9
1,359 Views
Last Modified: 2013-11-11
I'm working in C#.NET 3.5 with a SQL Server 2005 database in the back end.

I'm new to using LINQ and my boss wanted me to check it out for doing a prototype application for some new functionality that we're putting into one of our larger project.  

I'm essentially working with 3 tables, a Folder table, a File table, and an Entity table linking them.

So in my initial database design I had:
A Folder table with Primary Key FolderID and a bunch of data columns
A File table with Primary Key FileID and a bunch of data columns
An Entity table with Primary Key EntityID, Foreign Key FolderID, another Foreign Key FileID, and another Foreign Key ParentID associated with EntityID to define a tree structure

When I used LINQ to tie this to application, all the associations were automatically generated and worked just fine.  Then today when implementing a feature, I decided that the Entity table shouldn't actually have a separate primary key and instead should just have the ParentID be assosciated with FolderID as well, so I created a new table called FolderLink with 3 foreign keys(FolderID, FileID, ParentID) to replace the Entity table.

When I dragged the FolderLink table into the dbml designer, it appropriately displayed the associations, but the automatically generated FolderLink class does not have Folder or File properties the same way that the Entity class.  The Folder and File classes also did not have the FolderLinks property to mirror the Entities property that was autogenerated from the Entity association.

I have checked all the association properties in the designer and I can find no differences between the associations to FolderLink and the associations to Entity.  They all have OneToMany Cardinality, Child Property to True, appropriate Child Property and Parent Property names, and Child Property and Parent Property Access set to public.

The only discernible difference between the associations is that the 3rd association to FolderLink is another association from Folder, whereas Entity's ParentID association came from it's own EntityID field, but I honestly don't see how this could make a difference.

Does anyone have any ideas about why FolderLink would not have the auto-generated Folder and File properties?  As of right now I can only think of digging around in the designer.cs file and trying to imitate the code for Entity's associations for FolderLink, but I'd rather not touch the designer.cs as that has frequently caused headaches for me (mostly from doing something else in the designer and having all my changes erased...).
0
Comment
Question by:FactsetWes
  • 5
  • 4
9 Comments
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
Comment Utility
1) I have run into situations where I didn't think that I needed a primary key value, but there are so many expectations, that my database design always includes primary keys for all fields, even if there is only a logical foreign key relationship.

2) I don't quite understand how you changed the design, and what effect it had on the code generation.
0
 

Author Comment

by:FactsetWes
Comment Utility
1) I suppose it never really hurts to have a primary key aside from taking up a negligible amount of space.  Since this is still int the prototyping phase, I will consider whether a primary key should be included on the linking table.

2)

First db setup:
Folder Table
FolderID (int, not null, primary key)
... (a number of data fields)

File Table
FileID (int, not null, primary key)
... (a number of data fields)

Entity Table
EntityID (int, not null, primary key)
FolderID (int, null, foreign key on Folder.FolderID)
FileID (int, null, foreign key on File.FileID)
ParentID (int, null, foreign key on Entity.EntityID)

Second db setup:
Folder Table
 FolderID (int, not null, primary key)
 ... (a number of data fields)
  File Table
 FileID (int, not null, primary key)
 ... (a number of data fields)
 
 FolderLink Table
 FolderID (int, null, foreign key on Folder.FolderID)
 FileID (int, null, foreign key on File.FileID)
 ParentID (int, null, foreign key on Folder.FolderID)


When using the first db setup and the LINQ to SQL Classes designer in Visual Studio, I end up with an Entity class with the following properties:

Entities (child property created by the association on the EntityID and ParentID fields)
Entity (parent property created by the association on the EntityID and ParentID fields)
EntityID
Folder (created by the association on the FolderID fields)
FolderID
File (created by the association on the FileID fields)
FileID
ParentID

However, when I use the second db setup, my FolderLink class only has the following properties:

FolderID
FileID
ParentID

...despite the fact that there are associations that should generate Folder, File, Parent, and Children properties in the designer.

I hope this clarifies =\.

I think I'm actually going to just go back to the first db setup and just change the ParentID foreign key to be on FolderID (since as you pointed out, having primary keys is frequently useful and doesn't cost much) and see if that will work.  But I'm still baffled as to why LINQ to SQL wouldn't generate the properties from the associations in the second setupd.
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
Comment Utility
I use Team Suite, with the Database tool, which can do schema comparisons, and data comparisons.  The tool will skip any table that doesn't have primary keys when you are doing a data comparison.

FolderLink is the intermediate table for a many-to-many relationship, right?  Does FolderLink have any primary key defined?
0
 

Author Comment

by:FactsetWes
Comment Utility
Yes, FolderLink is the intermediate table for many-to-many relationships.
No, it does not currently have a primary key defined.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
Comment Utility
If you look at this AdventureWorks example:

Product --> ProductProductPhoto --> ProductPhoto

The ProductProductPhoto has a primary key with two columns (ProductID, ProductPhotoID).


AdventureWorks----Production.png
Production-LINQ-Designer.png
0
 

Author Comment

by:FactsetWes
Comment Utility
hmm, well I tried adding a unique key on the FolderLink table involving all three columns and the LINQ designer still does not properly generate the association properties.  I've gone back to using the Entity table and have simply changed the foreign key on ParentID to associate with FolderID rather than EntityID to generate the desired relationship and it seems to be working.

how do you actually add a primary key constraint with multiple columns in Management Studio?  I was only able to add a unique key constraint in the indexes and keys dialog and have only ever made primary key constraints in the designed by right clicking on a column and using the "set primary key" item...
0
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 500 total points
Comment Utility
1) Design the table

2) Click on the row selector for the first column.

3) Hold down the control key, and click on the row selectors for the other columns.

4) Click on the <Primary Key> button on the tool bar.
SSMS----Set-Multiple-Column-As-P.png
0
 

Author Comment

by:FactsetWes
Comment Utility
Adding the primary key on those 2 columns caused the LINQ designer to generate the correct code, thanks!
0
 

Author Closing Comment

by:FactsetWes
Comment Utility
I still don't exactly understand why LINQ would require a primary key on that table to properly create the associations, but thanks for your assistance!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now