I have been developing a project using LLBLGEN for 6 months now, and I have to admit, that I was unprepared with a lot of the concepts required to be effective with it.
Summary: My current question is in regard to setting up relations. I needed to create a new entity off of a new table and work with it on the front end. However whenever I set up the relations, using FK guid's to link them, I kept getting a 1:n relationship, and could find no way of editing the relationship to 1:1. Because of this, I could not get a clean "entity", but instead could only retrieve a collection of entities. This has created a number of issues with the middle to front end code. After wrestling with these for a few days, I hit upon a simple solution. I copied a table that had a similar link, but had the desired 1:1 relationship, replaced the table definition with the desired table, Refreshed the Catalog, and Viola! The question is why?

Details Table Account has a uniqueidentifier, accountID that most tables relate to. The table that creates a 1:n relationship is scripted as follows: USE [LITS] GO

/****** Object: Table [dbo].[PostTreatment] Script Date: 09/30/2010 12:56:24 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[PostTreatment]( [PostTreatmentId] [uniqueidentifier] NOT NULL, [AccountId] [uniqueidentifier] NOT NULL, [CreatedBy] [uniqueidentifier] NULL, [DateTimeCreated] [datetime] NOT NULL, [EditedBy] [uniqueidentifier] NULL, [DateTimeEdited] [datetime] NULL, [ReturnCode] [numeric](1, 0) NULL, [Start] [datetime] NULL, [End] [datetime] NULL, [Code] [uniqueidentifier] NULL, [Description] [uniqueidentifier] NULL, [Active] [bit] NULL, CONSTRAINT [PK_PostTreatment] PRIMARY KEY NONCLUSTERED ( [PostTreatmentId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [SECONDARY] ) ON [SECONDARY]

GO

ALTER TABLE [dbo].[PostTreatment] WITH NOCHECK ADD CONSTRAINT [FK_PostTreatment_Account] FOREIGN KEY([AccountId]) REFERENCES [dbo].[Account] ([AccountId]) GO

ALTER TABLE [dbo].[PostTreatment] NOCHECK CONSTRAINT [FK_PostTreatment_Account] GO

ALTER TABLE [dbo].[PostTreatment] ADD CONSTRAINT [DF_PostTreatment_PostTreatmentId] DEFAULT (newsequentialid()) FOR [PostTreatmentId] GO

ALTER TABLE [dbo].[PostTreatment] ADD CONSTRAINT [DF_PostTreatment_DateTimeCreated] DEFAULT (getdate()) FOR [DateTimeCreated] GO

The table that has a 1:1 relationship is scripted as follows: USE [LITSDev] GO /****** Object: Table [dbo].[PostTreatment] Script Date: 09/30/2010 12:12:25 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[PostTreatment]( [PostTreatmentId] [uniqueidentifier] NOT NULL, [AccountId] [uniqueidentifier] NOT NULL, [CreatedBy] [uniqueidentifier] NULL, [DateTimeCreated] [datetime] NOT NULL, [EditedBy] [uniqueidentifier] NULL, [DateTimeEdited] [datetime] NULL, [ReturnCode] [numeric](1, 0) NULL, [Start] [datetime] NULL, [End] [datetime] NULL, [Code] [uniqueidentifier] NULL, [Description] [uniqueidentifier] NULL, [Active] [bit] NULL, CONSTRAINT [PK_PostTreatment] PRIMARY KEY NONCLUSTERED ( [PostTreatmentId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY], CONSTRAINT [IX_PostTreatment_AuditId] UNIQUE NONCLUSTERED ( [AccountId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY] ) ON [SECONDARY]

GO

ALTER TABLE [dbo].[PostTreatment] WITH NOCHECK ADD CONSTRAINT [FK_PostTreatment_Account] FOREIGN KEY([AccountId]) REFERENCES [dbo].[Account] ([AccountId]) GO

ALTER TABLE [dbo].[PostTreatment] CHECK CONSTRAINT [FK_PostTreatment_Account] GO

ALTER TABLE [dbo].[PostTreatment] ADD CONSTRAINT [DF_PostTreatment_PostTreatmentId] DEFAULT (newsequentialid()) FOR [PostTreatmentId] GO

ALTER TABLE [dbo].[PostTreatment] ADD CONSTRAINT [DF_PostTreatment_DateTimeCreated] DEFAULT (getdate()) FOR [DateTimeCreated] GO

I ran these two scripts through a compare program, and had two differences The 1:1 file seems to have a "CONSTRAINT [IX_PostTreatment_AuditId] UNIQUE NONCLUSTERED "

and the FK_PostTreatment_Account is constrained in one script vice the other.

Is this it? I have spent 3 days off and on wrestling with this issue. I finnaly found a reference stating that the relationship 1:1 1:n is auto defined and is not editable. There must be a reason for that. What is it.

I am amazed at all the things your code does, but between WPF, a View Model Architecture, and LLBLGEN, it has been a very steep learning curve.

P

Sorry, after the length of that post I still wasn't entirely sure what you were asking Could you clarify exactly what your question is for us ?

Cheers

Matt

Sorry, I was following your guide on information required for submission, which had 10 parts to it.

I am asking why I can't select the relationship of two tables (1:1 vice 1:n) using LLBLGen. Instead of going through the gyration of creating a new table out of whole cloth to get the desired relationship.

Also, what is different between these nearly identical database tables that they parsed differently in LLBLGen. One with a 1:1 relationship to its parent table, the other 1:n.

Thanks for your prompt response. P

Hi Phil,

LLBLGen just follow the logical concept of relations. For instance, in your example, if TableA doesn't have any UniqueConstraint in the TableA.AccountId FK, then the logical relation's type is 1:m, as you can have many accounts in TableA.

Now if you have defined a UniqueConstraint in TableB.AccountId FK, then you can have only one record with specific AccountId. That means that for each Account, you can only have one TableB record, so the logical relation is 1:1. If you would allowed to modify this, you could have runtime and database errors.

You can also stay that the relationships wont be created from DB relations. So you can add relationships by yourself in LLBLGen Designer (more info..). That, however respects the same rules. For instance, you can't define a 1:1 relation between two entities if the FK side doesn't have a Unique Constraint in the FK field.