LINQ: Insert data into PK-FK tables

As already said in previous post, inserting data into SQL tables using LINQ is not that difficult. In this post, I will explain a little different case, kind of. We have 2 simple tables - MainData and AdditionalData. AdditionalData table has a primary key [DataLinkID] whose type is uniqueidentifier. [AdditionalData] table has a foreign key relationship with [OtherDataLinkID] of [MainData] table.

CREATE TABLE [dbo].[MainData](
     [Name] [nchar](10) NOT NULL,
     [Data] [nvarchar](50) NULL,
     [OtherDataLinkID] [uniqueidentifier] NOT NULL,
     CONSTRAINT [PK_MainData] PRIMARY KEY CLUSTERED
([Name] ASC)
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_DataLinkID] ON [dbo].[MainData]
(  [OtherDataLinkID] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AdditionalData](
     [DataLinkID] [uniqueidentifier] NOT NULL,
     [OtherData] [nvarchar](50) NULL,
CONSTRAINT [PK_AdditionalData] PRIMARY KEY CLUSTERED
([DataLinkID] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AdditionalData] WITH CHECK ADD CONSTRAINT [FK_AdditionalData_MainData] FOREIGN KEY([DataLinkID])
REFERENCES [dbo].[MainData] ([OtherDataLinkID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AdditionalData] CHECK CONSTRAINT [FK_AdditionalData_MainData]
GO

Once the tables are created in SQL Server, the following Linq To SQL diagram (DataClasses1.dbml) can be created in Visual Studio by drag and drop tables from Server Explorer. Diagram shows PK [OtherDataLinkID] and FK [DataLinkID] relationship.

By the way, I used DELETE CASCADE in this example, so if MainData record is deleted, related row in AdditionalData table is automatically deleted in SQL Server. This is a convenient feature. And I used uniqueidentifier column type to ensure that the record is uniquely identified. Sure, identitiy column can be used instead but uniqueidentifier is safter than identitiy since identitiy has max limit even if we typically don't hit the max.

Once LINQ classes are generated (by saving .dbml), we can insert a row into each table by using LINQ. And here is an example of inserting data. Red part is important since it enables the relationship between main and additional table. If this red part is removed, insertion will only occur to MainData table.

public void InsertData()
      {
         Guid linkID = Guid.NewGuid();

         DataClasses1DataContext db = new DataClasses1DataContext();
         MainData main = new MainData();
         main.Name = "Alex";
         main.Data = "data1";
         main.OtherDataLinkID = linkID;

         AdditionalData additional = new AdditionalData();
         additional.DataLinkID = linkID;
         additional.OtherData = "other data";

         main.AdditionalData = additional;

         db.MainDatas.InsertOnSubmit(main);
         db.SubmitChanges();
      }