I have a SQL-2005 database with 50 tables that I map to C# code with a
Linq-to-SQL dbml file. I have done it many times and it always work fine.

But this time I was surprised to to find out that some of the database
relations failed to turn up as code associations!!!

All tables in database have proper relations defined with foreign key.
They all turn up nicely in the dbml GUI.
But two of them are NOT implemented in code generation .....!!

I have compared them in detail with all other relations in database and dbml
GUI but find no strange difference anywhere.
- They are defined on int columns on both ends
- Cardinality OneToMany
- Child Property=True, Access=Public, Name defined
- Parent Property Access=Public, Name defined
- Unique= False

Tables are named: Person, Role and Person_Role
I have stumbled on situations where table names in database were identical
with reserved names in code. On those occassions I have changed entity names
in dbml. Just to be sure I switched names this time also but with no
difference.

I use VS2008 SP1 with Framework 3.5 SP1.

Hello Jakob

From the case description, I understand the importance of the issue in your
project. We are performing researches. In order to help you more
efficiently and effectively, I want to have a clearer picture of the
symptom and reproduce the problem in my lab. Would you please provide us
with the following information? Thank you for the cooperation in advance.

1. DB Schema

You mentioned three tables in the DB: Person, Role and Person_Role. Am I
right that Person and Role have the N:N relationship? May I get the schema
of the database or just these three tables? The schema can be exported in
SQL Server by right-clicking the DB -> Tasks -> Generate Scripts... Please
send the script to my mailbox: (E-Mail Removed). Thanks.

2. Codes

Would you please tell us how you used the generated LINQ to SQL classes,
Person, Role, Person_Role, in the dbml GUI? Some code snippets and a short
description of the miss of the association will be very helpful. Thanks

