This is my first LLBLGen Pro project so I may be missing something obvious. I have some code which is translating objects from outside my system into objects for storage into the database. The original version of the code through an exception which was due to an invalid INSERT statement being generated by LLBLGenPro. I changed the way I was creating the new object and the error went away and the generated INSERT was correct.

I don't understand why.

LLBLGen Pro Designer: 2.6 Final Runtime version: 2.6.9.116

The table structure is simple:

Group (ID bigint IDENTITY(1,1) NOT NULL PRIMARY KEY, NAME varchar(250), etc...) 
USER (ID bigint IDENTITY(1,1) NOT NULL PRIMARY KEY, NAME varchar(250), etc...) 
GroupMap (GROUP_ID bigint, MEMBER_ID bigint)
  -- GROUP_ID, MEMBER_ID make the PK
  -- both columns are FKs

-- Original code along with generated queries and exception

foreach (AuthGroup group in config.Groups) {
   GroupEntity groupEnt = new GroupEntity(group.Name);

   if (groupEnt.IsNew)
      groupEnt = new GroupEntity(group);

   UserCollection groupUsers = groupEnt.UserCollectionViaGroupMap;

   foreach (TLUser user in group.Members) {
      UserEntity userEnt = new UserEntity(user.NTUser);

      if (userEnt.IsNew)
         userEnt = new UserEntity(user);

      if (groupUsers.Count == 0 || !groupUsers.Contains(userEnt)) {
         groupEnt.UserCollectionViaGroupMap.Add(userEnt);
         GroupMapEntity map = new GroupMapEntity(groupEnt.Id, userEnt.Id);
     groupEnt.GroupMap.Add(map); 
      }
   }

   groupEnt.Save();
   groupEnt.UserCollectionViaGroupMap.SaveMulti();
   groupEnt.GroupMap.SaveMulti();
}

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [REPORT].[dbo].[GROUP] ([NAME], [ADMIN], [FILTER], [EVENTS]) VALUES (@Name, @Admin, @Filter, @Events);SELECT @Id=SCOPE_IDENTITY() Parameter: @Id : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Output. Value: <undefined value>. Parameter: @Name : String. Length: 256. Precision: 0. Scale: 0. Direction: Input. Value: "cxreveal admin". Parameter: @Admin : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 0. Parameter: @Filter : AnsiString. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: "0". Parameter: @Events : AnsiString. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: "".

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [REPORT].[dbo].[USER] ([NAME], [PASSWORD], [ADMIN], [DOMAINS]) VALUES (@Name, @Password, @Admin, @Domains);SELECT @Id=SCOPE_IDENTITY() Parameter: @Id : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Output. Value: <undefined value>. Parameter: @Name : String. Length: 256. Precision: 0. Scale: 0. Direction: Input. Value: "revealadmin". Parameter: @Password : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "init". Parameter: @Admin : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 0. Parameter: @Domains : String. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: "Tealeaf_Portal".

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [REPORT].[dbo].[GROUP_MAP] ([GROUP_ID]) VALUES (@GroupId) Parameter: @GroupId : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Input. Value: 56.

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll

-- Working code and the generated queries

