That works correct in MySQL server. We can we do to create many-to-many relationship in MS SQL server?Auto-sync of multiple key parameters is not supported for SQL Server.
Code: Select all
-- Script was generated by Devart Entity Developer, Version 5.0.56.0
-- Script date 13.03.2013 10:57:58
-- Target Server: SQL Server
-- Server Version: 2008
--
-- Creating a table dbo.Companies
--
CREATE TABLE dbo.Companies (
CompanyID INT NOT NULL IDENTITY,
Name VARCHAR(MAX),
CONSTRAINT PK_Companies PRIMARY KEY (CompanyID)
)
GO
--
-- Creating a table dbo.Orders
--
CREATE TABLE dbo.Orders (
OrderID INT NOT NULL IDENTITY,
ContactID INT,
OrderDate DATETIME2 NOT NULL DEFAULT ('0000-00-00 00:00:00'),
Freight DECIMAL(10),
ShipDate DATETIME2,
ShipCompanyID INT,
Discount DECIMAL(10),
CompanyID INT,
CONSTRAINT PK_Orders PRIMARY KEY (OrderID),
CONSTRAINT FK_Orders_Company FOREIGN KEY (CompanyID) REFERENCES dbo.Companies (CompanyID)
)
GO
Code: Select all
USE [crmdemo]
GO
/****** Object: Table [dbo].[Companies] Script Date: 03/13/2013 11:00:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Companies](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](max) NULL,
CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED
(
[CompanyID] 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
SET ANSI_PADDING OFF
GO
USE [crmdemo]
GO
/****** Object: Table [dbo].[Orders] Script Date: 03/13/2013 11:01:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[ContactID] [int] NULL,
[OrderDate] [datetime2](7) NOT NULL,
[Freight] [decimal](10, 0) NULL,
[ShipDate] [datetime2](7) NULL,
[ShipCompanyID] [int] NULL,
[Discount] [decimal](10, 0) NULL,
[CompanyID] [int] NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID] 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].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Company] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[Companies] ([CompanyID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Company]
GO
ALTER TABLE [dbo].[Orders] ADD DEFAULT ('0000-00-00 00:00:00') FOR [OrderDate]
GO
Function full code://ord2.Company = com; //Without Company, CompanyID maybe NULL in model and database
Code: Select all
var sqldt = new CrmDemoContextSQLServer.CrmDemoDataContextSQLServer();
sqldt.Connection.Open();
var com = new CrmDemoContextSQLServer.Company();
com.Name = "Company N1";
var ord1 = new CrmDemoContextSQLServer.Order();
ord1.Company = com;
ord1.Discount = 1;
ord1.Freight = 1;
ord1.OrderDate = DateTime.Now;
ord1.ShipDate = DateTime.Now;
sqldt.Companies.InsertOnSubmit(com);
sqldt.Orders.InsertOnSubmit(ord1);
sqldt.SubmitChanges();
var ord2 = new CrmDemoContextSQLServer.Order();
//ord2.Company = com; //Without Company, CompanyID maybe NULL in model and database
ord2.Discount = 2;
ord2.Freight = 2;
ord2.OrderDate = DateTime.Now;
ord2.ShipDate = DateTime.Now;
sqldt.Orders.InsertOnSubmit(ord2);
sqldt.SubmitChanges();