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

[Stefan Wilhelm]

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

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?

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.

 

[Stefan Wilhelm]

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

 

[Jon Skeet [C# MVP]]

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

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