Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Problem with LINQ to SQL Associations

Posted on 2009-05-07
9
1,370 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
ID: 24336045
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
ID: 24336935
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
ID: 24336964
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:FactsetWes
ID: 24337039
Yes, FolderLink is the intermediate table for many-to-many relationships.
No, it does not currently have a primary key defined.
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 24337219
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
ID: 24339962
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
ID: 24348273
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
ID: 24358489
Adding the primary key on those 2 columns caused the LINQ designer to generate the correct code, thanks!
0
 

Author Closing Comment

by:FactsetWes
ID: 31579234
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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