none
Association with other than Primary Key causing exception RRS feed

  • Question

  • Hi all,

    I am having a problem in Linq to SQL and I am expecting that I am just missing a configuration setting somewhere.  The problem that I am having is when two tables have a relationship and the column on the parent table is not the primary key but rather a column with a unique index.

    The parent's primary key column is nvarchar(50).  The column referenced by the child table is an int with a unique index on it.  Using Linq to Sql in VB, I need to read a parent record and remove the connection to the child records (set the child foreign key values to NULL).

    This is the code I am using to do this:

            Dim db As New TestDBDataContext  
     
            Dim david = (From parent In db.Parents Where parent.ParentName = "David").First  
            david.Childs.Clear()  
            db.SubmitChanges()  
     


    I put together a very simple example.  The SQL to create the two tables is listed below.  I used the OR designer and added the two tables along with their association to create the data context.

    What happens when the SubmitChanges method is called is that I get an exception that states: "Unable to cast object of type 'System.Int32' to type 'System.String'."

    I am assuming that somewhere in Linq to Sql's magic it is trying to take the foreign key from the child table and use it for the primary key on the parent table.

    Is this a problem in Linq to Sql?  Am I going about this the wrong way?  Am I just missing some configuration setting to let Linq to Sql know what I am really trying to do?

    Any help would be appreciated.

    Thanks,

    David

    SET ANSI_NULLS ON 
    GO  
    SET QUOTED_IDENTIFIER ON 
    GO  
    CREATE TABLE [dbo].[Parent](  
        [ParentID] [int] IDENTITY(1,1) NOT NULL,  
        [ParentName] [nvarchar](50) NOT NULL,  
     CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED   
    (  
        [ParentName] ASC 
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
    ON [PRIMARY]  
     
    /****** Object:  Table [dbo].[Child] ******/  
    SET ANSI_NULLS ON 
    GO  
    SET QUOTED_IDENTIFIER ON 
    GO  
    CREATE TABLE [dbo].[Child](  
        [ChildID] [int] IDENTITY(1,1) NOT NULL,  
        [ParentID] [intNULL,  
        [Name] [nvarchar](50) NOT NULL,  
     CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED   
    (  
        [ChildID] ASC 
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ONON [PRIMARY]  
    ON [PRIMARY]  
     
    GO  
    ALTER TABLE [dbo].[Child]  WITH CHECK ADD  CONSTRAINT [FK_Child_Parent1] FOREIGN KEY([ParentID])  
    REFERENCES [dbo].[Parent] ([ParentID])  
    GO  
    ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK_Child_Parent1]  
     

    Tuesday, December 16, 2008 9:25 PM

Answers

  • This is unfortunately a bug in LINQ to SQL and there isn't a known workaround at this time.

    [)amien

    http://damieng.com/
    Thursday, December 18, 2008 2:48 AM
    Moderator