This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hello all... I am trying to retrieve the ID that the lightspeed sequence script on oracle database generates for each insert....
I have something like this:
using ( DatabaseModelUnitOfWork unit = Repository.Context.CreateUnitOfWork() ) { Something s = new Something(); s.Name = "John"; unit.Add(s); unit.SaveChanges(); int id = s.Id; } the problem is that the ID returned is some random number that lightspeed generates for it's entity, not the one generated by the script and entered into the table... I need that ID to use it for deleting that record later on.... Can someone please help me out ? Thanks.. |
|
|
LightSpeed only generates temporary IDs when using the IdentityColumn identity method. For the Sequence identity method, the ID from the sequence should be available any time after you Add the entity to a unit of work, so would definitely be available after SaveChanges(). Even when using IdentityColumn, the ID allocated by the database should be available after SaveChanges(). Can you check when your real code is retrieving the Id property? |
|
|
my real code actually retrieves the ID just like in the example that I posted, right after SaveChanges()... I ran it a minute ago and the ID entered into the table was 61 and my entity got the ID 152854 (or something like that)....my identity method is sequence....my data provider is Oracle9.... I don't understand this... |
|
|
I just performed another test and here is what I got.... inside of the "using" block, in the example that I posted, right after SaveChanges() I did this:
Something newSomething = unit.Somethings.OrderByDescending(i=>i.Id).Take<Something>(1).First<Something>(); guess what ID newSomething has....the one entered into the database...the one I need... is this a feature or a bug ?? I don't like it that I have to query for the entity I just added....is there a workaround or am I just doing something wrong ??
|
|
|
Well, something is certainly wrong, but I can't for the life of me figure out what it is! When we try this, the entity gets the ID that it was allocated in the database, as you'd hope and expect; you shouldn't need to re-query and we certainly don't have to in our test environment. In fact with the Sequence identity method it is the entity's ID which gets sent TO the database: that is, LightSpeed sets the entity's Id property first (from the sequence), and it is exactly this Id which is retrieved and included in the INSERT statement. So I'm baffled as to where this different database ID is coming from! Could you try logging the SQL (LightSpeedContext.Logger = new TraceLogger(), LightSpeedContext.VerboseLogging = true) and post: - the logged SQL Could you also post the entity class declaration (you can omit the body of the class, but please leave any attributes) and the LightSpeedContext settings from your initialisation code or config file? Can you also let us know what triggers you have defined on the table you're inserting into? Thanks! |
|
|
I am really falling behind on what I am supposed to be doing here so I don't have the time to figure out how to use logging since I have not used it yet but here is what I can tell you and I can just hope you can figure something out from this...
last lightspeed sequece ID in the database is 152941...last generated ID in the table is 141....the entity gets the ID of 152931 ( I really don't get this )....
lightspeed script has the cache size of 20...
here are the LightSpeed Context settings (or how I initialize it): public static LightSpeedContext<DatabaseModelUnitOfWork> Context { get { if (_context == null) { _context = new LightSpeedContext<DatabaseModelUnitOfWork>();
_context.ConnectionString = LightSpeedContext.Default.ConnectionString;
_context.PluralizeTableNames = LightSpeedContext.Default.PluralizeTableNames;
_context.IdentityMethod = IdentityMethod.Sequence;
_context.DataProvider = DataProvider.Oracle9;
} return _context;
}
I also have an insert trigger: before insert on LOADER_STATUS this sequence that I'm using in the trigger says that the last number is 161 ( also cache size of 20)....so where is this ID of 141 coming from then ?? my script or the lightspeed script ?? or neither ?? I'm confused now... hope this is enough info...
|
|
|
Your problem appears to be that the insert trigger is overwriting the ID sent by LightSpeed. LightSpeed is allocating the entity an ID from the correct sequence (as you can see by comparing the allocated ID and the LightSpeed sequence value), but the database column is subsequently being set to a value from the LOADER_STATUS_SEQ sequence. I'm not sure what :NEW.IDTABLE is so I can't be absolutely certain what's going on, but the fact that the ID sent by LightSpeed corresponds to the LightSpeed sequence and that the ID actually inserted corresponds to the LOADER_STATUS_SEQ sequence makes me sure that it's something related to this. Assuming this is the case, LightSpeed has no way of knowing that Oracle has messed with the insert behind its back. LightSpeed doesn't read back from the database to check that the database did what it expected (even if it wanted to, if the database changes the ID, it has no way of knowing what ID to ask for). So you need either to remove that trigger (if that won't break existing apps) or to perform the explicit re-query. (By the way, if you are puzzled as to why the LightSpeed ID is different from the sequence value, this is because the sequence value had already been incremented when you looked at it, and LightSpeed was just chewing through the range of values it had acquired as part of that increment.) |
|
|
I don't want to get the last id inserted but I'd like to understand one thing... Are you saying that we should have only Lightspeed sequence in the database if there are many tables in our db with autoincrement ID column and we want to insert records in all of the from code using unitofwork.Add(), unitofwork.SaveChanges()? With no triggers executing someseq.nextval statement?
|
|
|
Yes, that's correct. When you use the Sequence identity method, LightSpeed queries the sequence and allocates IDs from what it gets back. It sends these IDs as part of the INSERT statement. You should not use triggers to re-allocate the IDs database-side. With the Sequence identity method then only the LightSpeed sequence is used. In the nightly builds we have added a MultiSequence identity method which allows you to use multiple sequences, e.g. sequence per table. |
|