I am creating a new user. When I save the user, the userId is not updated.

The error I get is an InvalidCastException. I get the same exception on all the fields unless I set the State to EntityState.Fetched.

The row is correctly added to the database.

I'm certain I'm missing something. Any ideas?

UserId is an Identity column. SQL Server 2008. UserId is a read only field in the entity.

     var user = new UserEntity();
        user.IsNew = true;
        user.Firstname = "John";
        user.Surname = "Smith";
        user.Save();
        user.Fields.State = EntityState.Fetched;
        int userId = user.UserId; // This line throws InvalidCastException

please post stacktrace & ormsupportclasses build nr (See http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7722 )

OK, i've looked at this a bit further.

On the table in the database there is an "INSTEAD OF" Trigger

ALTER TRIGGER [dbo].[trUserInsteadOfIUD] ON [dbo].[tUser] INSTEAD OF INSERT, UPDATE, DELETE

If I disable this trigger, the entity works correctly and returns the right UserID Every time.

I think this lies at the heart of all the strange problems we've encountered with recursively adding entities because all the tables currently have an instead of trigger.

I've distilled the code to reproduce the error to this.

So the insert code is reduced to this:

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

        var user = new UserEntity();
        user.Firstname = "John";
        user.Surname = "Smith";
        user.Save();
        int userId = user.UserId; // Throws invalid cast exception
    }
}

}

The trigger has also been cut back to basically nothing:

CREATE TRIGGER [dbo].[trUserInsteadOfIUD] ON [dbo].[tUser] INSTEAD OF INSERT AS

-- This correctly inserts the user - since the remainder of the columns are nullable -- Everything bar the identity column INSERT INTO tUser SELECT FirstName, Surname, EmployeeNo, Comment, DefaultAccountId, LCB, LCD FROM inserted

-- This inserts the identity value and the name into a test table for testing purposes INSERT INTO tTestUserInsert SELECT inserted.Firstname + ' ' +inserted.Surname ,SCOPE_IDENTITY() FROM inserted

GO

I can remove the tTestUserInsert SQL and reproduce the error as well. The tTestUserInsert table grows as one would expect with the correct values.

There are no datatype casting issues in the SQL because the tTestUserInsert table wouldn't fire if there were.

The exception thrown by LLBLGen is this:

System.InvalidCastException was unhandled Message="Specified cast is not valid." Source="Lloyds.WMIT.Cristal.Entity" StackTrace: at Lloyds.WMIT.Cristal.Entity.EntityClasses.UserEntity.get_UserId() in C:\Development\Cristal\Lloyds.WMIT.Cristal.Entity\EntityClasses\UserEntity.cs:line 1178 at Lloyds.WMIT.Cristal.Entity.EntityClasses.UserEntity.Refetch() in C:\Development\Cristal\Lloyds.WMIT.Cristal.Entity\EntityClasses\UserEntity.cs:line 459 at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.CheckForRefetch() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\EntityBase.cs:line 2066 at SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.GetValue(Int32 fieldIndex, Boolean returnDefaultIfNull) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\SelfServicingSpecific\EntityBase.cs:line 2371 at Lloyds.WMIT.Cristal.Entity.EntityClasses.UserEntity.get_UserId() in C:\Development\Cristal\Lloyds.WMIT.Cristal.Entity\EntityClasses\UserEntity.cs:line 1178 at ConsoleApplication1.Program.Main(String[] args) in C:\Documents and Settings\c000580\My Documents\Visual Studio 2008\Projects\UserTests\ConsoleApplication1\Program.cs:line 18 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException:

The build number is no longer available in the about box - though I'm using version 3.0 Final according to the application information. The version of ProLinq Support Classes for .NET 3.5 is 3.0.10.706

If you let me know where to find the DLL that you'd like a version for I'll find it.

We are using SQL 2008, .NET 3.5 SP1.

While having this trigger, could you please open a SQL Editor, and try to run an insert statment for that table and then Select @@SCOPE_IDENTITY afterwards, to see whether it returns a the correct value (The Id of the inserted row) or not.

If you use a trigger, you might want to switch the 'sequence' on the identity field mapping from SCOPE_IDENTITY() to @@IDENTITY. Could you try that please? (and regenerate code afterward of course)

You are correct. I've tried inserting with the INSTEAD OF trigger.

IDENT_CURRENT('tUser') returns the correct value but is clearly not going to work in a concurrent user scenario.

@@IDENTITY returns the correct value, and actually in our case, this would work flawlessly.

SCOPE_IDENTITY() is generally best practise but returns NULL when the trigger is enabled.

Trouble being (a bit out of scope of this discussion) that if you use an INSTEAD OF trigger at all, you have to implement them for all three operations - DELETE, UPDATE and INSERT - to support the MERGE function. We only want the INSTEAD OF for DELETE. Therefore we have an INSTEAD OF INSERT trigger which is basically doing nothing but the straight insert.; we're only interested in the trigger for DELETE.

So a quick google has found me not very much so far. I can't see why you'd support it but I have to ask - do you support @@IDENTITY?

I started replying about 2 hours ago, then got called into meetings so I didn't see your reply Otis. I can't find where I'd switch sequence mapping. There is a sequence in the preferences SEQ_{$EntityName} butI can't find it anywhere else. Where should I be looking.

PaulWilson wrote:

I started replying about 2 hours ago, then got called into meetings so I didn't see your reply Otis. I can't find where I'd switch sequence mapping. There is a sequence in the preferences SEQ_{$EntityName} butI can't find it anywhere else. Where should I be looking.

Open the entity in the entity editor, go to 'field mappings' tab, you'll see the SCOPE_IDENTITY() sequence right next to the field which is the PK. Change it to @@IDENTITY.

SCOPE_IDENTITY doesn't work with triggers AND identity inserts, because the scope of the trigger is different.

Thanks - have found it. Few questions:

a) It is possible to set a default to @@IDENTITY b) Is it possible to bulk update all entities to use @@IDENTITY

PaulWilson wrote:

Thanks - have found it. Few questions:

a) It is possible to set a default to @@IDENTITY

No. @@IDENTITY is in general discouraged. It is only necessary when triggers are used which affect the scope's identity value.

b) Is it possible to bulk update all entities to use @@IDENTITY

Yes, two ways: 1) open the llblgenproj file in notepad, then global replace SequenceToUse="SCOPE_IDENTITY()" with SequenceToUse="@@IDENTITY"

or 2) with Element search, using a query over the mappings, then update the field mappings with a different DBSequence object, however it's a tad complex to do this (but doable). So it's easier to use option 1).

If you want option 2) (e.g. you want only a subset of the number of entities being updated to @@IDENTITY, let us know so we can formulate the piece of code for you to run in element search.