none
SqlMetal reversing column order in multi-column foreign key RRS feed

  • Question

  • I've boiled this down to the code below so hopefully it will be easy to reproduce.  Create a test.dbml file that looks like the below:

     

    <?xml version="1.0" encoding="utf-8"?>

    <Database Name="dev_tools" Class="Dev_tools" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">

    <Table Name="Dash_Parameter_Value" Member="Dash_Parameter_Value">

    <Type Name="Dash_Parameter_Value">

    <Column Member="Content_id" Name="Content_id" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />

    <Column Member="Dash_page_guid" Name="Dash_page_guid" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />

    <Association Name="FK_Dash_Parameter_Value_Dash_Section" Member="Dash_Section" ThisKey="Dash_page_guid,Content_id" OtherKey="Dash_page_guid,Content_id" Type="Dash_Section" IsForeignKey="true" />

    </Type>

    </Table>

    <Table Name="Dash_Section" Member="Dash_Section">

    <Type Name="Dash_Section">

    <Column Member="Content_id" Name="Content_id" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />

    <Column Member="Dash_page_guid" Name="Dash_page_guid" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />

    <Association ThisKey="Dash_page_guid,Content_id" OtherKey="Dash_page_guid,Content_id" Name="FK_Dash_Parameter_Value_Dash_Section" Member="Dash_Parameter_Value" Type="Dash_Parameter_Value" DeleteRule="NO ACTION" />

    </Type>

    </Table>

    </Database>

     

    When I use sqlmetal to generate C# code using this command line:

     

    sqlmetal.exe /code:test.cs test.dbml

     

    The generated test.cs doesn't compile because it contains the following code inside the setter for the Dash_Section property of Dash_Parameter_Value (note that it has the two part key out of order):

     

    value.Dash_Parameter_Value.Add(this);

    this._Dash_page_guid = value.Content_id;

    this._Content_id = value.Dash_page_guid;

     

    I'm at a loss to get around this because as you can see the columns are listed in order for both the ThisKey and OtherKey attributes of both Assocation elements in the XML.

     

    Can somebody take a look at this and see if this is a sqlmetal bug or if I'm just way off in the weeds?  It looks like I have version 1.00.21022 of sqlmetal.  Thanks.

    Monday, March 17, 2008 10:49 PM

Answers

  • The meta-data readers that SQLMetal and the designer are using are not seeing the direct primary key declaration that shows the columns in the same order as the foreign key declaration.  They only end up seeing the main set of column declarations with extra data identifying individual columns as being part of the primary key.  Because of this, LINQ to SQL understands the natural order of the primary key to be the order as specified in the list of column declarations.  In that list, contend_id is defined first and dash_page_guid last.  However, the foreign key is explicity defined in the other order. Ideally, LINQ to SQL components would not get confused by this, yet that's what's happening.

     

    Tuesday, March 18, 2008 8:44 PM
    Moderator

All replies

  • Try making the order defined in the association match the order the the columns appear in the table element.  They are currently reversed.

     

    Tuesday, March 18, 2008 4:17 AM
    Moderator
  • Yes, that does fix it, but I'm noticing that the .dbml gets generated this way both by SQLMetal and when I use the Visual Studio designer and drag the tables out there.  Am I going to need to process the generated dbml and perform this reordering?

     

    Let me know if you want me to post the SQL CREATE TABLE statements that would hopefully allow you to reproduce this.  Basically the columns in the DB are in a different order from the ordering of the FK.

     

    Tuesday, March 18, 2008 6:31 PM
  • It's possible that the FK is defined in a different order than the PK is defined.  LINQ to SQL does get confused by this.

     

    Tuesday, March 18, 2008 6:42 PM
    Moderator
  • I am able to reproduce my problem by running the SQL below to create two tables, then dragging those tables into the Linq to SQL table view thing (to generate .dbml), then using SQLMetal from that .dbml to create C# code that won't compile.

     

    As you can see below, the ordering of the columns in the PK matches the ordering of the columns in the FK.  However, the columns in the table itself are in reverse order.  Perhaps that's where the problem lies.

     

    Let me know what you think.  Thanks for your quick responses on this.

     

    Code Snippet

    /****** Object: Table [dbo].[xDash_Parameter_Value] Script Date: 03/18/2008 15:31:25 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /****** Object: Table [dbo].[xDash_Section] Script Date: 03/18/2008 15:31:38 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[xDash_Section](

    [content_id] [int] NOT NULL,

    [page_seq] [smallint] NULL,

    [client_id] [int] NOT NULL,

    [sort_order] [float] NULL,

    [last_modified_user_id] [int] NOT NULL,

    [last_modified_timestamp] [datetime] NOT NULL,

    [data_guid] [uniqueidentifier] NULL,

    [dash_page_guid] [uniqueidentifier] NOT NULL,

    CONSTRAINT [xPK_Dash_Section] PRIMARY KEY CLUSTERED

    (

    [dash_page_guid] ASC,

    [content_id] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    CREATE TABLE [dbo].[xDash_Parameter_Value](

    [content_id] [int] NOT NULL,

    [parameter_group_id] [int] NOT NULL,

    [parameter_id] [int] NOT NULL,

    [int_value] [int] NULL,

    [string_value] [nvarchar](255) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NULL,

    [datetime_value] [datetime] NULL,

    [last_modified_user_id] [int] NOT NULL,

    [last_modified_timestamp] [datetime] NOT NULL,

    [client_id] [int] NOT NULL,

    [data_guid] [uniqueidentifier] NULL,

    [dash_page_guid] [uniqueidentifier] NOT NULL,

    CONSTRAINT [xPK_Dash_Parameter_Value] PRIMARY KEY CLUSTERED

    (

    [content_id] ASC,

    [dash_page_guid] ASC,

    [parameter_group_id] ASC,

    [parameter_id] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[xDash_Parameter_Value] WITH CHECK ADD CONSTRAINT [xFK_Dash_Parameter_Value_Dash_Section] FOREIGN KEY([dash_page_guid], [content_id])

    REFERENCES [dbo].[xDash_Section] ([dash_page_guid], [content_id])

    GO

     

     

    Tuesday, March 18, 2008 7:46 PM
  • The meta-data readers that SQLMetal and the designer are using are not seeing the direct primary key declaration that shows the columns in the same order as the foreign key declaration.  They only end up seeing the main set of column declarations with extra data identifying individual columns as being part of the primary key.  Because of this, LINQ to SQL understands the natural order of the primary key to be the order as specified in the list of column declarations.  In that list, contend_id is defined first and dash_page_guid last.  However, the foreign key is explicity defined in the other order. Ideally, LINQ to SQL components would not get confused by this, yet that's what's happening.

     

    Tuesday, March 18, 2008 8:44 PM
    Moderator
  •  

    OK, I'll fix it in the XML then.  For what it's worth, this is a pretty major pain to fix at the DB level as we have thousands of existing tables and reordering columns is a pretty hefty conversion.  So for what it's worth, my two cents is that improvements here would be pretty helpful.

     

    Thanks again for the quick responses.

    Wednesday, March 19, 2008 3:09 PM
  •  

    I had this problem too, and have had to fix it by adjusting the order of the <column> elements to match the primary key for each table.

     

    We have a lot of tables that have this problem, so I just wrote code to fix the dbml.    At the same time I fixed the naming convention of the members too..  no more stupid foreign key member names with a 1 tacked on the end to make it unique.

    Wednesday, March 19, 2008 6:17 PM
  • Yes, I'm also fixing SQLMetal's bad habit of upcasing all of the column names while I'm in there.

     

    Thursday, March 20, 2008 6:49 PM