Hi

We are using LLBLGen Pro 5.1 to model our database access based on Entity Framework v6 (SD.EntityFramework.v6 (DbContext API) preset); we are using this model in order to build the project as compatible with 4.5.2 NET framework. We are using the Database First model.

Our database schema is quite large and contains multiple tables views (which are imported as entities in the model) and stored procedures.

Using the entity framework 6 we were able to properly access to the database using the code generated by LLBLGen pro which saves much work on building up the classes and the model (edmx file). We have updated the code generation settings of the project to populate some information about the database as DataAnnotation attributes so that they can be used directly using Reflection (key, field length).

However we have hit a problem that we cannot resolve so far, that is, to force the Entity Framework to set default values defined in the database upon insertions.

• We define the tables in the database via scripts having the default values. The synchronization of database with the LLBLGen pro lead to a correct relational model data visible in the Catalog Explorer. The details of the fields of the tables properly displays the default values associated with the fields. The reverse engineer step to generate the entity objects produces the expected classes and fields. However, the information about default values for these fields are missing in the Project explorer which looks odd. Is it possible to display such information in the Project explorer? Nevertheless this is not the real problem. We generate the VC project via command line as follows:

CliGenerator.exe -p XAT5_1.llblgenproj -m r=Macgregor.XAT.PersistenceLayer;l=C#;pf=".NET 4.5.2";t="General";pr="SD.EntityFramework.v6 (DbContext API)";d=XAT

The resulting classes looks correct for using the Entity Framework as paradigm of database access. However, we note that when saving of new entities, the default values are simply ignored and even errors during validation appear because default values are not used.

We have updated the Code Generation settings of the project to include the attribute DatabaseGenerated(DatabaseGeneratedOption.Identity) for every data field but it is not resolving the problem.

Could you provide a suggestion to solve this problem please? Maybe there is a way in which LLBLGen pro can generate add-hoc code to populate the default values via C#?

Thanks for your help

alvarocm wrote:

However we have hit a problem that we cannot resolve so far, that is, to force the Entity Framework to set default values defined in the database upon insertions.

• We define the tables in the database via scripts having the default values. The synchronization of database with the LLBLGen pro lead to a correct relational model data visible in the Catalog Explorer. The details of the fields of the tables properly displays the default values associated with the fields. The reverse engineer step to generate the entity objects produces the expected classes and fields. However, the information about default values for these fields are missing in the Project explorer which looks odd. Is it possible to display such information in the Project explorer? Nevertheless this is not the real problem.

Default values are part of the relational model, not the entity model, as they're often not convertible to elements which make sense to the entity model, e.g. a function call to a DB function to produce a default value, how to convert that to the context of an entity model?

We generate the VC project via command line as follows:

CliGenerator.exe -p XAT5_1.llblgenproj -m r=Macgregor.XAT.PersistenceLayer;l=C#;pf=".NET 4.5.2";t="General";pr="SD.EntityFramework.v6 (DbContext API)";d=XAT

The resulting classes looks correct for using the Entity Framework as paradigm of database access. However, we note that when saving of new entities, the default values are simply ignored and even errors during validation appear because default values are not used.

I think this is because EF wants a value for a field if it's not set and won't insert a NULL?

Say you have an entity Customer, which has a field 'CustomerSince' of type DateTime and it has as default value 'GetDate()'. When you insert a new Customer entity, you don't set 'CustomerSince', and as it's not set, EF wants a value for that field and gives an error?

We have updated the Code Generation settings of the project to include the attribute DatabaseGenerated(DatabaseGeneratedOption.Identity) for every data field but it is not resolving the problem.

You can specify the setting on the field: 'Forced store generated pattern value'. You can do that per field in the entity editor, on the code generation info tab. You can also set them in bulk using the Bulk Element Setting Manipulator (the 2 gears icon in the toolbar), but this won't help much I think as you then can't provide a value for the field, EF will always assume the value is provided by the DB.

This is the reason you say 'hasn't resolved the problem' ?

Could you provide a suggestion to solve this problem please? Maybe there is a way in which LLBLGen pro can generate add-hoc code to populate the default values via C#?

It can't do that out of the box. Every entity has a call to OnCreated which is a partial method (and not implemented by default), so a template which generates the defaults in an override to OnCreated() could solve this. The template could pull the default values from the mapped table of the current entity, walking the field mapping objects in the entity mapping.

The default values are thus meant to overcome the limitation of EF that it can't insert a NULL if no value is provided (but the field has a default value) ?

