LLBLGen Prop Version + buildnr = Version 2.6 Final(April 15th, 2009) Template = Adapter .NET Version = 2.0

I have a question about Primary Key/Foreign Key synchronization.

We have two tables: ApplicationType and ApplicationFieldSchema. We actually have two relationships between the tables.

One relationship is a 1-to-many relationship between ApplicationType and ApplicationFieldSchema. Primary Key is ApplicationType.ID Foreign Key is ApplicationFieldSchema.ApplicationTypeID I'm not having trouble with that relationship.

The other relationship is a 1-to-1 relationship between ApplicationType and ApplicationFieldSchema. Primary Key is ApplicationFieldSchema.ApplicationTypeID, ApplicationFieldSchema.SchemaVersion Foreign Key is ApplicationType.ID, ApplicationType.FieldSchemaVersion. This is the relationship I'm having trouble with.

If ApplicationFieldSchema.ForApplication = 1 then we use the 1-to-many relationship. If ApplicationFieldSchema.ForApplication = 0 then we use the 1-to-1 relationship.

Here is the SQL for ApplicationType:

[code] CREATE TABLE [PT].[ApplicationType]( [ID] [int] IDENTITY(1,1) NOT NULL, [DepartmentID] [int] NOT NULL, [ApplicationTypeCode] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

 [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Description] [varchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [IssuePrefix] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CurrentSchemaVersion] [int] NULL,
    [NextIssueNumber] [int] NULL,
    [Deleted] [dbo].[Deleted] NOT NULL,
    [ReportExecutable] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DefaultExpireDays] [int] NULL,
    [UpdateByID] [int] NULL,
    [CreateByID] [int] NULL,
    [UpdateTime] [dbo].[UpdateTime] NULL,
    [CreateTime] [dbo].[CreateTime] NULL,
    [RowVersion] [timestamp] NULL,
    [FieldSchemaVersion] [int] NULL,
 CONSTRAINT [PK_APPLICATIONTYPE] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [PT].[ApplicationType]  WITH CHECK ADD  CONSTRAINT [FK_ApplicationType_ApplicationFieldSchema] FOREIGN KEY([ID], [FieldSchemaVersion])
REFERENCES [PT].[ApplicationFieldSchema] ([ApplicationTypeID], [SchemaVersion])

Here is the SQL for ApplicationFieldSchema

