I'm using the trial of LLBL v3 with the EF4 self tracking entities output option. My database is SQL Server 2005.

So I have many tables which have many fields that are declared not null with default values of blank string (''). This design cannot be changed as it is an established application.

The problem I'm bumping into is that when I attempt to create a new entity in code and persist it to the database, it fails with a message that null values cannot be inserted into a non-null column.

Here is my sample VB.Net code:

Dim model As New MyEntities

Dim newAddress As New Address With {
  .Street = "123 Main St",
  .City = "Springfield",
  .Zip = "12333"
}

model.Addresses.AddObject(newAddress)
model.SaveChanges()

Which creates the following SQL statement:

exec sp_executesql N'insert [dbo].[Address]([aStreet], [aCity], [aState], [aZip])
values (@0, @1, null, @2)
,N'@0 varchar(50),@1 varchar(50) ,@2 varchar(10)',@0='123 Main St',@1='Springfield',@2='12333'

As you can see, it appears that the generated SQL is specifying NULL for any column which wasn't specifically listed in my statement (in this example the "aState" column), and that's what is causing the issue. As far as I'm aware, the only way to force the DB to use the default values is to not include that field at all in the SQL query.

I found this other post at http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=18942 where Otis says that attempting to insert a null into a non null field should result in the database using the default value, but that does not happen for me in SQL 2005. Unless there's some flag that needs to be set on the database that I'm not aware of?

if your field has a default constraint in the database, saving a null into that field will make the db set the field to the default constraint value. I don't see how this goes wrong with us changing the default value from some magic value to null. Could you please check whether the field indeed has a default constraint?

So my question is how do I make this simple scenario work? Non null fields with default values. I feel like I must be missing something basic because this seems so fundamental. Could it have something to do with the default value being a blank string, which maybe LLBL is interpreting as there being no default value at all?

The default is used if the field is not present in the sql, which is your case. How your table DDL script looks like?

daelmo wrote:

The default is used if the field is not present in the sql

Yep I'm aware of this and that's what I was expecting the generated SQL would do -- not pass values for any fields that were not set in the code. But what it actually IS doing is passing NULL for all fields that are not set in the code, which causes an SQL exception when the underlying database field is not nullable.

If you look in my example, you can see the "aState" field is being assigned a NULL value in the generated SQL, when it should be excluded (or assigned DEFAULT) since I didn't set it in my code.

How your table DDL script looks like?

Here is the table definition for my example:

CREATE TABLE [dbo].[Address](
    [aAddressId] [int] IDENTITY(1,1) NOT NULL,
    [aStreet] [varchar](50) NOT NULL CONSTRAINT [DF_Address_aStreet]  DEFAULT (''),
    [aCity] [varchar](50) NOT NULL CONSTRAINT [DF_Address_aCity]  DEFAULT (''),
    [aState] [varchar](50) NOT NULL CONSTRAINT [DF_Address_aState]  DEFAULT (''),
    [aZip] [varchar](10) NOT NULL CONSTRAINT [DF_Address_aZip]  DEFAULT ('')
)

What do you think?

Apparently the field is seen as 'changed' by EF. Not sure if this is a bug in EF or the self-tracking poco's. We just ported those templates over so if there's a bug somewhere, it's also in the STE templates of EF.

In the time being, you can work around this by setting the default value in code.

More info here: http://social.msdn.microsoft.com/Forums/en-US/adonetefx/thread/cda357cf-e69b-42d0-88bd-e8a598bad25e

We'll look into whether we made a mistake in our port or if not, if we can make this better, but it's not under our control what the EF context does under the hood unfortunately, so it might be impossible.

The STE code MS wrote isn't without limitations btw. If possible, I'd switch to the normal poco classes preset.

(edit) confirmed with vanilla MS' STE templates: same error. The 'self tracking' system in the entities can't distinguish a null value as 'not initialized' from 'set to null'. It doesn't use a system of flags and proper checks for this, it seems. This is obvious if you look at the code in the context extensions file, the method 'SetValue'. This sets a value in a row object which is internal to entity framework: it doesn't use the notion whether the entity is new or already existed. Checking whether the entity is new is essential for this, our own framework checks for this with a more complex method to avoid mistakes like this.

