Entity Framework and SQL Server sequences



  • Got a simple task at hand - we have an SQL Server database which uses sequences instead of IDENTITY columns for primary keys - the rationale is that some tables need to share commonly unique IDs or something. Dunno, honestly. And we need to interface with that DB using Entity Framework 6.

    Problem is, inserting records into that database just doesn't work, since EF is seemingly coded to use SCOPE_IDENTITY() by default to retrieve the keys of just-inserted records. And that won't work with sequences.

    Is there any sane way to have this work? A cursory Google search shows that it's either impossible, or requires rewriting the generated SQL on the fly somehow. Surely there must be a better way?


  • area_deu

    @Maciejasjmj can you tell EF to use sprocs instead of executing the INSERT directly? You would have to write/generate a stored prcoedure once for every table, but I think that's doable.



  • @ChrisH said in Entity Framework and SQL Server sequences:

    can you tell EF to use sprocs instead of executing the INSERT directly? You would have to write/generate a stored prcoedure once for every table, but I think that's doable.

    Seems like it would be thrice for every table. By the cursory read, it's all or nothing - either all of INSERT, UPDATE and DELETE are handled by sprocs, or none of them.

    I guess it's one way to do this, although I'm certainly not gonna do that for each and every table... Perhaps we can push the client to use IDENTITY wherever possible and create the sprocs for those few tables where it isn't.

    I'd still prefer a way to configure that in EF, though.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.