Hi,

How can I have my date fields be generated with .HasColumnType("datetime")?

Regards,

Joe

Which LLBLGen Pro Designer version are you using?

Which framework are you targeting? is it E.F.? which version?

Walaa wrote:

Which LLBLGen Pro Designer version are you using?

Which framework are you targeting? is it E.F.? which version?

Sorry, neglected to include that: Designer version: 5.11 (5.11.0) RTM Target Framework: EF Core v8

Regards,

Joe

We don't generate these in the ModelBuilder class as they're not needed at runtime and our designer does a better job at exporting DDL SQL. Is there a particular reason you need this btw? I'm afraid if you really want this, you'll have to use a customized ModelBuilder.lpt template. The function which determines which functions to append to a property mapping starts at line 409 (DeterminePropertyAppendCalls)

Otis wrote:

We don't generate these in the ModelBuilder class as they're not needed at runtime and our designer does a better job at exporting DDL SQL. Is there a particular reason you need this btw? I'm afraid if you really want this, you'll have to use a customized ModelBuilder.lpt template. The function which determines which functions to append to a property mapping starts at line 409 (DeterminePropertyAppendCalls)

Thanks for the prompt response.

The request relate to a problem we experienced using DevExtreme's DataSourceLoader. When we try to filter on a date field, we receive an exception "Conversion failed when converting date and/or time from character string". Adding the .HasColumnType is the suggested solution, which I've tried, and it worked.

The link to the DevExpress support ticket https://supportcenter.devexpress.com/ticket/details/t737816/datagrid-the-conversion-failed-when-converting-date-and-or-time-from-character-string which in turn reference this EF Core issue https://github.com/dotnet/efcore/issues/14095#issuecomment-464365442

From what I can see this would not be an issue if the MSSQL Data type is datetime2, though I have not tested this myself.

The github thread suggests the issue was in EF Core and they've fixed it, but apparently it's not fixed in your situation?

There's another way: generating a derived type of the generated context class and in there generate an override of OnModelCreating. First call the base method, and then generate for each entity type, and for each entity type for each datetime field, the HasColumnType("datetime"). You could do this using an adhoc template for that so you don't need templatebindings if you don't want to. If it's just a few fields, you can also manually write these out. It should look like this:

public class EFCore8NWDataContextDerived : EFCore8NWDataContext
{
    public EFCore8NWDataContextDerived(string connectionString) : base(connectionString)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Order>().Property(t => t.OrderDate).HasColumnType("datetime");
    }
}

Here I add the column type to the Order entity's OrderDate field. To use this, use the derived context class instead of the generated context class. I wanted to use the partial class method OnModelCreatingComplete, but we made a design error there and don't pass the ModelBuilder object to that partial method

If you have problems writing the template, please let us know.

I've changed the ModelBuilder.lpt to add the HasColumnType() as you suggested, and it solved the issue on my side.

Though I am worried that when there is a new version of the designer, it might override the .lpt file and thus I loose my changes (I might have gone about it wrong, but I've changed the file it its original install location?).

Which brings me to your suggestion of an "adhoc template". I like this more. Can you perhaps point me in the right direction as to where to write it and hook it up (even a link to docs or examples will help).

Changing the template in the designer is indeed not the best way, as an update will overwrite it. Additionally, using a copy of it, using your own template bindings file (so basically you use your copy of the template instead of the one shipped with the designer) might be better, but then too you won't take into account any changes we make to the template (as you're using your own copy).

So the best way to fix this I think is to generate the derived context class as I gave an example of above. You can do that using two ways. One being adhoc templates, see: https://www.llblgen.com/Documentation/5.11/Designer/Functionality%20Reference/AdHocTemplates.htm They're basically templates defined in a folder using a simple xml file which are run with every code generation action. When you write your derived class template in an adhoc template it should keep working when you update the designer. An alternative is to define a new template with a custom template bindings file (See: https://www.llblgen.com/Documentation/5.11/Designer/Functionality%20Reference/TemplateBindingsViewer.htm) but that's more steps and for a single template likely overkill due to the complexity involved.

I'll write an example template to generate the class above and post it in a separate post.

The constructor I emit in the adhoc template passes in the connection string to the constructor of the datacontext which I added in a partial class. If you don't do it that way, you can remove that constructor.

<[SD.Tools.BCLExtensions.CollectionsRelated]>
<%
    Project currentProject = _executingGenerator.ProjectDefinition;
%>//------------------------------------------------------------------------------
// <auto-generated>This code was generated by LLBLGen Pro v<%=ApplicationConstants.LLBLGenProVersion%>.</auto-generated>
//------------------------------------------------------------------------------
using System;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using <%=_executingGenerator.RootNamespaceToUse%>.EntityClasses;

namespace <%=_executingGenerator.RootNamespaceToUse%>
{
    public class <%=_executingGenerator.ActiveSourceElementsContainerName%>DataContextDerived : <%=_executingGenerator.ActiveSourceElementsContainerName%>DataContext
    {
        public <%=_executingGenerator.ActiveSourceElementsContainerName%>DataContextDerived(string connectionString) : base(connectionString)
        {
        }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
<%
    foreach(var entity in _executingGenerator.Entities.OrderBy(v=>v.FullName, ApplicationUtils.GetNameOrderingComparer()))
    {
        var mapping = currentProject.GetGroupableModelElementMapping(entity, _executingGenerator.DriverID);
        foreach(var fieldMapping in mapping.FieldMappings.Where(fm=>(!fm.MappedFieldInstance.IsDiscriminator && (fm.MappedFieldWrapper.Path.Count<=1)) || 
                    fm.MappedFieldInstance.IsPartOfIdentifyingFields).OrderBy(fm=>fm.MappedTarget.OrdinalPosition))
        {
            var field = fieldMapping.MappedFieldInstance as IFieldElementCore;
            if(field==null || field.FieldType.KindOfType!=FieldTypeKind.DotNetType)
            {
                continue;
            }
            if(field.FieldType.RepresentedType==typeof(DateTime))
            {
%>          modelBuilder.Entity<<%=entity.Name%>>().Property(t => t.<%=field.Name%>).HasColumnType("datetime");
<%          }
        }
    }
%>      }
    }
}

Using adhoctemplate file:

<adhocTemplates usedByModelType="1">
    <template filename="AppendHasColumnTypeToFields.lpt" outputFilename=".\Persistence\[containerName]DataContextDerived.[extension]" emitType="generic"/>
</adhocTemplates>

to enable the adhoc templates, double click the code generation task and at the bottom of the dialog that opens you can check the enable checkbox for the adhoc template file (after you reloaded the project).

You have to do that once, it's persisted with the project after a successful code generation run. The example above only emits fields with datetime and doesn't do specific checking on fk fields being omitted and the like but I think it's not applicable here anyway.

Thank you, Frans. Really appreciate the help.