Table order is not considered right.

Hi,

I have installed the latest version of LS (19. Febr.).

While saving a new object I get some sql errors regarding to FK constraints.

The strange thing here is, that some of the dependend Entities are correctly inserted first, while others are not.

I attached a demo.

Regards, Sörnt

Thanks for alerting us to this. It appears to be a bug in the order in which we traverse the inheritance hierarchy and associations. We will investigate, but in the meantime, you can work around it by touching the AddInBase type before you touch any other entity type:

class Program {
  static void Main() {
    var dummy = new AddInBase();
    // rest of program as before
  }
}

Let us know if you still see a problem but this is working in our test environment.

Hi Ivan,

I am sorry, but that didn't work for me. My code path is more complex than that simple repo. Baiscally I use a builder to create the various entites:

  AddInBase addInBase = new AddInBase();

  Language deDe = getLanguageByCode("de-DE");

  Vendor vendor = CreateVendor(deDe);

  AddIn mcSHOUTCast = AddInBuilder.NewBuilder()
                                .CreatedBy(vendor)
                                .TheNameIs("mcShoutCast")
                                .TheVersionIs.MajorNumberOf(3)
                                             .MinorNumberOf(8)
                                             .RevisionNumberOf(19)
                                             .BuildNumberOf(1024)
                                             .ReleasedAtUtc(DateTime.UtcNow)
                                             .Completed()

                                .ForDescription.AddTranslation().WithText("Internet Radio für das Media Center")
                                                                .InLanguage.Of(deDe)
                                                                           .Completed()
                                                                .Completed()
                                               .Completed()

                                .TheLogo.Large.IsLocatedAt(new Uri("http://www.mcetools.de/Portals/0/Web_Logo.png"))
                                              .Completed()
                                        .Small.IsLocatedAt(new Uri("http://www.mcetools.de/Portals/0/Web_Logo.png"))
                                              .Completed()
                                        .Completed()


                                .AddDownloadInformation().For(SupportedOperatingSystem.W764Bit)
                                                         .IsLocatedAt(new Uri("http://www.mcetools.de/portals/0/downloads/mceShoutCast/mcShoutCast_64Bit_3_8_19.msi"))
                                                         .Completed()

                                .AddDownloadInformation().For(SupportedOperatingSystem.Vista32Bit)
                                                         .IsLocatedAt(new Uri("http://www.mcetools.de/portals/0/downloads/mceShoutCast/mcShoutCast_32Bit_3_8_19.msi"))
                                                         .Completed()
                                .BuildComponent();


  _addInRepository.AddOrUpdateAndCommit(mcSHOUTCast);

At program start I created dummy instances of all base classes as you suggested.

class Program { static void Main(string[] args) {

  AddInBase addInBase = new AddInBase();
  UserBase userBase = new UserBase();

  IocBootstrapper.InitializeIoC();

  // Update DB if needed
  IDatabaseSchemaMigrator schemaMigrator = ServiceLocator.Current.Resolve<IDatabaseSchemaMigrator>();
  schemaMigrator.CreateOrUpdateDatabase();


  // create some Langauges
  LanguagesFactory languagesFactory = ServiceLocator.Current.Resolve<LanguagesFactory>();
  languagesFactory.DeleteAll();
  languagesFactory.CreateItem();

