Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / Languages / C# / March 2008

Tip: Looking for answers? Try searching our database.

Linq to Sql: Adding nullable association in O/R Designer

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Stefan Wilhelm - 03 Mar 2008 09:37 GMT
Hi all,
I have a table that has an optional entry so that column is defined as int.
I want to associate it with a column in another table. That column must be
present so it is defined as int NOT NULL.

O/R Designer will not associate them saying that their definition does not
match. That does not seem reasonable to me to look at the nullable aspect of
a column in for matching.

Trying to join the tables with a handwritten linq query makes no problems.
Could someone explain to me why the nullable aspect is important for
determining if the column types match?

-Stefan
Jon Skeet [C# MVP] - 03 Mar 2008 10:19 GMT
> I have a table that has an optional entry so that column is defined as int.
> I want to associate it with a column in another table. That column must be
[quoted text clipped - 7 lines]
> Could someone explain to me why the nullable aspect is important for
> determining if the column types match?

The situation's not entirely clear to me. Is this the primary key of
the table with the "not null" constraint? I'm not sure I've ever
defined an association based on something other than a primary key -
but I *have* got optional assocations working with no trouble.

Signature

Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet   Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk

Stefan Wilhelm - 03 Mar 2008 11:28 GMT
Following example:

TableA:
[Column(Storage="_pkID", DbType="Int NOT NULL", IsPrimaryKey=true)]
public int pkID
[Column(Storage="_Groupname", DbType="NVarChar(50)")]
public string Groupname

TableB:
[Column(Storage="_pkID", DbType="Int NOT NULL", IsPrimaryKey=true)]   
public int pkID
[Column(Storage="_Name", DbType="NVarChar(50)")]
public string Name
[Column(Storage="_fkGoupID", DbType="Int")]
public System.Nullable<int> fkGroupID

This means, I have an entity of TableB which can be (but must not be)
assigned to a entity of TableA. Trying to set an association from
TableB.fkGroupID to TableA.pkID throws the errormessage. The Problem is, the
designer wants to declare an association between System.Nullable<int> and
System.Int - and this is just not the same datatype - the errormessage is
thrown.

For my usual work I have to deal with this. SQL-Server has no problems with
a join (inner or left) with this combination, joining "int" to "int NOT
NULL". Also Linq query ca do this:
var q = from b in db.TableB
join a in db.TableA on b.fkGroupID equals a.pkID //join int? to int
select XYZ;

Now my question is: How to deal with this?
I thought a big advantage of Linq to Sql was that I did not constantly have
to build joins by code - but with the designer it seems not to be possible...

-Stefan

> The situation's not entirely clear to me. Is this the primary key of
> the table with the "not null" constraint? I'm not sure I've ever
> defined an association based on something other than a primary key -
> but I *have* got optional assocations working with no trouble.
Jon Skeet [C# MVP] - 03 Mar 2008 11:49 GMT
> Following example:
>
[quoted text clipped - 11 lines]
> [Column(Storage="_fkGoupID", DbType="Int")]
> public System.Nullable<int> fkGroupID

<snip>

Okay, that's what I've got in the model I'm looking at, too, and it's
fine. Everything was generated by the designer.

Can I suggest you grab my source code and have a look at the
differences? It's available at

http://csharpindepth.com/Downloads.aspx

It includes the database files too.

Signature

Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet   Blog: http://www.msmvps.com/jon.skeet
World class .NET training in the UK: http://iterativetraining.co.uk


Rate this thread:







Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.