Question about query and remove...

 Howdy All,
     I noticed on the "Basic CRUD" page in the helpfile, that at the very bottom it mentions that you do not have to fetch the data first before deleting it...  I have a situation in I'm updating a table from an Excel spreadsheet, and the business rules state that If I should find a record that matches my incoming data, that I should delete it first then add it back in with possible updated data.  One other note, is that if the data already exists in the table, there will be only one record in the table...  With the statement from the helpfile, I'm thinking that I could setup the query, then use the .Remove(query) method of the UnitOfWork...  Is that so?  and also what happens if the query does NOT find something???  Does the .Remove have any problems with that???

Thanks in advance!!!

Kevin Orcutt
the Berry Company

Yes, you can use Remove(Query) to delete items out of the database without reading them in.  Please read the caveats in the documentation about the dangers of using this technique if the Remove can hit entities that have already been read in (to the unit of work or the L2 cache).  If you need to get data from the record (to merge with the new record) before deleting it, then you should do a Find to get the entity, then a Remove(entity) (rather than a Remove(Query)) to register it for deletion.

Also note that Remove(Query) does not actually perform the delete until SaveChanges is called, and that Remove(Query) does not handle cascading (e.g. if there are FKs pointing to a deletee, Remove(Entity) will handle this but Remove(Query) will not).

If the query doesn't match anything, then nothing is removed.  It basically translates into a DELETE WHERE, so it is not an error if nothing matches and nothing is deleted.

Note that if you remove a record, then insert a new record, the new record will have a different ID from the old record (it is a completely new record as far as LightSpeed is concerned).  This may or may not be a consideration for you.  In particular if other entities have foreign keys to the one(s) being deleted then you will need to reparent them (otherwise they will be orphaned or deleted).

 OK,
    Great.  I'll use the .Remove(Entity) method.  I've come up against a continuation problem from my first one now...  Now I'm getting a "unique constraint (...) violated" when I try to save the changes after I've 1) Deleted old row. 2) Saved Changes on UOW. 3) added new Entity. then 4) Saved Changes again.  It's on the second SaveChanges that I get the error.    Any Ideas???

Have a look at the SQL being emitted for the INSERT statement (you can see this in the Output window by setting LightSpeedContext.Logger = new TraceLogger()).  If you cross-check between the SQL being emitted, the constraint definition and the existing data, this should give you some idea of where the problem lies, e.g. is it a duplicate primary key or duplicate business data in a column with a unique key.

 Hey Ivan,

     It looks like something to do with the Sequence .NextVal ... Somehow it's getting a value already used in the sequence.  Example...  I loaded 19 new row into table.  Then I reran the app to test the "Find and Delete" part on the same data.... One the first row it should Find the row in the  table, delete it, then re-add it to the table.  The trace looks like it finds the row, deletes it, gets the next value from the sequence, then tries to add it.  however, the sequence number is already in use by another row.  (It's off by a 11, if that helps any???)  Further example  Sequence numbers 2403 - 2421 are in the table...  "New" row on the second run finds row with Sequence 2403 and deletes it. then it picks the new sequence number (in this case 2414) and tries to insert the new row with that number... BUT 2414 is already in the table...  Somehow after my first run the sequence didn't get updated or something.  so when It tries again on the second run, it picked an already used number...

Am I'm making any sense here??? 

Later,

Kevin Orcutt

One important gotcha with the way LightSpeed uses sequences is that the LightSpeedContext.IdentityBlockSize MUST be the same as the sequence INCREMENT BY.  The issue you're seeing can occur if the IdentityBlockSize is less than the INCREMENT BY amount.  I'm guessing perhaps you have an INCREMENT BY of 20, whereas the default IdentityBlockSize is 10.  Check the sequence definition, and change the IdentityBlockSize to be the same (e.g. 20).  (You can do this in code or configuration as for other LightSpeedContext properties.)

This post:

http://www.mindscape.co.nz/forums/Post.aspx?ThreadID=1988&PostID=7739

explains how LightSpeed works with sequences which may be helpful if further diagnostics are required.

 I've checked the sequence definition and it's "Increment by" is set to 1.  Should I set the IdentityBlockSize to 1 as well???

Thanks in advance,

Kevin Orcutt

Yes, either set the IdentityBlockSize to 1 or change the INCREMENT BY to 10.  Which option is better depends on how many items you typically insert per LightSpeedContext instance.

If you set the IdentityBlockSize to 1, then LightSpeed will query the sequence every time you add an entity to a unit of work.  This can result in a lot of database round-trips if you are adding a lot of items.

If you set the INCREMENT BY to 10 (and leave the IdentityBlockSize at the default 10), then LightSpeed will query the sequence every tenth time you add an entity to a unit of work.  This reduces the number of round-trips, but chews through the sequence faster.  The latter may or may not be a concern depending on how big you expect the database to get.

(If you're inserting thousands or tens of thousands of items in each run of your app, then an IBS/increment of 10 would still result in hundreds or thousands of sequence queries.  In this case, you'd want to consider cranking up the IBS/increment to 100 or 1000 or so.)

Note that identity allocation is at the LightSpeedContext level, so if you have (say) a Web app where there are multiple units of work, each of which inserts only one item, but they share a global LightSpeedContext instance (e.g. a static variable in global.asax), you can still realise an efficiency gain by using a IBS/increment of 10.

For your purposes, you probably don't want to worry about performance tuning right now: I mention the above only so that you can come back to it if setting IBS to 1 results in identity allocation becoming a performance bottleneck.