  // create a AppStoreAddIn item
  AppStoreAddInFactory appStoreAddInFactory = ServiceLocator.Current.Resolve<AppStoreAddInFactory>();
  appStoreAddInFactory.DeleteAll();
  appStoreAddInFactory.CreateItem();


}

No luck, I get still the sql exceptions.

Sörnt

Hi Sörnt,

I have recently made some changes relating to a separate issue which we were investigating which would appear to also correct this issue and these will be available in the next nightly build for you to test. After testing your repro sample with a local build here the issue appeared to be resolved, however I notice you have reported that the workaround suggested by Ivan did not resolve the issue (rather just the repro), so if you continue to have issues with the new build you can switch on an additional set of new behaviors which may resolve the issues you are seeing.

You can enable these by setting a CompatibilityOption on the LightSpeedContext instance associated with your UnitOfWork.

e.g.

context.CompatibilityOptions |= LightSpeedCompatibilityOptions.EnableCandidateFixes;

If you are able to review how the use of this alters the behavior in your actual system that would be much appreciated, this resolves the issue on the repro sample you sent through but I suspect you will have a much more varied set of actual cases in reality. Also if you notice any regressions after switching this on please let me know.

Jeremy

Hi Jeremy,

I installed the version from 23. Feb. and set the LightSpeedCompatibilityOptions.EnableCandidateFixes flag. Still no luck. That doesn't also work at the demo project!? Are you sure that your changes are within the nightly build?

Is there something I can do to support you in your efforts?

Another thing: Can you please update the version nummer of the msi file which still shows up 4.0.0? The LightSpeed Assembly versions shows up 4.0.1168.19856. Would be fine if you also display that version number next to the build date at the download page.

Sörnt

Yes the changes are definitely in that build. I am not seeing an error using this build running the demo project either with or without the compat flag so that is very strange that you are. Would you be able to provide a bit more detail about the error are you see and also are you able to attach the SQL statements that are being generated?

Thanks!
Jeremy

Hi Jeremy,

the 23. Feb. version fixes the insert order issue.

The reason it wasn't working at my side, was that I renamed the table "AddInVendor" to "Vendor" sometime ago. The Designer didn't drop the "AddInVendor" table and also not the the reference. It simple creates a new "Vendor" table for the renamed entity.

Strange thing is, when I choose "Update from Source" I expected to see an entity "AddInVendor" because it is still present at the database, but no luck.

The SQL exceptions I see are FK constraint violations from "AddInBase" to "AddInVendor".

After dropping the FK constraint and the "AddInVendor" table, the errors are gone.

Since I didn't send along my database, you created the database based on the model which didn't have the "AddInVendor" entity. So you didn't get that error.

Thanks for your support!

Kind regards, Sörnt

Hi,

I get a Sql exception "Der mehrteilige Bezeichner 't0.LockVersion' konnte nicht gebunden werden." while deleting a vendor from my model. The Vendor table has no LockVersion field, but the base class has.

Add these two lines

      uow.Remove(vendor);
      uow.SaveChanges();

after

      uow.Add(addIn);
      uow.SaveChanges();

at my repro.

Regards, Sörnt

Hi there,

an update for this much appreciated.

Sörnt

Hi Sörnt,

From the SQL being generated it appears that some of the cascaded deletes are missing the LEFT OUTER JOIN back to UserBase as t0 which is needed to refer to t0.LockVersion which is being used to filter the deletion. I will have a look into what is causing this and hopefully we should have a fix for this soon.

Jeremy

Hi Sörnt,

We have tracked this one down and added in a fix - this will be available for you in the next nightly build.

Jeremy

Hi Jeremy,

that build fixes the issue - thank you!

While testing it I saw that there are 4 SQL scripts separately send to the DB. Any reasons why they can't be batched into one or two?

The first one is quite long 178 lines - so I guess you have some kind of limit there. But the other three are 8, 29 and 8 lines long - so they could be batched.

Regards, Sörnt

They are actually being batched (and are subject to the LightSpeedContext.UpdateBatchSize limit for the maximum number of statements per batch - we batch on statements rather than lines of output), however when determining how to batch them we have rules which split the batch when we hit a versioned entity (so we can check to make sure that only 1 entity was affected by the statement when performing an optimistic concurrency check) and when we reach the end of a cascade delete chain. While this is not directly applicable to the output in your case this affects a number of other scenarios.

If you have a look at the SQL you will notice that the batch is being split initially when we get to the Vendor, because Vendor is versioned (by way of UserBase) - the LockVersion is not included since we dont check this for deletions. It is then split again when we get to UserBase, again because this is versioned.

In practice the whole set of statements specific to this query could be batched together (assuming an UpdateBatchSize limit of 11 or more) but because we have to apply blanket rules around splitting on versioning and cascade deletes to support other situations the statements are being batched as you are seeing currently.

Jeremy

Makes perfectly sense. Thanks