Looking through the code there's no option to avoid this. It also doesn't take into account any mapping information it seems as our tests with a nullable property shows that it inserts NULL in nullable properties as well. So it simply dumps the values in the properties to the db.

So to make this work, set the properties to a value in a partial class, where you implement the partial method OnCreated, which is called right after the ctor runs.

Ugh, not the answer I was hoping for

The STE code MS wrote isn't without limitations btw. If possible, I'd switch to the normal poco classes preset.

What exactly do I gain/lose with the POCO vs. STE? Would it resolve this issue?

So to make this work, set the properties to a value in a partial class, where you implement the partial method OnCreated, which is called right after the ctor runs.

My problem with this workaround is that there are literally hundreds of fields configured this way across 50+ tables. This would all have to be manually done by hand. Besides being labor intensive and inefficient, it also opens the door to errors and synchronization issues should the database field definitions change down the road. Is there a way for LLBL to auto-generate these partial methods or some other code that will essentially "band-aid" the bug until MS gets around to fixing it themselves?

I can't see a way to solve this rather than coding it like Frans said. Unless you have the option to switch the framework, and use LLBLGen Pro Framework.

It is doable I think, with custom templates (to generate the partial class). The drivers obtain the default values for fields in a table, which could be used to emit the code at generation time.

So a template which for each entity generates a partial class which implements the OnCreated() method. In there, you emit simple setters for each field which is not nullable, and is mapped onto a field with a default value set (so other than empty string). You can also make this simpler, and simply emit code which emits a default value for the type of the entity field, e.g. for string fields you emit a setter which sets the field to "". I also think you only need this for strings, datetime, guid and byte array typed fields, as other fields are valuetypes, and by default they have a valid value (int fields for example are set to 0, boolean's to false etc.).

If you get stuck, let us know and we'll help you through it. You can just post in this thread to re-open it.

Differences between STEs and normal POCO's is the change tracking which manifests itself in the fact that a not-new edited STE entity knows it's not new when it's persisted with a context instance which is different from the one you used to fetch it (typical scenario in websites).

Still a bit of a pain though, as lazy loading for example doesn't work in STEs, which is somewhat logical if you consider they might be serialized across the wire, but it doesnt make sense if you use them solely on the server.

Otis wrote:

If you get stuck, let us know and we'll help you through it. You can just post in this thread to re-open it.

Ummm......OK so I'm stuck Is there some documentation on the template syntax and/or some sample code you throw my way to get me started?

FWIW, I think this would be a valuable feature to have built in to the software, at least as an option, since the MS implementation is currently broken and there is no simple workaround for it. If LLBL had the option to automatically generate this "patch" code and essentially fix the MS bug before MS slowly gets around to it, it would make it a no brainer for others like myself who are at a dead end. Case in point, just see some of the angry posts about a similar bug that MS has known about for over a year and still has not released a fix for, even though it can lead to data loss/corruption:

http://connect.microsoft.com/VisualStudio/feedback/details/505178/storegeneratedpattern-property-in-ado-net-entity-model-designer-sets-cdsl-annotation-but-not-ssdl-attribute http://geeksharp.com/2010/05/27/ef4-bug-in-storegeneratedpattern-ssdl/

So if you could give me a kickstart here that would be appreciated....or if you want to throw something together yourself, that is also welcome

The best place to start will be the SDK documentation here http://www.llblgen.com/documentation/3.0/SDK/hh_start.htm. The easiest way to get into modifiying templates is just to crack on and make some changes to get used to how the system works, and then work up from there...!

Matt

I'll see what I can come up with today for you. Stay tuned

pt wrote:

FWIW, I think this would be a valuable feature to have built in to the software, at least as an option, since the MS implementation is currently broken and there is no simple workaround for it. If LLBL had the option to automatically generate this "patch" code and essentially fix the MS bug before MS slowly gets around to it, it would make it a no brainer for others like myself who are at a dead end.

Good point. We'll look into this in the near future (3.2+)

Case in point, just see some of the angry posts about a similar bug that MS has known about for over a year and still has not released a fix for, even though it can lead to data loss/corruption:

http://connect.microsoft.com/VisualStudio/feedback/details/505178/storegeneratedpattern-property-in-ado-net-entity-model-designer-sets-cdsl-annotation-but-not-ssdl-attribute http://geeksharp.com/2010/05/27/ef4-bug-in-storegeneratedpattern-ssdl/

This is something our designer doesn't suffer from: identity fields are marked as such in the relational model data and because of that also as such in the SSDL

So if you could give me a kickstart here that would be appreciated....or if you want to throw something together yourself, that is also welcome

I'll throw something together and document the steps here.

We're going to do the following: (as this is the simplest way) - create an include template which is included in all entities - add a virtual method to CommonEntityBase, called from an implementation in CommonEntityBase. - in our include template, we're going to override this virtual method and init the fields.

Steps to take: In your code: - create a partial class of CommonEntityBase - Add the partial OnCreated() implementation, and in there call to a virtual (overridable) method called OnEntityCreated: (C#)

partial void OnCreated()
{
    OnEntityCreated();
}

Add a protected virtual (overridable) method called OnEntityCreated()

protected virtual void OnEntityCreated()
{
}

Designer: - load your EF4 project. - go to project properties and specify a folder for 'AdditionalTemplatesFolder' and 'AdditionalTasksFolder'. These folders have to exist. For this exercise, c:\temp\ is used - open template bindings viewer (click the button or do Window -> show template bindings viewer) - click 'new'... on the template bindings viewer. - in the dialog, specify: Name: MyBindings Precedence: 11 Filename (click [...] button): mybindings.TemplateBindings Description: (fill in something) Frameworks: Check 'Entity Framework 4' Platforms: Check '.NET 4' Databases: Check <Any> - by default, the target language is C#, select VB.NET - Add a templatebinding: TemplateID: SD_EF_STE_CustomEntityInclude Filename: VB.NET\custom_STE_EntityInclude.lpt logicLanguage: C# includeOnly: true (checked) - click Save and Close

The template bindings viewer now shows your new bindings (if not, select it in the 'Found templatebindings combo box). The templatebindings you specified (if you only did VB.NET, there's just 1) are displaying their filename in red, showing that the file doesn't exist yet. Select the row and click 'Edit selected files'. Click 'yes' when you're asked if you want to create them.

You're now greeted with an empty template editor. As this is an include template, it's included in a bigger template, namely the steEntityClass.lpt (bound to SD_EF_STE_Entity, in the template bindings SD.EntityFramework.v4). So the current entity and fields are already known and in-scope.

Select in template bindings viewer the SD.EntityFramework.v4 templatebindings and open the steEntityClass.lpt template in the editor. The include is on line 297.

Switch back to your empty custom template editor. You can create several tab groups, so you can place the empty template editor of your own template below the steEntityClass.lpt template, so you can check what's in scope and what's it called (right-click tab header, -> move to new horizontal tab group)

In the empty editor, paste (for C#)

        public override void  OnEntityCreated()
        {
<%  Type[] typesToCheck = new Type[] {typeof(Guid), typeof(string), typeof(byte[]), typeof(DateTime)}; 
    foreach(var field in entityFields.Where(f=>!f.IsOptional && !f.IsPartOfIdentifyingFields && (f.FieldType.KindOfType==FieldTypeKind.DotNetType) &&
                                                typesToCheck.Contains(f.FieldType.RepresentedType)))
    {
        string valueToEmit = string.Empty;
        var fieldType = field.FieldType.RepresentedType;
        if(fieldType == typeof(string))
        {
            valueToEmit = "String.Empty";
        }
        if(fieldType==typeof(Guid))
        {
            valueToEmit = "Guid.NewGuid()";
        }
        if(fieldType==typeof(byte[]))
        {
            valueToEmit = "new byte[0]";
        }
        if(fieldType==typeof(DateTime))
        {
            valueToEmit = "DateTime.Now";
        }
%>          this.<%=field.Name%> = <%=valueToEmit%>;
<%  }
%>      }

or

        Public Overrides Sub  OnEntityCreated()
<%  Type[] typesToCheck = new Type[] {typeof(Guid), typeof(string), typeof(byte[]), typeof(DateTime)}; 
    foreach(var field in entityFields.Where(f=>!f.IsOptional && !f.IsPartOfIdentifyingFields && (f.FieldType.KindOfType==FieldTypeKind.DotNetType) &&
                                                typesToCheck.Contains(f.FieldType.RepresentedType)))
    {
        string valueToEmit = string.Empty;
        var fieldType = field.FieldType.RepresentedType;
        if(fieldType == typeof(string))
        {
            valueToEmit = "String.Empty";
        }
        if(fieldType==typeof(Guid))
        {
            valueToEmit = "Guid.NewGuid()";
        }
        if(fieldType==typeof(byte[]))
        {
            valueToEmit = "New Byte(0)";
        }
        if(fieldType==typeof(DateTime))
        {
            valueToEmit = "DateTime.Now";
        }
%>          Me.<%=field.Name%> = <%=valueToEmit%>
<%  }
%>      End Sub

for VB.NET

Save everything, by clicking the save all button. Click the 'refresh the code generation meta-data' button if the templatebindings editor still shows the filenames in red. This is the green circular button on the toolbar, or click Tools -> Refresh Code Generation Meta-data.

Done!

Generate code. You should now have in every generated entity a method called OnEntityCreated() which, if necessary, inits the non-nullable fields. Don't forget to create the virtual method in the partial class of CommonEntityBase!

Otis wrote:

You should now have in every generated entity a method called OnEntityCreated() which, if necessary, inits the non-nullable fields.

You're my hero! Seriously, thanks a million for putting this together. Works like a charm for all the non null fields with blank string defaults, of which I have a ton.

OK so now my minor gripe .....

Because this doesn't take into account the actual default values from the database, it leads to incorrect data in the case where the database field has a non-blank default value. In that case, this new code will init the field to "" which then overrides the database default value. This would also affect any fields that had DB functions as their defaults (except GETDATE which would actually happen to work correctly by chance since this new code inits it to DateTime.Now).

So my question is, a) is it even possible to cover this scenario, and b) does that make this whole thing way more complicated?

pt wrote:

Otis wrote:

You should now have in every generated entity a method called OnEntityCreated() which, if necessary, inits the non-nullable fields.

You're my hero! Seriously, thanks a million for putting this together. Works like a charm for all the non null fields with blank string defaults, of which I have a ton.

Good

OK so now my minor gripe .....

Because this doesn't take into account the actual default values from the database, it leads to incorrect data in the case where the database field has a non-blank default value. In that case, this new code will init the field to "" which then overrides the database default value. This would also affect any fields that had DB functions as their defaults (except GETDATE which would actually happen to work correctly by chance since this new code inits it to DateTime.Now).

So my question is, a) is it even possible to cover this scenario, and b) does that make this whole thing way more complicated?

