Hi!

MSSQL 2008, LLBLGEN 4.6, target Linq2Sql, C#

Table definition

CREATE TABLE [dbo].[myTable](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [UserId] [int] NOT NULL,
  [Date] [datetime] NOT NULL,
CONSTRAINT [PK_idmp_Submission] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY]) ON [PRIMARY]

ALTER TABLE [dbo].[myTable] ADD  CONSTRAINT [DV_myTable_Date] DEFAULT (sysutcdatetime()) FOR [Date]

Created property:

/// <summary>Gets or sets the Date field. Mapped on target field 'Date'. </summary>
[Column(Name="Date", Storage="_date", CanBeNull=false, DbType="datetime NOT NULL")]
public System.DateTime Date
{
  get { return _date; }
  set
  {
    if((_date != value))
    {
      OnDateChanging(value);
      SendPropertyChanging("Date");
      _date = value;
      SendPropertyChanged("Date");
      OnDateChanged();
    }
  }
}

Unfortunately the default value does not get written to the database. I tried setting the property in the entity to readonly, which results in no setter is created for the property, but still the wrong value is written to the table (0001-01-01 00:00:00.000000) and so an error is thrown: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

If I manually add an entry to the table, the correct current DateTime value (UTC) is written.

I'd like to NOT edit the generated source code manually but somehow edit the LLBLGEN project setting to properly NOT write a value at all to the table, but let the database do it's job writing the default value.

One problem seems to be in the generated code: eventhough the column has a default value, the attribute IsDbGenerated is missing. If this is added, the field is set correctly.

Any hint on that?

Do you mean LLBLGen 4.2?

Walaa wrote:

Do you mean LLBLGen 4.2?

Sorry. Yes, of course. Version 4.2 (May 5th, 2015)

What is generated SQL for the insert when using LLBLGen?

That Insert starement, What happen when you run it directly on DB?

The code in question is run as unit test:

[TestMethod]
public void TestAutoGenColumn()
{
    string msgnum = DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss.ffff");
    using (var context = IDMP.Create(s_ConnectionString))
    {
        var status = context.TransmissionStatuses.FirstOrDefault();
        var submission = new Submission() {As2Receiver = "rec", As2Sender = "snd", MessageNumber = msgnum, TransmissionStatus = status};
        context.Submissions.InsertOnSubmit(submission);
        context.SubmitChanges();
    }
    using (var context = IDMP.Create(s_ConnectionString))
    {
        var submission = context.Submissions.FirstOrDefault(sub => sub.MessageNumber == msgnum);
        submission.Date.Should().NotBe(DateTime.MinValue);
        Console.WriteLine(submission.Date);
    }
}

At the moment, the test failes at context.SubmitChanges with the message: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

The app.config has the trace switches included:

<system.diagnostics>
    <switches>
        <add name="SqlServerDQE" value="4" />
        <add name="ORMGeneral" value="4" />
        <add name="ORMStateManagement" value="4" />
        <add name="ORMPersistenceExecution" value="4" />
        <add name="LinqExpressionHandler" value="4" />
    </switches>
    <trace autoflush="true" indentsize="2">
        <listeners>
            <add name="textWriterListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="J:\LLBLGEN_Trace.log" />
            <remove name="Default" />
        </listeners>
    </trace>
</system.diagnostics>

Nevertheless I do not get any trace output. What am I missing?

It's actually an issue in Linq 2 SQL. I ran into it when I was refactoring my fork of Linq to Sql's sourcecode. See: https://github.com/FransBouma/LinqToSQL2/issues/14 (and the stackoverflow question linked there)

The only way to overcome this is to set the field to a value.

You don't get any trace output as those are for our own runtime framework, not for linq to sql. If you want to get linq to sql trace output, you should use the output writer on the context, which you can write to stdout.

Like: var writer = new StringWriter(); ctx.Log = writer;

and then do writer.ToString(), or grab the string writer of stdout and pass that.

Ok. Thanks. So I can stop using default SQL values and make sure the desired/required value is set in code.

Not great, but I'll blame Linq2SQL then

Workaround question: can I somehow get LLBLGEN to add the IsDbGenarated attribute to certain fields? Because then it works.

At the moment that's not supported. Reading up on this property of the Column attribute, I see we missed this usage for IsDbGenerated. We do generate IsDbGenerated for a category of columns, e.g. identity, RowGUIDs, computed columns etc., but not columns which have a default value defined and are otherwise normal columns, and we don't offer a setting to force the generated to emit this flag.

We've scheduled a change for this, for v5, with a setting so it's easy to define in bulk and per field. If you want to enforce this attribute on some fields it's only doable if you alter both sd.frameworks.linqtosql.frameworksettings file and the template.

If you really need it and can't continue otherwise, we'll make the change now, otherwise it's postponed to v5.0.

Otis wrote:

At the moment that's not supported. Reading up on this property of the Column attribute, I see we missed this usage for IsDbGenerated. We do generate IsDbGenerated for a category of columns, e.g. identity, RowGUIDs, computed columns etc., but not columns which have a default value defined and are otherwise normal columns, and we don't offer a setting to force the generated to emit this flag.

We've scheduled a change for this, for v5, with a setting so it's easy to define in bulk and per field. If you want to enforce this attribute on some fields it's only doable if you alter both sd.frameworks.linqtosql.frameworksettings file and the template.

If you really need it and can't continue otherwise, we'll make the change now, otherwise it's postponed to v5.0.

After thinking about both, the workaround and the offer to get a change in LLBLGEN, I'm not sure whether this workaround is totally correct. Especially for DateTime if it's a not null field with a database generated default value (which still means it could be changed).

The field is meant to be a read-only field marking the creation of the db entry. If I used a trigger, would that reflect correctly as a read-only field using LLBLGEN? Cause that's what I basically want to achive.

If you mark the field as an computed field in the table schema, the field will be seen as a computed field and will get the IsDbGenerated flag set (and ling to sql will retrieve the value after insert)

Implemented in v5.0 (field setting)