Newbiew questions

Hello to all.

I'am porting a application to LightSpeed and Ilike very very much, however I found several problems. Please excuse the english.

1. How can I model a foreign key relation when the column in the main table is not the primary key? I now this is inusual but it's possible to define such relation.

2. What is the order of the operations when calling uow.SaveChanges()?

    When I insert a entity in a table, and several related entities in related tables (using a FK relation), LightSpeed tries to first insert the related entities generating a SQL integrity error. Currently I first save the changes corresponding to the main entities, and then another SaveChanges() to insert the related entities.

I can do that but there is complicated. I think that LightSpeed could infer the correct order of insertion to avoid this problems.

3. How to know the number of operations pending before calling SaveChanges()? Sometimes its useful to know this information.

4. Model Designer. I used to initially create the model (the database has 35 tables). After that I code manually the model. Some relations were erroneous and when there are several FK relations between 2 tables its necessary to change manually the name of the collections (they appears duplicated).

Thank for making this wonderful product.

Hi, I'm another time the user with the problem.

About point 2, the problem arises when there is a many to many relation using a through relation.

The through relation works very well, but I must first insert the main entity.

The main entity is called Machine, and there is a many to many relation to the entity Clinica (there is a tabla MachineClinicas). 

            var machine = new Kernel.Database.Machine
                          {
                              Nombre = machineName,
                              InactivityTime = 300,
                              Disabled = false
                          };

            uow.Add(machine);

            uow.SaveChanges();  // <- I need this call

            // Adding clinicas using the through relation, it is very easy
            foreach (var clinica in uow.Clinicas)
                machine.Clinicas.Add(clinica);

            uow.SaveChanges();

Hello Enrique,

1. At the moment, we only support associations where the foreign key refers to the identity.  However, you can easily add your own custom property or method to traverse a unique ID association:

partial class Sku {
  private string _productRef;  // FK to unique column but not ID
  public Product Product {
    get { return UnitOfWork.Products.Where(p => p.Reference == _productRef).FirstOrDefault(); }
  }
}

But see the answer to 2 because this isn't a "real" association and may therefore run into the insert ordering problem.

