cascade delete error?

 Hi, trying cascade delete and got error (Must declare the scalar variable @p3 - yes it's missing)

 SQL trace:

 

 

exec sp_executesql N'UPDATE

  StockLot

SET

  DeletedOn = @p1

WHERE

  StockLot.Id = @p0;

 

UPDATE

  StoreStockLot

SET

  DeletedOn = @p2

WHERE

  (StoreStockLot.StockLotId = @p0 AND StoreStockLot.DeletedOn IS NULL);

 

UPDATE

  StoreMovement

SET

  DeletedOn = @p3

WHERE

  (StoreMovement.StockLotId = @p0 AND StoreMovement.DeletedOn IS NULL);

 

UPDATE

  StoreTransfer

SET

  DeletedOn = @p3

WHERE

  (EXISTS (

    SELECT

      StoreMovement.*

    FROM

      StoreMovement

    WHERE

      StoreMovement.Id = StoreTransfer.DeliveryStoreMovementId AND

      StoreMovement.StockLotId = @p0

  ) AND StoreTransfer.DeletedOn IS NULL);

 

UPDATE

  StoreTransfer

SET

  DeletedOn = @p2

WHERE

  (EXISTS (

    SELECT

      StoreMovement.*

    FROM

      StoreMovement

    WHERE

      StoreMovement.Id = StoreTransfer.EntryStoreMovementId AND

      StoreMovement.StockLotId = @p0

  ) AND StoreTransfer.DeletedOn IS NULL);

 

UPDATE

  IOrderedItem

SET

  StockLotId = NULL

WHERE

  (IOrderedItem.StockLotId = @p0 AND IOrderedItem.DeletedOn IS NULL);

 

UPDATE

  StockLot

SET

  DeletedOn = @p1

WHERE

  StockLot.Id = @p0;

 

UPDATE

  StoreStockLot

SET

  DeletedOn = @p1

WHERE

  (StoreStockLot.StockLotId = @p0 AND StoreStockLot.DeletedOn IS NULL);

 

UPDATE

  StoreMovement

SET

  DeletedOn = @p1

WHERE

  (StoreMovement.StockLotId = @p0 AND StoreMovement.DeletedOn IS NULL);

 

UPDATE

  StoreTransfer

SET

  DeletedOn = @p1

WHERE

  (EXISTS (

    SELECT

      StoreMovement.*

    FROM

      StoreMovement

    WHERE

      StoreMovement.Id = StoreTransfer.DeliveryStoreMovementId AND

      StoreMovement.StockLotId = @p0

  ) AND StoreTransfer.DeletedOn IS NULL)',N'@p0 uniqueidentifier,@p1 datetime',

@p0='EDDB38B1-CDB8-425D-B807-4E74E0E1F8A3',@p1='Jan  7 2009  1:36:34:813PM'

 

Could you provide us with your model (.lsmodel or .cs files) and the SQL CREATE TABLE scripts for your database please?  (You can attach a zip file via the Options tab, or if you would prefer not to post these publicly, email them to ivan @ the obvious domain name.)  We will then look into this.  Thanks!

I assume you are using SQL Server, is that right?

 hi, file attached - in sql dir is schema (we are using sql2005)

in nunit project run AddCountry - it's ok, run RemoveAndAddASameCountry - it fails

I want reproduce another error - with the incorrect parameters issue too,  but it's difficult for me - we are using WCF and very much layers ... but if i tried this I reproduce

UnitTest.CountryTest.RemoveAndAddASameCountry:
System.Data.SqlClient.SqlException : Must declare the scalar variable "@p1".
Must declare the scalar variable "@p0".
Must declare the scalar variable "@p0".

error, in this sample

I assume it's same case as before

thank you

Thanks for the very thorough repro case.  We have investigated this and identified a bug with continuing to use the unit of work after performing a Remove(Query) or Update(Query).  This is now fixed and the fix will be available in nightly builds dated 10 Jan 2009 and above, available after about 1430 GMT from the store.

However, your design also has an issue which will still cause you problems even once you have the bug fix.

The problem is that you are trying to use a deleted object.  In your second Save operation (which is where the exception gets raised), you do a FindOne to search for an existing Country with the ID of the country to be saved.  If you find such a Country, you update it from the country to be saved, rather than adding the country to be saved to the unit of work.  Unfortunately, you *do* find such a Country, and it is the Country which you thought you had just deleted.

This occurs because you had deleted the country from the database via a query (which bypasses the unit of work identity map -- see the docs for IUnitOfWork.Remove(Query) -- so the UOW does not know the object is deleted), and you are calling SaveChanges() instead of SaveChanges(true) (which means LightSpeed can carry on using its existing identity map rather than re-checking the database).

So when you do the FindOne, LightSpeed first of all looks in the UOW to see if it already has a Country with that ID.  Because the Remove query went behind the UOW's back, straight to the database, the country is still there.  Your code then updates this object and tries to save it.  But the object is already soft-deleted in the database!

The easiest fix for this is:

1. In LightSpeedEntity.Remove<B>, after you do the Remove(Query), call SaveChanges(true) instead of SaveChanges().  This will ensure that the FindOne call returns null, to indicate that no entity with the specified Id currently exists.

2. In LightSpeedEntity.Save<B>, if FindOne returns null, check whether the entity it receives is in the New state (in which case Add it and SaveChanges), and if not then clone it into a new instance and Add that new instance instead.  E.g.

if (uof.FindOne<B>(idFromTransportLayer) == null)
{
  if (newdata.EntityState == EntityState.New)
  {
    result = Add<B>(uof, newdata);
  }
  else
  {
    B b = new B();
    TransformByReflection(b, newdata);
    result = Add<B>(uof, b);
  }
} // etc.

(You will need to add a new() constraint to the Save<B> "where B : ..." clause.)

I'm not sure how this maps back to your WCF scenario. I'm guessing you have something similar with objects or changesets coming in over WCF and trying to apply those to the unit of work using the same Extensions library.  In which case hopefully the same changes will solve that problem too.  However it's less obvious to me in that case how you might be trying to save a deleted object -- unless your WCF application is maintaining a long-running unit of work rather than a per-transaction UOW?  That could result in deleted objects hanging around and causing trouble...  Anyway, try making the same changes there (once you have the bug fix) and if they don't help then we will investigate further.

 Hi thank you very much, are scenario is complicated

- in transport layer we receive object - we need to transform it to business layer (BL) - here must we create a new object every time - we need two objects (old data, new data) in BL - for business rule testing - so state of newdata object is every time EntityState == EntityState.New

I'll try your new night build and will change save afeter remove to SaveChanges(true)

 Hi, again :)

I tried to change Remove<B> this way (not using query but entity):

 

public static void Remove<B>(UnitOfWork uof, Guid id)
            where B : Entity<Guid>
        {
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
            {

                // validate               
                B original = uof.FindOne<B>(id);
                if (original != null)
                {
                    ValidateRemove<B>(uof, original);
                    //
                    uof.Remove(original);
                    uof.SaveChanges();
                }
                scope.Complete();
            }
        }

but An optimistic concurrency violation was detected while attempting update or delete of table occured

We appear to have a bug with objects that use both optimistic concurrency checking and soft deletion, *and* have child collections.  We have located the source of the bug and will try to provide you with a fix as soon as possible.

In the meantime, we suggest you revert to Remove(Query), doing a SaveChanges(true) to force the unit of work to pick up the results of the removal.

I have committed a fix for this bug and it will be included in nightly builds dated 13 Jan 2009 and above, available after about 1430 GMT from the store.

Please note that concurrency checking is applied ONLY to the entity being deleted.  If child objects have been changed in the database by another user, the version mismatch will not be detected and the parent will still be deleted (and the children will be cascade-deleted).

thank you very much, I'll try this