We help IT Professionals succeed at work.
Get Started

Problem with LINQ to SQL Associations

FactsetWes asked
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...).
Watch Question
Most Valuable Expert 2012
Top Expert 2008
This problem has been solved!
Unlock 4 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE