Bug: Multicolumn pk with foreign key

I have a legacy db where one table 'B' has a composed primary key which includes a column 'data_id' which is a foreign key to table 'A'. I have imported both tables (first A then B) into Lightspeed, but the model is not an exact representation of the db. The relation from B to A is present, but no foreign key column name is given, which means the relation column is called "DataId" and not "data_id". Furthermore I cannot enter the correct column name, because the column name is already used within this table in the primary key representation.

Please fix the problems with such a mixed primary key (or at least add an error message during the import - which would not help me with this legacy db).

MSSQL code for the two tables:

USE [LS_TEST]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[A](
    [id] [bigint] NOT NULL,
    [value] [int] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[B](
    [number] [int] NOT NULL,
    [data_id] [bigint] NOT NULL,
 CONSTRAINT [PK_Combined] PRIMARY KEY CLUSTERED
(
    [number] ASC,
    [data_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[B]  WITH CHECK ADD  CONSTRAINT [FK_Combined_Data] FOREIGN KEY([data_id])
REFERENCES [dbo].[A] ([id])
GO

ALTER TABLE [dbo].[B] CHECK CONSTRAINT [FK_Combined_Data]
GO

There is a known issue in designer import with primary keys that are also foreign keys.

To resolve this, select the association, go to the Properties window, and in the Key Property Reference field enter:

Id.DataId

This tells LightSpeed that the FK for the association is the DataId member of the Id/PK (which is in turn mapped to the data_id column), and that it should not autogenerate a separate DataId field in the entity.

I tried this and created a migration script, which resulted in the following ("Falsche Syntax in der Nähe von" may be translated as "Wrong syntax near"):

System.Data.SqlClient.SqlException: Falsche Syntax in der Nähe von '.'.
   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   bei System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   bei System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   bei System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   bei Mindscape.LightSpeed.Generator.Extraction.DdlUpdater.ExecuteSql(String sql)
   bei Mindscape.LightSpeed.Generator.Extraction.DdlUpdater.AddTable(TableName tableName, Field identityField, IEnumerable`1 fields, IEnumerable`1 fkFields)
   bei Mindscape.LightSpeed.Migrations.Migration.AddTable(String name, String owner, Field identity, Field[] fields)
   bei Locoplan.Domain.Roms.Modifications.InitialSetup.Up() in d:\VS Projekte\LocoPlan\trunk\src\Locoplan.Domain.Roms.Modifications\20110315073833_InitialSetup.cs:Zeile 992.
   bei Mindscape.LightSpeed.Migrations.Migrator.Execute(Migration migration, Direction direction)
   bei Mindscape.LightSpeed.Migrations.Migrator.MigrateUp(MigrationVersion currentVersion)
   bei Mindscape.LightSpeed.Migrations.Migrator.Execute()
   bei Mindscape.LightSpeed.Migrations.Migrator.Migrate(IMigrationLoader source, ProviderType provider, String connectionString, Nullable`1 toVersion)

The line in my code looks like:

  Migrator.Migrate(loader, ProviderType.SqlServer2005, connectionString, null);

The migration-code is as follows:

    public override void Up()
    {
      this.AddKeyTable("KeyTable", ModelDataType.Int32);
      this.AddTable("A", null, new Field("Id", ModelDataType.Int64, false), new Field[] {
            new Field("Value", ModelDataType.Int32, true)});
      this.AddTable("B", null, new Field("data_id", ModelDataType.Int32, false), new Field[] {
            new ForeignKeyField("Id.DataId", ModelDataType.Int64, false, "A", null, "Id")});
    }

After taking a look at the code, the exception is one thing, but I would also expect the column "number" in the migration. Furthermore I expect

"this.AddTable("B", null, new Field("data_id", ModelDataType.Int32, false), new Field[] {"

to be

"this.AddTable("B", null, new Field("data_id", ModelDataType.Int64, false), new Field[] {"

P.S.: Is there a way to add a sql-logger to the migration?

Migrations don't support creating tables with composite keys.  LightSpeed supports composite keys only for compatibility with existing tables that can't be changed to use surrogate keys, and if you're dealing with an existing table then you won't need to create it with a migration.

That said, we should obviously be skipping the table and providing a meaningful error explaining why, rather than producing incorrect migration code.  I'll log a bug for this.

If you do need to replicate a legacy table schema using a migration, you can do so using the ExecuteNativeCommand method (passing in the SQL CREATE TABLE statement from your existing database script).

It isn't currently possible to add a SQL logger to the migration, but that sounds like it would be a really handy feature!  (And I'm pretty sure we have all the plumbing, we'd just need to provide an API.)  Thanks for the suggestion -- we'll see if we can get that done for you!