Hi there,

We are using LLBLGen Pro 4.0 Final with a target framework of EF v5.0. We have a number of database tables such as...

CREATE TABLE [dbo].[Referral]( [ReferralId] [uniqueidentifier] NOT NULL, [UserId] [uniqueidentifier] NOT NULL, [EmailAddress] nvarchar NOT NULL, [ReferralCode] nvarchar NOT NULL, [ReferralMessage] nvarchar NULL, [isSuccessful] [bit] NOT NULL, [DateTimeCreated] [datetime] NOT NULL, CONSTRAINT [PK_Referral] PRIMARY KEY CLUSTERED ( [ReferralId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Referral] ADD CONSTRAINT [DF_Referral_ReferralId] DEFAULT (newid()) FOR [ReferralId] GO

ALTER TABLE [dbo].[Referral] ADD CONSTRAINT [DF_Referral_isSuccessful] DEFAULT ((0)) FOR [isSuccessful] GO

ALTER TABLE [dbo].[Referral] ADD CONSTRAINT [DF_Referral_DateTimeCreated] DEFAULT (getdate()) FOR [DateTimeCreated] GO

ALTER TABLE [dbo].[Referral] WITH CHECK ADD CONSTRAINT [FK_Referral_User] FOREIGN KEY([UserId]) REFERENCES [dbo].[User] ([UserId]) GO

ALTER TABLE [dbo].[Referral] CHECK CONSTRAINT [FK_Referral_User] GO

...whereby...

-The PK is a single uniqueidentifier with a default value of newid() -There are DateTimeCreated fields with a default value of getdate()

When the edmx for the above is generated by LLBLGen Pro it seems to generate as...

<Property Name="ReferralId" Type="uniqueidentifier" Nullable="false" /> <Property Name="DateTimeCreated" Type="datetime" Nullable="false" />

...but for us to be able to add a new record and not specify these values we seem to need to have it set as...

<Property Name="ReferralId" Type="uniqueidentifier" Nullable="false" StoreGeneratedPattern ="Identity" /> <Property Name="DateTimeCreated" Type="datetime" Nullable="false" StoreGeneratedPattern ="Computed" />

...manually within the .edmx XML generated by LLBLGen Pro.

From the other threads regarding StoreGeneratedPattern that I have seen it seems similar is being done for a timestamp data type. What should we be doing in the above scenario? Also we obviously want to "set" this somewhere once and have it apply to refreshes of the database, further generation of source code etc.

Thanks in advance,

-Pat Ramadass

The logic is there, in the templates, but I don't know if ADO.Net succesfully retrieves the IsComputed metadata property of the table field. I will check that.

Hi there,

To confirm it is not a computed field within the database itself, i.e. it's just default values on the relevant fields for both of those cases. Please confirm asap as it obviously has quite a large impact for this particular project and deadlines are tight. It's the type of thing that could possibly be easily changed on the .edmx designer itself, but in the absence of that really being used with the LLBLGen Pro designer and EF I would think it should be possible to do it via LLBLGen Pro's designer instead.

Thanks,

-Pat Ramadass

NewID() as pk value creator won't work, and we don't support that as such. The issue is that the value won't be retrievable after insert, so the entity object's new PK value is unknown after insert. It's also not recommended because it generates Guids which are not sequential, so it's slow at inserts because it will cause a lot of I/O due to the clustered index. Better is to use NEW_SEQUENTIALID() but EF doesn't support that (we do though). This is by design (as NEWID() for default value of a PK is not really what you want). About the default value containing field and the computed value... the 'computed' flag makes sure EF will read the value back when you persist it. We didn't anticipate usage of computed in this scenario, as it's mostly used for identity fields. In practice, not setting the field will likely get you what you want, but it won't read back the value, and it might be awkward as you need to set the field as nullable (otherwise EF's validation at runtime will refuse the insert). Could you try setting the field as 'ReadOnly' and nullable, so it's not settable, and see whether it is persistable?

We can add a setting to EF's settings which shows up for each field so you can enable the setting which then can be used in the templates to generate the required annotation. We'll look into adding this setting.

How did you define the getdate() default on the field? Because if you do that as the computed column specification, the IsComputed flag is set and the driver will set the IsComputed flag for the field in the relational model data. This flag is then used to emit the computed annotation in the EDMX. Is the IsComputed flag set on the table field in the catalog explorer?

When I define 'getdate()' as Computed column specification on a non-nullable datetime field in a table, create a new project from that catalog, obtain the metadata, I see IsComputed is set for that field. When I then create an entity and generate EF5 code, the edmx shows: <Property Name="Accessed" Type="datetime" Nullable="false" StoreGeneratedPattern="Computed" />

To test to see what the driver sees, please run this query:

SELECT (SELECT COLUMNPROPERTY(OBJECT_ID('[dbo].[Referral]'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsComputed')) AS IsComputed,
 (SELECT COLUMNPROPERTY(OBJECT_ID('[dbo].[Referral]'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity')) AS IsIdentity, 
 (SELECT COLUMNPROPERTY(OBJECT_ID('[dbo].[Referral]'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol')) AS IsRowGuidColumn 
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='Referral' AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='dbo' 
 ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ASC

Hi there again,

Thanks for the feedback on this as well.

Using uniqueidentifer in general is something we have done for a while on various projects, originally stemming from occasionally connected solutions which required replication and similar. Understood regarding the downsides of using NEWID, we will look to make use of NEW_SEQUENTIALID() going forward starting with this project. Do you feel that there are other reasons not to use uniqueidentifer for PKs in general? It's not as easy when it comes to enums and similar for one.

A setting per field in terms of the StoreGeneratedPattern would be good I think, i.e. similar to EF's designer itself. Not sure how quickly and easily this can be added on your side though?

With GetDate() this is in fact not set as the computed column specification, it is set as the default value only for this particular field as it is DateTimeCreated. For DateTimeUpdated for example I guess it could be the computed column specification and from your post it should then work.

What are your thoughts on next steps then? With the PKs my understanding from what you have said is that even if we change them to NEW_SEQUENTIALID() we would still need to set them as being Identity as a StoreGeneratedPattern (As per http://stackoverflow.com/questions/5333417/what-is-the-recommended-identity-generation-approach-in-entity-framework) for EF to even save it properly. With the GetDate() we could possibly do it in another way I guess, but, setting it to Computed would obviously work if we could do that somehow.

Perhaps we should have just stuck with LLBLGen Pro Runtime Framework instead of EF!

patramadass wrote:

Using uniqueidentifer in general is something we have done for a while on various projects, originally stemming from occasionally connected solutions which required replication and similar. Understood regarding the downsides of using NEWID, we will look to make use of NEW_SEQUENTIALID() going forward starting with this project. Do you feel that there are other reasons not to use uniqueidentifer for PKs in general? It's not as easy when it comes to enums and similar for one.

NEWSEQUENTIALID isn't supported by EF 5, so it won't be able to retrieve the value inserted. At least I can't find info that it does. EF6 seems to do it, but there are still issues open about this.

Guids for PKs can work perfectly, though if you're not going to merge rows with rows from other db instances, it might be overkill: if there's no chance on PK value duplicates why bother?

In any case, you can continue with the normal uniqueidentifier column if you want to, and use Guid.Comb to create pk values. This means you have to create values on the client (the DB won't do this) but this is OK.

See the article of Nilsson about the background of it: http://www.informit.com/articles/article.aspx?p=25862

Code how to create Comb values on the client: http://stackoverflow.com/a/6100778/44991

Using the above code you can create unique GUids, which are sequential, and therefore don't mess up your clustered index. So you then have to set the ReferalId to a value returned from the call to that method I linked to above and you're set.

A setting per field in terms of the StoreGeneratedPattern would be good I think, i.e. similar to EF's designer itself. Not sure how quickly and easily this can be added on your side though?

1 minute, it's not complex .

With GetDate() this is in fact not set as the computed column specification, it is set as the default value only for this particular field as it is DateTimeCreated. For DateTimeUpdated for example I guess it could be the computed column specification and from your post it should then work.

Yes, specify the GetDate() there and everything should work as normal: the field should be set as 'Computed' in the EDMX without a problem.

What are your thoughts on next steps then? With the PKs my understanding from what you have said is that even if we change them to NEW_SEQUENTIALID() we would still need to set them as being Identity as a StoreGeneratedPattern (As per http://stackoverflow.com/questions/5333417/what-is-the-recommended-identity-generation-approach-in-entity-framework) for EF to even save it properly. With the GetDate() we could possibly do it in another way I guess, but, setting it to Computed would obviously work if we could do that somehow.

We'll add the storegeneratedpattern setting, but for now, you can proceed with this: - create the Guids on the client using the Comb code above - Define the getdate() value as Computed column specification, to make the field a computed column. THis will make the field show up as a computed column and the EDMX will have the right setting.

Perhaps we should have just stuck with LLBLGen Pro Runtime Framework instead of EF!

Not sure why you'd to go for EF, but indeed WIth v4's performance increase we're at least 30% faster than EF for entity fetches and as we have multiple query systems there's more flexibility as well. But perhaps you needed EF because of some client requirement, not sure. Anyway, we'll add the setting (Also the setting for the annotation for geometry types) so you have the flexibility to decide for yourself what to use

I hope to have the changes for you later this afternoon.

Hmm, a setting for this at the field level is actually not going to work: we define settings on the entity level. However the StoreGeneratedPattern has to be defined on the table level, which means 2 or more entities on the same target require the setting to be set in both entities. This is rather silly.

Additionally, when we emit SSDL information, we don't know a mapped target and there can be multiple, so deciding what to do on one of them is not going to work.

So we'll not going to add this setting; for computed columns, please define the default as the computed column definition, and for Guids, please define the guid at the client. If this is an impossible workaround for you, we can discuss what to do about it in this thread.

Hope this helps. The change for EDMX/settings to allow geometry types will be attached to the original thread of the problem.

Hi there again,

Thanks for the feedback. To me one of the problems here is that the built in .edmx designer obviously let's you fiddle with these settings, but with LLBLGen Pro generating the .edmx and related source code this is somewhat hidden from us and while we could change it manually it's just going to get overwritten during the next regen. I think I may be misunderstanding the reasons for not implementing it at entity level, but if it's because you feel that multiple entities mapped on the same table would not always want this setting to be "inherited" I would think that they always would. Perhaps I am missing something obvious otherwise though in terms of when or why this would be an issue.

In terms of the alternate routes suggested, computed column specification will work for the likes of DateTimeModified (Inserts and Updates), but it will not be the desired behaviour from our side for say DateTimeCreated (We would only want this set on Inserts) obviously. Reviewing the Guid Comb links sent through and will make a decision on implementing this client side, thanks for this.

Overall the decision to go with EF on this project was really because the client wanted a vanilla MS stack as much as possible. We still do a lot of custom/bespoke development for clients so often we are bound by their technology choices. We had also hoped working with EF had matured considerably by now, but it still seems like some of the oldest paint points, including with the designer, are still around which is hard to believe. We may still change things on this project and go back to your Runtime Framework again.

Thanks for looking it all for us and also the quick feedback.

patramadass wrote:

Hi there again,

Thanks for the feedback. To me one of the problems here is that the built in .edmx designer obviously let's you fiddle with these settings, but with LLBLGen Pro generating the .edmx and related source code this is somewhat hidden from us and while we could change it manually it's just going to get overwritten during the next regen. I think I may be misunderstanding the reasons for not implementing it at entity level, but if it's because you feel that multiple entities mapped on the same table would not always want this setting to be "inherited" I would think that they always would. Perhaps I am missing something obvious otherwise though in terms of when or why this would be an issue.

The code which generates the SSDL data doesn't know any types being mapped onto the tables it handles. It then has to lookup these types, and there can be multiple types mapped onto the same target. If one entity has this setting set, and the other one doesn't, the one which has the setting set is used for the SSDL data, but the other one doesn't have the setting, but inherits this setting as it's mapped onto the same table.

This isn't ideal. As there are workarounds (at the time of posting) we decided not to go for this, as it's unfortunately a cause for confusion. I said 'at the time of posting) as your quote below shows a showstopper:

In terms of the alternate routes suggested, computed column specification will work for the likes of DateTimeModified (Inserts and Updates), but it will not be the desired behaviour from our side for say DateTimeCreated (We would only want this set on Inserts) obviously.

Hmm... the computed column of course always gives the current date at fetch time, not the one which was inserted at insert time... That's indeed not a usable workaround. As there's no workaround, we'll add the (albeit confusing) setting which allows you to mark the storegenerated pattern from the entity level, which should solve it. Hopefully I have it working this afternoon.

Overall the decision to go with EF on this project was really because the client wanted a vanilla MS stack as much as possible. We still do a lot of custom/bespoke development for clients so often we are bound by their technology choices. We had also hoped working with EF had matured considerably by now, but it still seems like some of the oldest paint points, including with the designer, are still around which is hard to believe. We may still change things on this project and go back to your Runtime Framework again.

Sorry to hear that, but you can always switch back to our framework inside the designer without much problems.

I'll try to get this setting added later today so you can mark the fields properly.

Attached are 3 files, same procedure as with the other thread. I zipped them in 1 archive.

• edmxInclude.lpt. This template has to be copied to <llblgen pro installation folder>\Frameworks\Entity Framework\Templates\Shared\Shared
• generalTemplateUtils.lpt. This template also has to be copied to <llblgen pro installation folder>\Frameworks\Entity Framework\Templates\Shared\Shared
• SD.Frameworks.EntityFramework.v5.frameworksettings. This file has to be copied to <llblgen pro installation folder>\Frameworks\

Each field has a new setting, ForcedStoreGeneratedPatternValue. This setting controls the StoreGeneratedPattern annotation on both the SSDL and the CSDL level. You can set the setting in bulk using the Bulk Element Setting Manipulator, which is available through the toolbar. This will allow you to select all fields which should get the same setting at once.

Identity on guids makes NEWSEQUENTIALID() as default value work on EF5, I tested this, this works.

Please let us know if problems occur with this. These files contain the fix I gave you 2 days ago.

Apologies for the delay on responding. The project that this issue was related to has been put on hold by the client. That said we will probably go back to using the LLBLGen Pro Runtime Framework once/if it resumes, the EF experience (No fault of yours, but rather the inherent issues and shortfalls that EF has) just hasn't been all that pleasant so far and the client will be fine with this.

I see that the fixes from both threads have also been included in the latest release based on the change log. Will definitely let you know if there are issues if we continue to use it as we had originally planned. Similar will come up on other projects as well.

Thanks again for the great support.

Sorry to hear the project has been put on hold, hopefully it will continue in the future!