2. The order of operations is Inserts and Updates (which can be mixed together), then Deletes.  Within each category, the order of operations is worked out from the entity dependency graph -- e.g. if Sku has a to-one association (FK) to Product then Products will be inserted before Skus so that the product ID is available when the SKU comes to be inserted.  (Delete goes in reverse order so Skus will be deleted before Products, again so that we don't try to delete a Product while there are still FKs to it in the database.)  So normally there is no need to do multiple steps -- LS will work out the order for you as you suggest.  If you're seeing errors here, it's probably because you have associations in the database that aren't represented in the model (so LightSpeed isn't able to infer the dependency and order things correctly).  This may be because of the fact that LightSpeed can't truly represent associations to non-identity columns.  A through association shouldn't normally run into this problem but I'll investigate and see if I can reproduce it.

3. Sorry, there isn't a way to get hold of this at the moment.  You could hook up a listener to EntityStateChanged to detect when entities are modified or deleted (or override OnEntityStateChanged in a base class), and count those, but this still wouldn't give you pending inserts.

4. We'd welcome any examples of where we're working out relationships incorrectly.  However, the duplicate names for double relationships are by design -- the idea is to force you to give them meaningful names (e.g. Father and Mother) early, rather than accepting generated names like Parent1 and Parent2 and then thinking "oh no, we should have given those meaningful names after all" only *after* you've got existing code with them in!  We know this seems niggly at first but we think it's better in the long run!

Thank you Ivan.

About point 2.

In my database model there are 2 cases with the same behavior, both are many to many relations.

I added a through association to simplify the code, but the problem also occurs without the through association.

The LS model seems correct, the relations are working, I can navigate from parents to children.

It's seems a problem with many to many relations.

I'll try to create a small example to send you. Its difficult but I'll try it.

Thank you.

Update:

3. Red face time.  I'd forgotten about this, but in LightSpeed 3, IUnitOfWork is IEnumerable<Entity>.  So you can in fact perform LINQ queries over the entities in the unit of work and get the count of pending operations that way:

int pendingOps = unitOfWork.Count(e => e.EntityState != EntityState.Default);

Sorry for misleading you.

Hello Ivan, I have a little example to send you to test the insert problem with many to many relations. How can I send you the code?

Hi,

You can email files to support@mindscape.co.nz. Please remove any binaries from the attached file as Google (who manage our email) will block us receiving attachments with binaries. 

I hope that helps,

John-Daniel Trask

Hi, I sent you a mail with the sample.

Hello Enrique,

This looks like there's a bug with inserting IdentityColumn-based entities and non-IdentityColumn-based entities in the same batch.  The non-IdentityColumn-based INSERTs get collected up for execution as a batch.  But the IdentityColumn-based INSERTs get executed as soon as we get to them.

If you have the option, change the through table (and UserGroup entity) to not use an identity column (make it use KeyTable or your ID generation stored procedure).  Otherwise, there doesn't seem to be a convenient workaround for this at the moment.  In any event, we'll aim to get a fix out in the next day or two.

Thanks very much for reporting this issue and for taking the time to create the sample.

Hello Ivan, that works! Thank you.

Initially in my DB model the table UserGroup had a composite PK composed of columns (userId, groupId), but mixing a composite primary key with a through association was complicated for me, do you think it's possible to work?

However the CancelEdit() problem persists, I can send you the file Program.cs modified with a example.

The pattern is very easy: load a user (with groups in the through association), call BeginEdit(), do something (its no neccesary to modify the entity user), call CancelEdit() and the groups of the through association gets null, there are the same number of groups in the association, but all are null now.

Thank you.

I sent you the sample another time, modified to show the CancelEdit() problem.

The sample is in the post Lazy load entities, BeginEdit() & CancelEdit().

Our recommendation is that if you have control of the database you should not use a composite key: always use a surrogate primary key.  Composite key support is intended to help users of legacy databases where they already have CKs and cannot add surrogate keys.

Regarding composite keys and through associations: given the way we implement composite keys, I don't think it would be easy to get this to work (we'd probably need to do some additional development to support this scenario).  Again, if you have a legacy database where you have to use an existing through table that relies on a composite key, let us know.

Just so you know, we have now committed a fix for the integrity issue with mixing IdentityColumn and non-IdentityColumn entities in a batch, and this will be included in nightly builds dated 8 Jan 2010, available from about 1430 GMT.  However if you're able to stick with the non-mixed design then we'd recommend this as it will be more efficient (because it allows batching) -- and I think it's easier to understand if all tables have the same identity policy!

We're investigating the CancelEdit issue and will keep you informed.

Thank you Ivan.

The DB is a legacy database but its controlled by me, I can change anything, so I will follow your recommendations. Thank you.

One more question: I assume that all queries (uow.Find() or LINQ) are executed against the DB, and all new entities added to the unit of work but not saved are not included in the result. Is it true? Is there any way to get the unsaved entities as part of the result?

You are correct: queries run against the database, and unsaved entities that match the criteria are therefore NOT included in the result.  (Another corollary is that pending deletes ARE included in the result.)

There is no direct way to get the unsaved entities as part of the result.  However, since IUnitOfWork is IEnumerable<Entity>, you could do a query against the unit of work itself, using LINQ to Objects:

var allElderlyPenguins =
  (uow.Penguins.Where(p => p.Age > 10))  // only consults the database
  .Union
  (uow.OfType<Penguin>().Where(p => p.Age > 10))  // consults the in-memory identity map

(You could furthermore append a .Except clause to this if you wanted to exclude pending deletes or entities that used to match but have unsaved changes that would cause them to no longer match.)

Obviously there is an efficiency cost associated with doing this, especially if there are a lot of entities in the unit of work; if possible you'll want to design so that it's not necessary to perform this "double check" of pending changes (short-lived units of work help here).