Hi,

I'm having a problem saving some related entities :-

3 Tables related in a m:n relationship - there can be more than company associated with each fund entity. They are related via foreign keys on their primary keys. fund_id & company_id are identity fields. FundCompanies

Funds (PK: fund_id) FundCompanies (PK: fund_id;company_id) Companies (PK: company_id)

What I am doing is creating a new Companies entity, setting various fields (name / address etc) assigning it to an existing FundCompanies entity and saving that (ie. I want to change one company to another).

An exception is thrown :-

An exception was caught during the execution of an action query: UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_FundCompanies_Companies'. The conflict occurred in database 'RadarMarc', table 'Companies', column 'Company_Id'.

Query: UPDATE [RadarMarc].[dbo].[FundCompanies] SET [Company_Id]=@CompanyId WHERE ( [RadarMarc].[dbo].[FundCompanies].[Fund_Id] = @FundId1 AND [RadarMarc].[dbo].[FundCompanies].[Company_Id] = @CompanyId2)

Parameter: @CompanyId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0. Parameter: @FundId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3. Parameter: @CompanyId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1709.

It fails because it tries to save a company_id of zero (which is illegal) rather than the id of the newly saved company entity, yet as far as I can see the company_id is available in the saved company entity.

I've tried several versions of the code with a little success - here are a few cut down examples. Versions 1 and 2 do not work. Version 3 does. The fundCompany entity is a child of the fund entity which is being used in a datasource.

Version #1

CompaniesEntity company = new CompaniesEntity();
company.CompanyName = "Test Company";
company.Addr1 = "Test";
company.RoleId = 1;
company.Save();

FundCompaniesEntity fundCompany = (FundCompaniesEntity)bsFundCompany.DataSource;
fundCompany.Companies = company;
fundCompany.Save();

Version #2

CompaniesEntity company = new CompaniesEntity();
company.CompanyName = "Test Company";
company.Addr1 = "Test";
company.RoleId = 1;

FundCompaniesEntity fundCompany = (FundCompaniesEntity)bsFundCompany.DataSource;
fundCompany.Companies = company;
fundCompany.Save(true);

Versions 1 & 2 both fail with the same exception. In version 1, the company_id was correctly allocated when the company entity was saved. In version 2 the company doesn't appear to have saved.

Version #3

CompaniesEntity company = new CompaniesEntity();
company.CompanyName = "Test Company";
company.Addr1 = "Test";
company.RoleId = 1;
company.Save();

FundCompaniesEntity fundCompany = (FundCompaniesEntity)bsFundCompany.DataSource;
fundCompany.CompanyID = company.CompanyID;
fundCompany.Save();

Version 3 does work - if I directly assign the CompanyID then the record is saved correctly. Unfortunately that isn't really a workable solution as I wanted to handle all of this through a UnitOfWork and that fails with exactly the same error (I originally started with a UnitOfWork and then progressively simplified the code until I got something that worked).

Can you help, as I feel I must be missing something here? I would have thought I could assign the company entity?

SQL Server 2000 Visual Studio 2005 / WinForms Self Servicing model RuntimeBuild: 02282008 RuntimeVersion: 2.5.0.0

Thanks

Simon Nicholson

What's the database DDL for company.CompanyID? Is it Identity column or uniqueIdentifier?

Would you please provide a simple northwind repro solution?

Thanks.

Walaa wrote:

What's the database DDL for company.CompanyID? Is it Identity column or uniqueIdentifier?

Would you please provide a simple northwind repro solution?

Thanks.

Hi,

Company.CompanyID is an identity field. I'll look into replicating the problem against Northwind.

CREATE TABLE [dbo].[Companies](
    [Company_Id] [int] IDENTITY(1,1) NOT NULL,
    [Company_Name] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [Addr1] [varchar](70) COLLATE Latin1_General_CI_AS NULL,
    [Addr2] [varchar](70) COLLATE Latin1_General_CI_AS NULL,
    [Addr3] [varchar](70) COLLATE Latin1_General_CI_AS NULL,
    [City] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
    [Postcode] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
    [State] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
    [Website] [varchar](60) COLLATE Latin1_General_CI_AS NULL,
    [Role_cd] [varchar](1) COLLATE Latin1_General_CI_AS NULL,
    [Telno1] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
    [Telno2] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
    [WebUser] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
    [WebPwd] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
    [Country_code] [char](2) COLLATE Latin1_General_CI_AS NULL,
    [Role_Id] [int] NOT NULL,
    [CrossStreet1] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
    [CrossStreet2] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
    [Location] [varchar](30) COLLATE Latin1_General_CI_AS NULL,
    [Email] [varchar](60) COLLATE Latin1_General_CI_AS NULL,
 CONSTRAINT [PK__COMPANIES__51700577] PRIMARY KEY CLUSTERED 
(
    [Company_Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Regards

Simon.

Walaa wrote:

What's the database DDL for company.CompanyID? Is it Identity column or uniqueIdentifier?

Would you please provide a simple northwind repro solution?

Thanks.

Hi Walaa,

I believe I've found the problem & a work around for it. The problem occurs because I am trying to update an existing FundCompanies entity. If I delete the existing record and create a new one then my code works fine.

The FundCompanies table consists of two fields - fundId & companyid which together form the primary key. When I try to change the record to point at a new company I am changing part of the primary key which I guess isn't allowed?

I can replicate this in Northwind as well using the Employees / EmployeeTerritories & Territories tables as well.

Regards

Simon Nicholson

The FundCompanies table consists of two fields - fundId & companyid which together form the primary key. When I try to change the record to point at a new company I am changing part of the primary key which I guess isn't allowed?

This is it. This is not allowed.

So you have 2 options:

1- Delete the record in the intermediate table and re-create it with the new PK values.

2- Use UpdateMulti() with the appropriate filter.