CREATE TABLE [PT].[ApplicationFieldSchema](
    [ApplicationTypeID] [int] NOT NULL,
    [SchemaVersion] [int] NOT NULL,
    [Description] [varchar](1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [version] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [UpdateByID] [int] NULL,
    [CreateByID] [int] NULL,
    [UpdateTime] [dbo].[UpdateTime] NULL,
    [CreateTime] [dbo].[CreateTime] NULL,
    [RowVersion] [timestamp] NULL,
    [ForApplication] [bit] NOT NULL,
 CONSTRAINT [PK_APPLICATIONFIELDSCHEMA] PRIMARY KEY CLUSTERED 
(
    [ApplicationTypeID] ASC,
    [SchemaVersion] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [PT].[ApplicationFieldSchema]  WITH CHECK ADD  CONSTRAINT [FK_ApplicationFieldSchema_FIELDSCHE_TIONTYPE] FOREIGN KEY([ApplicationTypeID])
REFERENCES [PT].[ApplicationType] ([ID])

Here is some code. _fieldSchema is of type ApplicationFieldSchemaEntity. appType is of type ApplicationEntity

_fieldSchema = new ApplicationFieldSchemaEntity();
_fieldSchema.ForApplication = false;
_fieldSchema.ApplicationType = appType;
_fieldSchema.SchemaVersion = nextSchemaVersion;
_fieldSchema.Description = "Automatically generated for an Application Type.";
_fieldSchema.Version = nextSchemaVersion.ToString();

appType.FieldSchemaVersion = nextSchemaVersion;
appType.ApplicationFieldSchema_ = _fieldSchema;

Here is the LLBLGen declaration of ApplicationTypeEntity.ApplicationFieldSchema_

//
// Summary:
//   Gets / sets related entity of type 'ApplicationFieldSchemaEntity' which has
//   to be set using a fetch action earlier. If no related entity is set for this
//   property, null is returned. This property is not visible in databound grids.
[Browsable(false)]
public virtual ApplicationFieldSchemaEntity ApplicationFieldSchema_ { get; set; }

Later on when the the ApplicationTypeEntity is saved using this version of DataAccessAdapter.SaveEntity() where refetchAfterSave is true, updateRestriction is null, and recurds is true.

public virtual bool SaveEntity(IEntity2 entityToSave, bool refetchAfterSave, IPredicateExpression updateRestriction, bool recurse);

But I get this error:

An exception was caught during the execution of an action query: Cannot insert the value NULL into column 'ApplicationTypeID', table 'GCSLRMS_Grant_DB29.PT.ApplicationFieldSchema'; column does not allow nulls. INSERT fails

I thought that when you set a reference to another object that the foreign key and primary key fields were synchronized. I wonder if the problem is due to the other relationship between the two tables described earlier.

Any help would be appreciated. Thanks!

Hi. I have some questions:

1. Please recheck the "Field on Relations" on LLBLGen Designer, to see the names of the fields that map the relations. I ask this because: someApplicationType.ApplicationFieldSchema is an ApplicationFieldSchemaEntity and someApplicationType.ApplicationFieldSchema is an ApplicationFieldSchemaEntity as well_ so maybe you are mixing up these two.

2. Please debug your lines and check the field values at runtime, then post the info. Something like:

_fieldSchema = new ApplicationFieldSchemaEntity();

new appblicationFieldSchema is created

_fieldSchema.ForApplication = false;

forApplication = 0. Use the 1-1 rel

_fieldSchema.ApplicationType = appType;

is this the 1:1 rel? if it is, and appType is new, then PK/FK goes here. the field values should be: _fieldSchema.ApplicationTypeId : NULL _fieldSchema.SchemaVersion : NULL appType.Id : NULL appType.SchemaVersion : NULL

_fieldSchema.SchemaVersion = nextSchemaVersion

part of the PK, now: _fieldSchema.ApplicationTypeId : NULL _fieldSchema.SchemaVersion : nextSchemaVersion appType.Id : NULL appType.SchemaVersion : NULL

...
appType.FieldSchemaVersion = nextSchemaVersion

 _fieldSchema.ApplicationTypeId : NULL
 _fieldSchema.SchemaVersion : nextSchemaVersion
 appType.Id : NULL
 appType.SchemaVersion : nextSchemaVersion

appType.ApplicationFieldSchema_ = _fieldSchema;

m:1 rel. This make PK/FK sync. At this point the values should be: _fieldSchema.ApplicationTypeId : NULL (here is the PK of this relation, keep unmodified) _fieldSchema.SchemaVersion : nextSchemaVersion (here is the PK of this relation, keep unmodified) appType.Id : NULL (the value from _fieldSchema.ApplicationTypeId) appType.SchemaVersion : nextSchemaVersion (the value from _fieldSchema.SchemaVersion)

1. Now, the save comes, are you doing a recursive save? Does the appType already exists or is new?

daelmo wrote:

Hi. I have some questions:

Here are the answers to your questions. But I know what the problem is now. I'll describe it in my next post. So you may just want to ignore this post.

daelmo wrote:

1. Please recheck the "Field on Relations" on LLBLGen Designer, to see the names of the fields that map the relations. I ask this because: someApplicationType.ApplicationFieldSchema is an ApplicationFieldSchemaEntity and someApplicationType.ApplicationFieldSchema is an ApplicationFieldSchemaEntity as well_ so maybe you are mixing up these two.

Here are the relations in the LLBLGen designer. It is the second one I'm having problems with.

FieldName                       Relation
ApplicationFieldSchema  ApplicationType - ApplicationFieldSchema(1:n)
ApplicationFieldSchema_  ApplicationType - ApplicationFieldSchema( m:1)

Description:
Returns one instance of the entity 'ApplicationFieldSchema' which are directly related to the instance of the entity 'ApplicationType' where ApplicationType.Id=ApplicationFieldSchema.ApplicationTypeId AND ApplicationType.FieldSchemaVersion=ApplicationFieldSchema.SchemaVersion

daelmo wrote:

1. Please debug your lines and check the field values at runtime, then post the info.

_fieldSchema = new ApplicationFieldSchemaEntity();
_fieldSchema.ForApplication = false;
_fieldSchema.ApplicationType = appType;

_fieldSchema.ApplicationTypeId : 0 _fieldSchema.SchemaVersion : 0 appType.Id : 0 appType.FieldSchemaVersion : null

_fieldSchema.SchemaVersion = nextSchemaVersion;

_fieldSchema.ApplicationTypeId : 0 _fieldSchema.SchemaVersion : 1 appType.Id : 0 appType.FieldSchemaVersion : null

_fieldSchema.Description = "Automatically generated for an Application Type.";
_fieldSchema.Version = nextSchemaVersion.ToString();
appType.FieldSchemaVersion = nextSchemaVersion;

_fieldSchema.ApplicationTypeId : 0 _fieldSchema.SchemaVersion : 1 appType.Id : 0 appType.FieldSchemaVersion : 1

appType.ApplicationFieldSchema_ = _fieldSchema;

_fieldSchema.ApplicationTypeId : 0 _fieldSchema.SchemaVersion : 1 appType.Id : 0 appType.FieldSchemaVersion : 1

daelmo wrote:

1. Now, the save comes, are you doing a recursive save? Does the appType already exists or is new?

We're doing a recursive save. The appType is new, it doesn't exist in the database yet.

I no longer think I have a Primary Key / Foreign Key synchronization problem.

My problem seems to be with the fact I have two relationships between ApplicationType and ApplicationFieldSchema. I think the problem may be bad database design. I didn't design this database or write this code, so I don't really know why it was designed the way it was.

The program I'm working on is a program that tracks Permit Applications (e.g. building permits, sanitary permits, etc.).

ApplicationType represents a type of application. Let's say we have a building permit. We'll have an ApplicationType called Building Permit. The actual Building Permit application that a person fills out has fields for certain information. But each customer of our permit tracking application may have different fields on their building permits. So we allow them to define the fields of their Building Permit application. We store that information in a table called ApplicationFieldSchema.

Since the building permit application fields may change over time, we allow them to add a new ApplicationFieldSchema while retaining any old ApplicationFieldSchemas. So that is why we have a 1-to-many relationship between ApplicationType and ApplicationFieldSchema where ApplicationType.ID = ApplicationFieldSchema.ApplicationTypeID.

Here is the associated LLBLGen code representing this relationship: ApplicationType.ApplicationFieldSchema: ApplicationType has a collection of ApplicationFieldSchema. ApplicationFieldSchema.ApplicationType: ApplicationFieldSchema holds the ApplicationType object it depends on.

This stuff works fine.

The ApplicationFieldSchema information above was used for specifying the fields used on an application of a certain application type. ApplicationType itself has some fields like Name, Next Issue Number, etc. But later we decided that maybe we should allow customers to store extra information about ApplicationType (not information about the fields on application form). So it appears we tried using the ApplicationFieldSchema table for that too.

For these extra ApplicationType fields, there should be a 1-to-1 relationship between ApplicationType and ApplicationFieldSchema. We added a ApplicationFieldSchema.ForApplication field to distinguish between an ApplicationFieldSchema record used for storing application form fields (ApplicationFieldSchema.ForApplication = true) and an ApplicationFieldSchema record used for storing extra ApplicationType record fields (ApplicationFieldSchema.ForApplication = false). So for any given ApplicationType, there really should only be one ApplicationFieldSchema record at most whose ForApplication field is false.

Now here is the part I question. We created a relationship where ApplicationFieldSchema is the primary key table and ApplicationType is the foreign key table:

ApplicationFieldSchema.ApplicationTypeID = ApplicationType.ID ApplicationFieldSchema.SchemaVersion = ApplicationType.FieldSchemaVersion.

Here is the associated LLBLGen code representing this relationship: ApplicationType.ApplicationFieldSchema: ApplicationType holds the ApplicationFieldSchema object it depends on. ApplicationFieldSchema.ApplicationType: ApplicationFieldSchema holds a collection of ApplicationType objects that depend on it. But in practice, it should only be one.

So now let's go through the code I posted earlier for the situation where we are creating an ApplicationFieldSchema record for the purpose of adding extra fields to an ApplicationType.

_fieldSchema = new ApplicationFieldSchemaEntity();
_fieldSchema.ForApplication = false;
_fieldSchema.ApplicationType = appType;
_fieldSchema.SchemaVersion = nextSchemaVersion;
_fieldSchema.Description = "Automatically generated for an Application Type.";
_fieldSchema.Version = nextSchemaVersion.ToString();
appType.FieldSchemaVersion = nextSchemaVersion;
appType.ApplicationFieldSchema_ = _fieldSchema;

We create a new ApplicationFieldSchema entity. Later we specify the ApplicationType it is connected to. I don't know why we do that in this situation. Later we set the ApplicationTypeEntity.ApplicationFieldSchema_ object to the ApplicationFieldSchema we just created.

**So here is the crux of the problem: Since ApplicationType is now dependent on ApplicationFieldSchema, during the save process, LLBLGen will try to insert an ApplicationFieldSchema record BEFORE adding an ApplicationType record. But that won't work, because ApplicationFieldSchema.ApplicationTypeID is a foreign key to ApplicationType.ID which is an identity field that doesn't get assigned until the database assigns it when adding an ApplicationType. **

The scenario where we are creating an ApplicationFieldSchema record for the purpose of defining fields used on applications of a certain application type, we don't have any problems because ApplicationType does not depend on ApplicationFieldSchema. So ApplicationType is added first and ApplicationFieldSchema is added later. In case you're interested, here is the code for that situation:

_fieldSchema = new ApplicationFieldSchemaEntity();
_fieldSchema.ForApplication = true;
_fieldSchema.ApplicationType = appType;
_fieldSchema.SchemaVersion = nextSchemaVersion;
_fieldSchema.Description = schemaDescription;
_fieldSchema.Version = version;
appType.CurrentSchemaVersion = nextSchemaVersion;

// replace any existing schemas with this new one
appType.ApplicationFieldSchema.Clear();
appType.ApplicationFieldSchema.Add(_fieldSchema);

We create a new ApplicationFieldSchema entity. Later we specify the ApplicationType it is connected to. Later we add the new ApplicationFIeldSchemaEntity to the collection associated with ApplicationTypeEntity.

My inclination would be to get rid of the 1-to-1 relationship between ApplicationType and ApplicationFieldSchema that is used for storing extra AppliationType fields. I suppose our program code could enforce the rule that there should only be one ApplicationFieldSchema per ApplicationType whose ForApplication field is false. What are your thoughts?

My inclination would be to get rid of the 1-to-1 relationship between ApplicationType and ApplicationFieldSchema that is used for storing extra AppliationType fields. I suppose our program code could enforce the rule that there should only be one ApplicationFieldSchema per ApplicationType whose ForApplication field is false.

That would be great. Is the best solution, in my opinion.

If your DBA coudln't bake such change, you could save the entiies separately (first fieldSchema, then appType).