Entity Framework and Default Values [answered]

[Pascal Ganaye]

I setup a simple sample program to expose a problem I have with the EntityFramework. A simple table contains 3 fields

contactguid UNIQUEIDENTIFIER Default: NEWID()
surname VARCHAR(255)
forename VARCHAR(255)

I have created an EntityFramework model for it and wrote this code

policy = dbContext.CreateObject();
policyHolder.Surname = "Blog";
policyHolder.Forename = "Joe";
dbContext.SaveChanges();

The issue is that whatever I do the query set to the database will look like this:

exec sp_executesql N'insert [dbo].[tblContact]([ContactGUID], [Surname], [Forename])
values ('00000000-0000-0000-0000-000000000000', 'Blog', 'Joe')

I was not expecting the ContactGUID to be set with zeros but the default NEWID() to be returned to the client. This is not happening. I have tried to change about every property of the contactguid field (particularly StoreGeneratedPattern) but nothing seems to have any action. Did someone hit this problem?

modified on Friday, July 22, 2011 4:39 AM

[Pascal Ganaye]

Replying to my own message. My good colleague Adam found an excellent blog post about this exact problem. The solution to this problem is simple is to edit the your model edmx file. In the property element which originaly was:

I need to add the StoreGeneratedPattern="Identity" attribute.

The generated SQL then seems very good, as not only the default value are not overwritten but they are returned to the client in a single query.

declare @generated_keys table(ContactGUID uniqueidentifier)
insert [dbo].[dtblContact](surname, forname)
output inserted.ContactGUID into @generated_keys
values ('Blog', 'Joe')
select t.ContactGUID
from @generated_keys as g
join [dbo].dtblContact as t on g.ContactGUID = t.ContactGUID
where @@ROWCOUNT > 0

For more details visit: http://leedumond.com/blog/using-a-guid-as-an-entitykey-in-entity-framework-4/[^]

modified on Friday, July 22, 2011 4:39 AM