LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby AKRRKA » Mon 11 Mar 2013 06:01

Hello,

2 tables in database have many-to-many relationship via 3rd table. Inserting a row to 3rd table in MS SQL server raised an exception:

Auto-sync of multiple key parameters is not supported for SQL Server.


That works correct in MySQL server. We can we do to create many-to-many relationship in MS SQL server?
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby MariiaI » Mon 11 Mar 2013 12:34

We couldn't reproduce this issue on our environment with LinqConnect 4.1.197. We are sending you a sample project to the e-mail address you provided in your forum profile, please check that the letter is not blocked by your mail filter. Please make changes to it so that the issue could be reproduced and send it back to us or send us your project.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby AKRRKA » Mon 11 Mar 2013 13:18

Email is not received. Maybe mail filter is not missed. I ask again, and change the extension from the archive, such as a WindowsFormsApplication3.zip.RemoveThisExtension.
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby MariiaI » Mon 11 Mar 2013 13:27

We have sent you a sample project again. Please confirm whether you've received it or not.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby AKRRKA » Mon 11 Mar 2013 14:37

Have not received a letter. But I prepared an example, based on previously sent you the project.
You can download sample project Devart Sample 2 from: http://rghost.ru/44425798
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby AKRRKA » Mon 11 Mar 2013 14:44

I also have another problem with SQLServer. You can see it in the example 1.
You can download sample project Devart Sample 1 from: http://rghost.ru/44425941

Both examples work correctly with MySQL but do not work with SQLServer.

PS: Did not begin to create a separate topic, maybe they are related. Both issues are very critical. Please help on both.
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby MariiaI » Tue 12 Mar 2013 11:09

Regarding the issues you have reported:
1) The first issue, related to many-to-many relationship.
We have reproduced it, the problem is that the Auto-Synс property is set to 'OnInsert' for the 'OrderId' and 'CompanyId' properties (which are the foreign keys) in the 'CompanyOrders' entity class. In this situation there is no need in synchronization, as you insert the known values into the third table. Thus, please set the Auto-Synс to 'Never' for these two fields and save the changes.
As for the error when working with SQL Server, we will investigate it.

2) The second issue (Devart Sample 1 from: http://rghost.ru/44425941) - we don't get any exceptions with this sample. Please specify the following:
- what exactly errors you are getting with it;
- the versions of MySQL and SQL Server you are working with;
- the SQL script for creating database tables, that correspond to Order and Company entity classes (we have created these tables from your model, thus there may be some differences in them);
- the exact steps to reproduce (e.g., what events should be tested).

Looking forward to your reply.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby AKRRKA » Wed 13 Mar 2013 06:54

1) OK. But in MySQL works, and on SQLServer not, so please find out what the problem is, maybe it affect still on something.
2) I tried on multiple computers, anywhere have a bug.
Use Microsoft SQL Server 2008 R2 Enterprise Edition ver.10.50.1617.0 (64-bit) with Windows 7 Pro (64-bit) and MySQL 5.5.29-0 with Ubuntu 12.04.2.
Video for demonstration problem
Full Exception text
Script for SQL Server generated into Entity Developer
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

Script for SQL Server get from Microsoft SQL Server Management Studio 10.50.1617.0
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

AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby MariiaI » Wed 13 Mar 2013 13:59

According to the stack trace of the exception, most likely, the problem is that the null value is returned from the server instead of the generated "id". We are still unable to reproduce this error with your sample project. In both cases, the data are inserted into both MySQL and SQL Server tables without errors.
Please specify the SQL scripts you're getting while inserting data for both MySQL and SQL Server tables. To get them you could use dbMonitor with Devart.Data.LinqMonitor. For more information please refer to:
http://www.devart.com/linqconnect/docs/LinqMonitor.html
http://www.devart.com/linqconnect/docs/dbMonitor.html
http://www.devart.com/dbmonitor/download.html
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby AKRRKA » Thu 14 Mar 2013 06:39

You ever tried that example that I sent you (Devart Sample 1.zip)?
The problem lies in the fact that returns null (And rightly so. This is and should be.)!
Give me the opportunity to connect remotely to any computer (need TeamViewer) with VS2012 + SQLServer2008r2 and I'll show you the problem.

Look closely at the code. This:
//ord2.Company = com; //Without Company, CompanyID maybe NULL in model and database

Function full code:
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();


CompanyID is a foreign key, and it can be a NULL (Look carefully my previous post.)!

PS: You can contact me on Skype(akr_arz) or ICQ(5570155). I'm on-line.
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby MariiaI » Thu 14 Mar 2013 11:33

We have reproduced this issue. Apparently earlier when creating tables from the model in your sample (Devart Sample 1.zip), some of the settings were lost.
The reason is the same as in the first issue. The Auto-Synс property is set to 'OnInsert' for the 'CompanyId' property (which is the foreign key) in the 'Order' entity class. In this situation there is no need in synchronization, as you insert the known values into this table. Thus, please set the Auto-Synс to 'Never' for the 'CompanyId' property and save the changes.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby AKRRKA » Mon 18 Mar 2013 09:48

Ok. So works.
I think in this case, need show issue warnings, and recommended that change to Never.

Thanks.
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby MariiaI » Wed 20 Mar 2013 15:22

When working with SQL Server and MySQL the composite primary key can't be synchronized after inserts. Previously an exception has been thrown in this situation. We have made the following changes: now when working with SQL Server in such situation the "Auto-sync of multiple key parameters is not supported for SQL Server" and SqlNullValueException exceptions are not thrown.
We will consider the question of adding warnings in Entity Developer.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: LinqConnect Professional Version 4.1.197 (28-Feb-2013) - SQL Server

Postby MariiaI » Fri 05 Apr 2013 08:28

The new build of LinqConnect 4.2.222 is available for download now. It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information please refer to http://forums.devart.com/viewtopic.php?f=31&t=26311
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)