(no worries on the template, if you can't figure it out, we'll write it for you, as it's a feature that more people likely find useful).

Thanks for your answer.

I understand that the default values are part of the relational model specially when the default values are calls to DB functions, however I would have expect information at this point in entity model so that we could provide such information at least via macros that could propagate to entities in orm of attributes if required.

Is correct, I have learnt about the entity framework these days and it looks like default values are not properly handled in this paradigm of db access. If you do not put a value, the 0 or null value is tried to be inserted in the database regardless there are default values defined in the database, hence the problem.

I did not test changing Forced store generated pattern value but looks a non manteinable solution since our model is Database first. I would need a way to apply this pattern in general to the whole project, specially to the fields having default values. I tried to apply attributes that should do the same function but it did not work (actually the default values were not applied), nevertheless, as you point out, probalby this sollution would not be valid because these fields might eventually have a custom values even during insertion.

The template solution you suggest looks fine if the template can specify the default values in generic way using the DB schema as the source. Is it possible to create a template to generate code for each entity setting the default values in generic way?

alvarocm wrote:

Thanks for your answer.

I understand that the default values are part of the relational model specially when the default values are calls to DB functions, however I would have expect information at this point in entity model so that we could provide such information at least via macros that could propagate to entities in orm of attributes if required.

Fair point, however do consider that the defaults are not really needed at the entity level: when an entity class instance is created, the entity instance inside it doesn't really 'exist', it only does when the entity is persisted and the default values are read back. An ORM should simply insert NULL if no value is given as the column could have a default value. Our runtime does that for example (I think nh does too), EF doesn't. It has always been a blur to me why they went that route, you can also leave it to validation specified by the user.

Anyway, that doesn't solve your problem of course.

Is correct, I have learnt about the entity framework these days and it looks like default values are not properly handled in this paradigm of db access. If you do not put a value, the 0 or null value is tried to be inserted in the database regardless there are default values defined in the database, hence the problem.

I did not test changing Forced store generated pattern value but looks a non manteinable solution since our model is Database first. I would need a way to apply this pattern in general to the whole project, specially to the fields having default values. I tried to apply attributes that should do the same function but it did not work (actually the default values were not applied), nevertheless, as you point out, probalby this sollution would not be valid because these fields might eventually have a custom values even during insertion.

Yes, it's a limitation of EF and it doesn't have a clear solution. Other than specifying values of course.

The template solution you suggest looks fine if the template can specify the default values in generic way using the DB schema as the source. Is it possible to create a template to generate code for each entity setting the default values in generic way?

Yes. Tomorrow (friday), I'll give you a small template which will be included at generation time in the entity template and will generate an override of OnCreated() with code to set fields with the default value if they have one. I'll give you a step-by-step guide so it's easy to follow what to do, and you can alter the template later on if you want to.

You use the CodeFirst preset or the DbContext preset?

We are using "SD.EntityFramework.v6 (DbContext API)" preset.

Thanks!

Please do the following.

Keep in mind, this only works if there's a single DB mapped to your entity model. Doesn't work with complex types / value type definitions but if you want you can add that yourself. Doesn't work with PK fields (but you can alter the template to make that so)

• Open project settings
• General -> Additional Templates folder, set it to (without the quotes): '.\'
• Open tools-> Template Bindings Viewer
• Click the 'New...' button on the right
• In the dialog that opens: For name, specify: EF6Customizations For template folder: select the one at the bottom, which is the folder suffixed with '.\' and the one your llblgenproj file is located For filename, specify: EF6Customizations.templatebindings For Frameworks, select Entity Framework v6 For platforms, select the .NET platforms you're using, e.g. .NET 4.5.1 and up. For databases, select the database to use. In the first row in the grid below 'Target language', type the following: For TemplateID: SD_EF_DbContext_CustomEntityInclude For Filename: CustomTemplates\SetDefaultValuesInclude.lpt For Logic language: C# For Include Only: check the checkbox Hit TAB to create the new row in the grid.
• Click Save and Close.

You now see a template binding in red, illustrating the file isn't found. Double click it. The designer will ask you if it can create the folders and file as they don't exist yet. Click 'Yes'.

The Template Editor opens. We're now going to write the include template, which will be included in each entity template, and which is bound to the ID SD_EF_DbContext_CustomEntityInclude.

In this template we'll be creating an override to OnCreated(), if the entity has a default value in its target fields. This template is included into entityClassInclude.lpt, so the variables inscope in that template are in-scope in our include template.

Copy / paste the following code into the template editor and press cntrl-shift-s to save it. Close the template editor.

<%
    var entityMapping = currentProject.GetGroupableModelElementMapping(entity, _executingGenerator.DriverID) as EntityMapping;
    var fieldMappingsToProcess = entityMapping.FieldMappings
                                    .Where(fm=>fm.MappedTarget.HasDefaultValue && !fm.MappedTarget.IsComputed && 
                                           !fm.MappedTarget.IsIdentity && !fm.MappedFieldInstance.IsPartOfIdentifyingFields).ToList();
    if(fieldMappingsToProcess.Any())
    {
%>
        protected override void SetDefaultValues()
        {
            base.SetDefaultValues();
<%
            foreach(var fm in fieldMappingsToProcess)
            {
                var valueToEmit = string.Empty;
                switch(fm.MappedTarget.DefaultValue.ToLowerInvariant())
                {
                    case "getdate()":
                        valueToEmit = "DateTime.Now";
                        break;
                    case "newid()":
                        valueToEmit = "Guid.NewGuid()";
                        break;
                    default:
                        if(fm.MappedTarget.TypeDefinition.DBTypeAsNETType==typeof(string))
                        {
                            valueToEmit = fm.MappedTarget.DefaultValue.Replace("'", "\"");
                        }
                        else
                        {
                            valueToEmit = fm.MappedTarget.DefaultValue;
                        }
                        break;
                }
%>          this.<%=fm.MappedFieldInstance.Name%> = <%=valueToEmit%>;
<%
            }
%>      }       
<%  }
%>

Add a class file to the 'EntityClasses' folder in the Model project called 'CommonEntityBaseExtensions.cs'. This is OK, it's not removed / overwritten when you generate code again. The class in this file is an extension to CommonEntityBase and add the following:

public partial class CommonEntityBase
    {
        partial void OnCreated()
        {
            SetDefaultValues();
        }


        protected virtual void SetDefaultValues()
        {
            // nop
        }
    }

And that's it. Generate code now using your normal preset by pressing F7. Double click the task to edit it and click 'Advanced' to make sure your template bindings are visible on the 2nd tab. Generate code and you should have a method in each entity which has defaults which sets the fields to their default value. As you can see conversions are needed here, and you likely have many more for your specific project.

Hope this helps. I've attached the template + templatebindings file too.

Actually it was very helpful. The solution you provided, although with some refinements, looks perfect for us. We are including such solution in our build process.

Thanks!