Entity Framework: No support for server-generated keys and server-generated values

Question

Hello

I tried the Beta 1 of the service pack 1 to .net 3.5. If I try to add an entity (and try to save this), I get the Exception "No support for server-generated keys and server-generated values".

How can I add entities to my Sqlce- database?

I tried to give the id- column (primary key) in the database an identity, another time without identity, only primary key --> none of them worked. I always get the same error.

What do I have to change to make successfully a SaveChanges()?

Thanks for your help,
Gerald

Answer 1

Hi Gerald

 

You will only get this error when you try to do DML operations(Insert/Update) on a database which has server-generated columns like identity and rowversion.

 

It should definitely work if you have an int column which is primary key. If this is not working for you, I just want you to check if your database schema is right and that you have generated metadata files again. (I presume that you re-generated all the metadata files again after you made your second database without identity).

 

If you still face any issues, please send me your database schema or any other info that will help me reproduce this scenario. My email id can be found in my profile.

Answer 2

Hi Ravi

Thanks, you are right. I wrote, I tried it without an identity, but when I did, I think I took the false database...
I made a table without identity and now it works. Very cool!

Thanks for your help,
Gerald

Answer 3

Sorry if I reopen the thread, but I need to have something clear. Is not possible to use auto-generated id's with sqlce? Because I have the same error and as I see, gerri312 solves his problem using manual-generated keys.

Thanks in advance.

 

Agustín

Answer 4

Have been browsing, searching, browsing and searching again to find the answer - still not clear to me: does SQL CE used with the Entity Framework support autogenerated primary keys?

 

It's a huuge effort re-writing all of my code to "comply" with this serious limitation.

 

Could anyone that knows please reply and explain in a clear and simple way please?

 

Best regards,

Christof

Answer 5

No, auto-generated keys are NOT supported when SQLCE is used via EF.

If you have rowversion/identity columns in the database/entity, on running any DML statements you will get an error saying that server-generated keys are not supported.

 

You will have to generate the keys yourself.

Answer 6

 

Many thanks for your reply! Much appreciate the openess and help!

 

So, in reality, how are people solving this annoying limitation?  I'd be interested in hearing how one deals with this typically.  It's not that CE is only used in read-only scenario's right?

 

Unless you want to spend tons of effort in determining time and time again what is the "next unique key" for each table; GUID's are the only valid option??

 

Best regards!

Christof

Answer 7

In a way you have answered your own question.

In absence of identity columns, rowguids are the best option if you are planning to do DMLs. For read-only scenarios you can have anything as the primary key.

 

We usually need keys in entities so that we can differentiate between two entities, so using an int(identity) or a rowguid should not make much difference when it comes to that (My opinion).

 

To give you the idea why there is this limitation.

SQLCE does not support batch queries. So unlike SQL Server we can't issue T-SQL statements where we can do a insert into a table with identity and fetch back the identity value inserted through the same statement.

 

insert into table .... select @@identity....

 

Because of this T-SQL limitation we don't support this. In other managed world scenarios one can have a transaction where he inserts something and then issue a select @@identity to get the value but this is not possible via entity framework.

 

[PS: This post is "as is" and confers no rights]

Answer 8

Wow; thanks!  This does make sense.  At least now I know clearly what is possible and why there's this limitation.

 

I hope in a future version, the entity framework would allow you to do two queries for an insert, so that the SQL CE Entity Framework provider could manage auto-generated keys.  (Performance is rarely an issue with CE - as usually there's only one person working with the DB at the same time and massive updates will be rare when manually entering/updating data.)

 

I would suspect there's a couple of best practices to find around the subject;  thinking out loud here: would it be possible to extend each of ones entities to generate a rowguid automatically before a "create"?  (Some partial method or so; ... not sure what is possible - but seems like a viable alternative that frees the client developer from having to deal with new primary keys all the time.)

 

Best regards!!

Christof

Answer 9

I have found for you a great resource. This will definitely help you around the best practices or things you can or cannot do..

http://blogs.msdn.com/dsimmons/pages/entity-framework-faq.aspx

 

Refer to the topic 17 of EntityKey

 

Here are few snippets:

17.3.Can you use a guid property as part of an entity key?

Yes.  You can use a guid in your conceptual model as a regular property or a primary key.

17.4.Can I use a server-generated guid as my entity key?

Unfortunately, in v1 of the EF this is not supported.  While it is possible with SQL Server to have a column of type “uniqueidentifier” and to set it’s default value to be “newid()”, with SQL Server 2000 there’s no good way to extract the value that the server generated at the time you do the insert.  This can be done on more recent versions of SQL Server, though, so the intent is that we should find a clean way to special case this support in future versions of the EF so it can be supported on those databases that allow it and not on others.  For now, the work around is to generate the guid on the client (ideally in the constructor of your object) rather than on the server.

 

I would suggest you to do the same while working with SQLCE.

That is, have the database schema in a way like this

 

create table MyEntityTable(ID uniqueidentifier primary key default newid(), ............

This way any DML that happens through database will get a new id automatically.

 

When working from you EF application use Guid.NewGuid() method to generate a new key for your entity.

EF does not allow defaults in application layer as of now, so if you leave your guid field blank it will try to insert zeroes in that column, and you are likely to hit the "duplicate key" error.

Although it is very rare to hit a issue where you can end up with same guids, but for safety purpose have a logic to generate a new guid key again if you get duplicate key error.

 

Hope this helps

Answer 10

Hi guys,
I was wondering about two things:
1) The reason that entity framework does not support autogenerated keys on sql server compact sounds like sqlce limitation. This means that we should not expect to see any change in next release of entity framework. Does this sound resonable?
2) I was thinking about an alternative approach to this problem. Instead of using Guid to continue to use integer and produce the new IDs on SavingChanges even of ObjectContext. Here is the complete scenario:
Some table (lets call it Identity) will hold the last used ID. A .NET function or SQL SP called GetNewIdentity should be implemented that will lock Identity table, increment the ID (something like UPDATE [IDENTITY] SET ID=ID+1 and then SELECT ID FROM [IDENTITY]) and unlock it. Of course if insert fails then this ID will be lost and will never be used but this is not a big deal. What do you think about this idea?

---

Dimitris Papadimitriou, Software Development Professional

Answer 11

Hello Ravi,

Thank you for the information.

I'm trying to implement your approach. On the SQL CE Database it seems to be fine, now for the EF side, I tried to define Guid.NewGuid() as the Default Value on the Mappings of the .edmx file. At compilation, I got the following error:

Error 1 Error 54: Default value (Guid.NewGuid()) is not valid for GUID. The value must be enclosed in single quotes in the form 'dddddddd-dddd-dddd-dddd-dddddddddddd'.

As it seems this is not possible, I was thinking about generating the new GUID at the Constructor of my entity class. But I think that will not be a good idea, since this code is autmatically genearted and anything I change will be lost when I update the model...

What do you suggest?

Igor.

---

Project Manager at INOVATIVA Tecnologia www.inovativatec.com