Regards,
Jialiang Ge ((E-Mail Removed), remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(E-Mail Removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subs...#notifications.

MSDN Managed Newsgroup support offering is for non-urgent issues where an
initial response from the community or a Microsoft Support Engineer within
2 business day is acceptable. Please note that each follow up response may
take approximately 2 business days as the support professional working with
you may need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subs.../aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

I was able to reproduce the problem in a blank new database and new code
project. Database script and solution source code is enclosed in zipfile.

Program class holds example calls with description on expected behaviour.

Thanks for your concern!

Hello Jakob

Thank you for the project, from which I notice that you are building a many
to many relationship between Person and Role. The problem results from the
current status of LINQ - SQL that "many to many" relationships are not well
supported. This means that there is no specific relation attribute that
allows defining such a relation. The product group is in progress to
improve it. What we have to do currently is to create as many entities as
the physical model does and define your intermediate table on the code
side. Please refer to these two articles for an example:

How to implement a many-to-many relationship using Linq to Sql ?
http://blogs.msdn.com/mitsu/archive/...t-a-many-to-ma
ny-relationship-using-linq-to-sql.aspx
(if the long URL is broken by the newsgroup system, please concat it
manually)

Create many-to-many relationships in LINQ for SQL
http://www.codeproject.com/KB/databa...many2many.aspx

If you have any difficulties when trying the solution, please feel free to
tell me.

Regards,
Jialiang Ge ((E-Mail Removed), remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(E-Mail Removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

Thanks for your answer.
You are partly right.
Of course there is a limitation in Linq-to-SQL that does not allow
many-to-many relations be automatically generated in code.
This limitation is well known and I am aware of this.
That is not really a problem for me.

What bothered me was that the associations between the tables were not
generated in code on those particular tables.

Today I managed to find out the problem myself!
The dbml generation is obviously dependant on all involved tables to have
primary keys defined. I had done this on all tables in database but had
forgotten to do it on the intermediate table Person_Role .....

The dbml fooled me by hiding the problem. The associations turned up in
design view and all properties on the associations were correct.

So if someone else stumbles into the same problem ... this is the simple
explanation

jaklith,

I had the same issue - your post solved it.

Primary key the problem.

Thanks, mate.

> On Monday, January 26, 2009 4:39 AM jaklith wrote:

> I have a SQL-2005 database with 50 tables that I map to C# code with a
> Linq-to-SQL dbml file. I have done it many times and it always work fine.
>
> But this time I was surprised to to find out that some of the database
> relations failed to turn up as code associations!!!
>
> All tables in database have proper relations defined with foreign key.
> They all turn up nicely in the dbml GUI.
> But two of them are NOT implemented in code generation .....!!
>
> I have compared them in detail with all other relations in database and dbml
> GUI but find no strange difference anywhere.
> - They are defined on int columns on both ends
> - Cardinality OneToMany
> - Child Property=True, Access=Public, Name defined
> - Parent Property Access=Public, Name defined
> - Unique= False
>
> Tables are named: Person, Role and Person_Role
> I have stumbled on situations where table names in database were identical
> with reserved names in code. On those occassions I have changed entity names
> in dbml. Just to be sure I switched names this time also but with no
> difference.
>
> I use VS2008 SP1 with Framework 3.5 SP1.


>> On Tuesday, January 27, 2009 4:25 AM jialg wrote:

>> Hello Jakob
>>
>> From the case description, I understand the importance of the issue in your
>> project. We are performing researches. In order to help you more
>> efficiently and effectively, I want to have a clearer picture of the
>> symptom and reproduce the problem in my lab. Would you please provide us
>> with the following information? Thank you for the cooperation in advance.
>>
>> 1. DB Schema
>>
>> You mentioned three tables in the DB: Person, Role and Person_Role. Am I
>> right that Person and Role have the N:N relationship? May I get the schema
>> of the database or just these three tables? The schema can be exported in
>> SQL Server by right-clicking the DB -> Tasks -> Generate Scripts... Please
>> send the script to my mailbox: (E-Mail Removed). Thanks.
>>
>> 2. Codes
>>
>> Would you please tell us how you used the generated LINQ to SQL classes,
>> Person, Role, Person_Role, in the dbml GUI? Some code snippets and a short
>> description of the miss of the association will be very helpful. Thanks
>>
>> Regards,
>> Jialiang Ge ((E-Mail Removed), remove 'online.')
>> Microsoft Online Community Support
>>
>> Delighting our customers is our #1 priority. We welcome your comments and
>> suggestions about how we can improve the support we provide to you. Please
>> feel free to let my manager know what you think of the level of service
>> provided. You can send feedback directly to my manager at:
>> (E-Mail Removed).
>>
>> ==================================================
>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/en-us/subs...#notifications.
>>
>> MSDN Managed Newsgroup support offering is for non-urgent issues where an
>> initial response from the community or a Microsoft Support Engineer within
>> 2 business day is acceptable. Please note that each follow up response may
>> take approximately 2 business days as the support professional working with
>> you may need further investigation to reach the most efficient resolution.
>> The offering is not appropriate for situations that require urgent,
>> real-time or phone-based interactions. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> http://msdn.microsoft.com/en-us/subs.../aa948874.aspx
>> ==================================================
>> This posting is provided "AS IS" with no warranties, and confers no rights.


>>> On Tuesday, January 27, 2009 5:02 AM jaklith wrote:

>>> I was able to reproduce the problem in a blank new database and new code
>>> project. Database script and solution source code is enclosed in zipfile.
>>>
>>> Program class holds example calls with description on expected behaviour.
>>>
>>> Thanks for your concern!


>>>> On Tuesday, January 27, 2009 11:17 PM jialg wrote:

>>>> Hello Jakob
>>>>
>>>> Thank you for the project, from which I notice that you are building a many
>>>> to many relationship between Person and Role. The problem results from the
>>>> current status of LINQ - SQL that "many to many" relationships are not well
>>>> supported. This means that there is no specific relation attribute that
>>>> allows defining such a relation. The product group is in progress to
>>>> improve it. What we have to do currently is to create as many entities as
>>>> the physical model does and define your intermediate table on the code
>>>> side. Please refer to these two articles for an example:
>>>>
>>>> How to implement a many-to-many relationship using Linq to Sql ?
>>>> http://blogs.msdn.com/mitsu/archive/...t-a-many-to-ma
>>>> ny-relationship-using-linq-to-sql.aspx
>>>> (if the long URL is broken by the newsgroup system, please concat it
>>>> manually)
>>>>
>>>> Create many-to-many relationships in LINQ for SQL
>>>> http://www.codeproject.com/KB/databa...many2many.aspx
>>>>
>>>> If you have any difficulties when trying the solution, please feel free to
>>>> tell me.
>>>>
>>>> Regards,
>>>> Jialiang Ge ((E-Mail Removed), remove 'online.')
>>>> Microsoft Online Community Support
>>>>
>>>> =================================================
>>>> Delighting our customers is our #1 priority. We welcome your comments and
>>>> suggestions about how we can improve the support we provide to you. Please
>>>> feel free to let my manager know what you think of the level of service
>>>> provided. You can send feedback directly to my manager at:
>>>> (E-Mail Removed).
>>>>
>>>> This posting is provided "AS IS" with no warranties, and confers no rights.
>>>> =================================================


>>>>> On Wednesday, January 28, 2009 5:16 AM jaklith wrote:

>>>>> Thanks for your answer.
>>>>> You are partly right.
>>>>> Of course there is a limitation in Linq-to-SQL that does not allow
>>>>> many-to-many relations be automatically generated in code.
>>>>> This limitation is well known and I am aware of this.
>>>>> That is not really a problem for me.
>>>>>
>>>>> What bothered me was that the associations between the tables were not
>>>>> generated in code on those particular tables.
>>>>>
>>>>> Today I managed to find out the problem myself!
>>>>> The dbml generation is obviously dependant on all involved tables to have
>>>>> primary keys defined. I had done this on all tables in database but had
>>>>> forgotten to do it on the intermediate table Person_Role .....
>>>>>
>>>>> The dbml fooled me by hiding the problem. The associations turned up in
>>>>> design view and all properties on the associations were correct.
>>>>>
>>>>> So if someone else stumbles into the same problem ... this is the simple
>>>>> explanation


>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>> Store ASP.NET Site Visitor Stats in MongoDb
>>>>> http://www.eggheadcafe.com/tutorials...n-mongodb.aspx