foreach (AuthGroup group in config.Groups) {
   GroupEntity groupEnt = new GroupEntity(group.Name);

   if (groupEnt.IsNew)
      groupEnt = new GroupEntity(group);

   UserCollection groupUsers = groupEnt.UserCollectionViaGroupMap;

   foreach (TLUser user in group.Members) {
      UserEntity userEnt = new UserEntity(user.NTUser);

      if (userEnt.IsNew)
         userEnt = new UserEntity(user);

      if (groupUsers.Count == 0 || !groupUsers.Contains(userEnt)) {
         groupEnt.UserCollectionViaGroupMap.Add(userEnt);
         GroupMapEntity map = new TlGroupMapEntity();
         map.Group = groupEnt;
         map.User = userEnt;
      }
   }

   groupEnt.Save();
   groupEnt.UserCollectionViaGroupMap.SaveMulti();
   groupEnt.GroupMap.SaveMulti();
}

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [REPORT].[dbo].[GROUP] ([NAME], [ADMIN], [FILTER], [EVENTS]) VALUES (@Name, @Admin, @Filter, @Events);SELECT @Id=SCOPE_IDENTITY() Parameter: @Id : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Output. Value: <undefined value>. Parameter: @Name : String. Length: 256. Precision: 0. Scale: 0. Direction: Input. Value: "cxreveal admin". Parameter: @Admin : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 0. Parameter: @Filter : AnsiString. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: "0". Parameter: @Events : AnsiString. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: "".

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [REPORT].[dbo].[USER] ([NAME], [PASSWORD], [ADMIN], [DOMAINS]) VALUES (@Name, @Password, @Admin, @Domains);SELECT @Id=SCOPE_IDENTITY() Parameter: @Id : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Output. Value: <undefined value>. Parameter: @Name : String. Length: 256. Precision: 0. Scale: 0. Direction: Input. Value: "revealadmin". Parameter: @Password : AnsiStringFixedLength. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "init". Parameter: @Admin : Byte. Length: 1. Precision: 3. Scale: 0. Direction: Input. Value: 0. Parameter: @Domains : String. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: "Tealeaf_Portal".

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [REPORT].[dbo].[GROUP_MAP] ([GROUP_ID], [MEMBER_ID]) VALUES (@GroupId, @MemberId) Parameter: @GroupId : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Input. Value: 57. Parameter: @MemberId : Int64. Length: 0. Precision: 19. Scale: 0. Direction: Input. Value: 41.

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ

The only difference is that I changed these two lines

GroupMapEntity map = new GroupMapEntity(groupEnt.Id, userEnt.Id);
groupEnt.GroupMap.Add(map); 

into these lines

GroupMapEntity map = new TlGroupMapEntity();
map.Group = groupEnt;
map.User = userEnt;

It seems like it should work the same either way. Can someone explain why it doesn't?

Thanks.

kfranzen wrote:

The only difference is that I changed these two lines

GroupMapEntity map = new GroupMapEntity(groupEnt.Id, userEnt.Id);
groupEnt.GroupMap.Add(map);

into these lines

GroupMapEntity map = new TlGroupMapEntity();
map.Group = groupEnt;
map.User = userEnt;

It seems like it should work the same either way. Can someone explain why it doesn't?

Hi Kirk, when you set only the primary key fields (groupEnt.Id and userEnt.Id) the new entity (map) is not connected to the others (groupEnt and userEnt). So, when you perform the groupEnt.GroupMap.SaveMulti(); no entities are present in the _GroupMap _collection.

At the second code snippet you indeed are using the PK/FK syncrhonization.

Another thoughts about your code:

UserEntity userEnt = new UserEntity(user.NTUser);

     if (userEnt.IsNew)
         userEnt = new UserEntity(user);

Here userEnt.IsNew will always be true, due to the userEnt = new ....

groupEnt.Save();
groupEnt.UserCollectionViaGroupMap.SaveMulti();
groupEnt.GroupMap.SaveMulti();

Here you can use recursive save. This way you ensure an atomic save for all the related entities:

groupEnt.Save(true);

You also could use a transaction and include the saves there.

Hope that helps

Hi Kirk, when you set only the primary key fields (groupEnt.Id and userEnt.Id) the new entity (map) is not connected to the others (groupEnt and userEnt). So, when you perform the groupEnt.GroupMap.SaveMulti(); no entities are present in the GroupMap collection.

At the second code snippet you indeed are using the PK/FK syncrhonization.

That makes sense to me. However, when I inspect the GroupMap object after building it with the primary key constructor GroupMap(int, int) the Group and User members are not null. It looks as though the underlying code has correctly looked them up?

Thanks for the other info.

That makes sense to me. However, when I inspect the GroupMap object after building it with the primary key constructor GroupMap(int, int) the Group and User members are not null. It looks as though the underlying code has correctly looked them up?

It might be that when you inspect the properties, lazy loading trigger queries and show the objects. Please see the generated code before and after you inspect those objects. Remember that you are using the lazy-loading SelfServicing templateSet.