?
Solved

Problem with LINQ to SQL Associations

Posted on 2009-05-07
9
Medium Priority
?
1,381 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
[X]
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
  • 5
  • 4
9 Comments
 
LVL 96

Assisted Solution

by:Bob Learned
Bob Learned earned 2000 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 2000 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
Stack Overflow Podcast - Frustrating Miracles

In this podcast, Stack Overflow interviewed Linux Academy CEO/Founder, Anthony James, and got his developer story!

"Follow your passion, be prepared to work hard and sacrifice, and, above all, don't let anyone limit your dreams."  - Donovan Bailey

 

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 2000 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Suggested Courses

771 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