If I have two tables, ProductVersion and FrameVersion, and I have a one to one relationship between the two tables. The table FrameVersion then relates to FrameColor and FrameSize which each should return collections for the colors and sizes.

Why is the property product.ProductVersion returning a collection? Since there is a one to one relation I expected that the ProductVersion property should return and single entity. Is it possible to get the one to one relationship property to return only a single entity?

The code that I'm using the get the FrameVersion currently looks like this.

FrameVersionEntity frameVersion = (FrameVersionEntity)productVersion.FrameVersion[0];

I would like to be able to do this instead.

FrameVersionEntity frameVersion = (FrameVersionEntity)productVersion.FrameVersion;

Here is the DDL:

CREATE TABLE [ProductVersion] ( 
    [ProductVersionID] int identity(1,1)  NOT NULL,
    [ProductID] int NOT NULL,
    [ActiveState] char(1) DEFAULT ('3') NOT NULL,
    [DistributorID] int NOT NULL,
    [DistributorNumber] varchar(30) DEFAULT ('') NOT NULL
) 
GO

CREATE TABLE [FrameVersion] ( 
    [ProductVersionID] int NOT NULL,
    [FrameSizeID] int NOT NULL,
    [FrameColorID] int NOT NULL
) 
GO

CREATE TABLE [FrameSize] ( 
    [FrameSizeID] int identity(1,1)  NOT NULL,
    [FrameID] int NOT NULL,
    [FrameSize] decimal(4,2) DEFAULT (0) NOT NULL
) 
GO

CREATE TABLE [FrameColor] ( 
    [FrameColorID] int identity(1,1)  NOT NULL,
    [FrameID] int NOT NULL,
    [Color] varchar(50) DEFAULT ('') NOT NULL
) 
GO

ALTER TABLE [ProductVersion] ADD CONSTRAINT [PK_ProductVersion] 
PRIMARY KEY NONCLUSTERED ([ProductVersionID]) 
GO

ALTER TABLE [FrameColor] ADD CONSTRAINT [PK_FrameColor] 
PRIMARY KEY NONCLUSTERED ([FrameColorID]) 
GO

ALTER TABLE [FrameSize] ADD CONSTRAINT [PK_FrameSize] 
PRIMARY KEY NONCLUSTERED ([FrameSizeID]) 
GO

ALTER TABLE [FrameVersion] ADD CONSTRAINT [PK_FrameVersion] 
PRIMARY KEY ([ProductVersionID], [FrameSizeID], [FrameColorID]) 
GO

ALTER TABLE [FrameVersion] ADD CONSTRAINT [FK_FrameVersion_FrameColor] 
FOREIGN KEY ([FrameColorID]) REFERENCES [FrameColor] ([FrameColorID])
GO

ALTER TABLE [FrameVersion] ADD CONSTRAINT [FK_FrameVersion_FrameSize] 
FOREIGN KEY ([FrameSizeID]) REFERENCES [FrameSize] ([FrameSizeID])
GO

ALTER TABLE [FrameVersion] ADD CONSTRAINT [FK_FrameVersion_ProductVersion] 
FOREIGN KEY ([ProductVersionID]) REFERENCES [ProductVersion] ([ProductVersionID])
GO

Is the problem that I have a composite primary key on the three columns in FrameVersion?

Regards,

Aaron

tprohas wrote:

Why is the property product.ProductVersion returning a collection? Since there is a one to one relation I expected that the ProductVersion property should return and single entity. Is it possible to get the one to one relationship property to return only a single entity?

I think you need to add a Unique Constraint to FrameVersion.ProductVersionID

ALTER TABLE [FrameVersion] ADD CONSTRAINT [UC_FrameVersion_ProductVersionID]
UNIQUE  NONCLUSTERED ( [ProductVersionID]) 
GO

Marcus

Correct, thanks Marcus

Marcus wrote:

tprohas wrote:

Why is the property product.ProductVersion returning a collection? Since there is a one to one relation I expected that the ProductVersion property should return and single entity. Is it possible to get the one to one relationship property to return only a single entity?

I think you need to add a Unique Constraint to FrameVersion.ProductVersionID

ALTER TABLE [FrameVersion] ADD CONSTRAINT [UC_FrameVersion_ProductVersionID]
UNIQUE  NONCLUSTERED ( [ProductVersionID]) 
GO

Marcus

Marcus,

Thank you very much, this now works.

Aaron

I just realized something in the process of learning that I have to add the unique constraint to the ProductVersionID.

I now have to add this unique constraint to the system in a lot of tables to make the generated code work the way I want. Is this normal to have to change the schema of my database to get the correct code generated?

Why doesn't the generator know that the ProductVersionID is unique by the fact that ProductVersionID is a primary key column?

ALTER TABLE [FrameVersion] ADD CONSTRAINT [PK_FrameVersion] PRIMARY KEY ([ProductVersionID], [FrameSizeID], [FrameColorID]) GO

Since ProductVersionID is part of a composite primary key it can actually have the same value across several records when combined with different values in the other primary key columns. I'm guessing the generator realizes that fact and set it up as a collection. When you added the unique constraint to allow only unique values in that column for every record, then the generator knew it could use an entity instead of a collection.

In addition, it is good practice to add Unique Constraints to columns that are going to be unique. This provides guaranteed integrity and gives SQL Server hints when performing query optimizations.

You will also notice if you create a Database Diagram that the symbol on the relationship (on the FrameVersion side) before adding the unique constraint is infinity, while after it is a key. Now SQL Server and LLBLGen know that this column is indeed unique!