?
Solved

Problem with LINQ to SQL Associations

Posted on 2009-05-07
9
Medium Priority
?
1,390 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 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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