Inserting into tables w/ FK relationship fails inside Migrations.cs

Topics: General, Troubleshooting, Writing modules
Mar 1, 2012 at 10:53 PM
Edited Mar 1, 2012 at 10:55 PM

Here's the code from Migrations.cs. First I create the schema, then FK, then try to insert the data into the parent table first, and then the child table. This code works if I just do the insert into the parent table. But if I do the inserts to both tables I get FK errors, which leads me to believe Orchard or NHibernate is for some reason attempting inserts into the child table in parallel or before the parent table inserts are done. 

I tried IRepository<>.Flush() and IContentManager.Flush() after the parent table inserts but that didn't help. 

Any guidance? 

 

 

SchemaBuilder.CreateTable("ParentRecord"
    , table => table
        .Column<int>("Id", column => column.PrimaryKey().Identity())
        .Column<string>("Name", c => c.WithLength(100))
);
SchemaBuilder.CreateTable("ChildRecord",
    table => table
        .Column<int>("Id", column => column.PrimaryKey().Identity())
        .Column<int>("ParentRecord_Id")
        .Column<string>("Name", c => c.WithLength(100))
);

SchemaBuilder.CreateForeignKey(
    "FK_ChildRecord__ParentRecord"
    , "ChildRecord", new string[] { "ParentRecord_Id" }
    , "ParentRecord", new string[] { "Id" }
); 

if (_parentRepo == null) {
    throw new InvalidOperationException("Couldn't find Parent  repository.");
}
foreach (var parent in parents) {
    _parentRepo.Create(parent);
}
_parentRepo.Flush();
_contentManager.Flush(); 

if (_childrenRepo == null) {
    throw new InvalidOperationException("Couldn't find Child repository.");
}
foreach (var child in children) {
    _childrenRepo.Create(child);
}
Mar 1, 2012 at 10:59 PM

Nevermind. I see the problem. I declared Id columns as Identity instead of setting them manually in the data i'm trying to port in. It really is a FK error. 

Mar 1, 2012 at 11:10 PM

Actually, running into a new problem now that I fixed the above issue. Looks like NHibernate doesn't want to insert the value I set manually for "Id" property. See the sql statement in the error below, Id column is missing. I have already modified the migrations .cs as such: 

SchemaBuilder.CreateTable("ParentRecord"
    , table => table
        .Column<int>("Id", column => column.PrimaryKey())
        .Column<string>("Name", c => c.WithLength(100))
);
SchemaBuilder.CreateTable("ChildRecord",
    table => table
        .Column<int>("Id", column => column.PrimaryKey())
        .Column<int>("ParentRecord_Id")
        .Column<string>("Name", c => c.WithLength(100))
);

 

2012-03-01 19:02:55,248 [1] NHibernate.AdoNet.AbstractBatcher - Could not execute query: INSERT INTO orch1x_TheMonarch_ParentRecord (Name) VALUES (@p2); select SCOPE_IDENTITY()
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'Id', table 'orchard1x.dbo.orch1x_TheMonarch_ParentRecord'; column does not allow nulls. INSERT fails.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)

Developer
Mar 1, 2012 at 11:15 PM

If you want the database to generate the ID value, you should also call the Identity method:

 

SchemaBuilder.CreateTable("ParentRecord"
    , table => table
        .Column<int>("Id", column => column.PrimaryKey().Identity())
        .Column<string>("Name", c => c.WithLength(100))
);

 

Mar 1, 2012 at 11:18 PM

That's what I was doing at first (I have the .Identity() call in the OP), but I actually want to set the Id's myself. I will need to periodically tie these back to an external data source and don't want to complicate things by having to maintain diff set of mappings between the Orchard db and the original db. 

Mar 1, 2012 at 11:20 PM
// Here's the data that is being passed to the insert loop: 
IEnumerable<ParentRecord> parents = new List<ParentRecord> {
  new ParentRecord{ Id = 55, Name = "Some Parent Name" }
}

Developer
Mar 1, 2012 at 11:21 PM
Edited Mar 1, 2012 at 11:25 PM

I see. That's odd. Did you check the column settings in the DB itself? Perhaps it's still set to Identity (although you should get a different type of error I think)

Perhaps you could also post the insert loop?

Mar 1, 2012 at 11:25 PM

I just checked right now and it's not set to identity. You're right, it would be a different error. The error message in my 3rd post shows that NHibernate is for some reason not trying to insert any value for Id column. I'm not that familiar with NHibernate so not sure how to best debug this. 

Mar 1, 2012 at 11:46 PM

Seems like it's related to FluentNHibernate assuming "Id" is identity: http://stackoverflow.com/questions/2041149/can-we-stop-the-id-column-from-auto-incrementing 

Where can I edit the mapping from my Orchard module to try out this fix? 

Id(x => x.ObjectTypeId).GeneratedBy.Assigned();

Developer
Mar 1, 2012 at 11:47 PM

I can reproduce the problem. The strange thing is that even though you set the Id to a value, it is automatically set to 0. Must be NHibernate in Action. I might investigate further tomorrow.

Developer
Mar 1, 2012 at 11:54 PM
TheMonarch wrote:

Seems like it's related to FluentNHibernate assuming "Id" is identity: http://stackoverflow.com/questions/2041149/can-we-stop-the-id-column-from-auto-incrementing 

Where can I edit the mapping from my Orchard module to try out this fix? 

 

Id(x => x.ObjectTypeId).GeneratedBy.Assigned();

 


I think in AbstractDataServicesProvider (Orchard.Framework.Data.Providers).

Developer
Mar 2, 2012 at 12:06 AM

I can see that Orchard defines a so called Alteration for ContentPart records where they configure the primary key:

class ContentPartAlterationInternal<T> : IAlteration where T : ContentPartRecord {
            public void Override(object mappingObj) {
                var mapping = (AutoMapping<T>)mappingObj;

                mapping.Id(x => x.Id)
                    .GeneratedBy.Foreign("ContentItemRecord");

                mapping.HasOne(x => x.ContentItemRecord)
                    .Constrained();
            }
        }

 

I don't know enough about NHibernate, but I suspect that you would have to create your own alteration in order to configure your primary key. Alternatively, you could inherit your entity record from ContentPartRecord (but I suspect you're not going to want to do that)

Mar 2, 2012 at 4:01 AM

I'm interested to try that out, but for now I just went with "Id" columns for the PK's, an "ParentId" cols in both tables, indexed to aid the join, but w/o FK relationship. Referential integrity isn't that big a deal since this data won't be edited frequently, and possibly never through the UI. We'd know pretty quickly if the integrity were violated.

Would I just create a class that implements IAutoMappingAlteration? How does NHibernate scan and pick that up in order to run my Override code?