Yes it's possible. To do so, you have to obtain the mapping for the entity and then for the fields to process, you have to obtain the field mapping which contains the target field. That object contains the default constraint. This is doable by calling: var mapping = currentProject.GetGroupableModelElementMapping(entity, _executingGenerator.DriverID); then you can obtain the field mapping for entity field F: var fieldMapping = mapping.GetFieldMappingOfField(f);

which contains MappedTarget, the field in the table/view mapped. This has a default value string.

This is the easy part. The hard part is to interpret the string there to a meaningful .NET value. This is something you have to do yourself.

Cool. I was able to get at the default value via the MappedTarget.DefaultValue property as you suggested, only problem is it's always blank. I double checked and verified the field in the database has a non blank default value. Interestingly, if I look at the field through the catalog explorer, the Default Value column is blank there as well. It doesn't seem to be picking up any of the defaults from the database. Something I'm doing wrong?

Not all DB's offer the ability to obtain default values. SQL Server does offer them for example. If you go to catalog explorer and right click the 'Fields' node below the table you'd want to obtain the default values for, select 'show details' you can see the obtained default values.

Or execute the query: SELECT INFORMATION_SCHEMA.COLUMNS.* FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='<tablename' ORDER BY INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION ASC

on the catalog you're targeting in sql server managemnt studio.

The column COLUMN_DEFAULT has to have the column default value. I have verified that this works, I can see the default values for AdventureWorks' fields just fine in llblgen pro.

OK my mistake, I guess the sql login I was using didn't have the necessary permissions to read the defaults so it just skipped over that process without popping up any error. When I changed it to an admin user, the defaults came in correctly.