LINQ to SQL, not null, use default value

Question

I'm using EF with DB First. My table looks like this:

CREATE TABLE [dbo].[Person](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NOT NULL,
    [dob] [datetime] NOT NULL 
    CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED )
GO
ALTER TABLE [dbo].[Person] 
    ADD  CONSTRAINT [DF_Person_dob]  DEFAULT ('12.12.3000') FOR [dob]

After using DB First I get the following partial Person class:

using System;

public partial class Person
{
    public int id { get; set; }
    public string name { get; set; }
    public System.DateTime dob { get; set; }
}

The problem I have is that when I want to use the DB generated default value for 'dob' field I get an exception because nulls are not allowed. I'm trying to do this:

 using (var db = new NullTestEntities())
 {
     var person = db.Person.Create();
     person.name = "John Doe";
     db.Person.Add(person);
     db.SaveChanges();
 }

I've tried adding

 Column(IsDataBaseGenerated=true)

but I can't compile because I get an error:

'System.ComponentModel.DataAnnotations.Schema.ColumnAttribute' does not contain a definition for 'IsDatabaseGenerated'

What do I need to do in order to not define the dob attribute in C# code and let the database generate the default value?

Linq To Sql and Entity Framework are completely separate technologies. The "query engine" in Entity Framework that translates Linq to SQL is called "Linq To Entities". You should remove the references to Linq To Sql, or they may cause confusion. — Magnus Grindal Bakken, Oct 02 '13 at 09:23
Firstly, I suspect most ORMs are going to want to do the same thing here (store it with a `null`, because *that is what the object states*); secondly, if it *was* configurable, it would be different between linq-to-sql and entity-framework (tags), and thirdly: why oh why oh why would you want a default value of `12.12.3000` ? What *possible* use is that? How is that preferable to `null` ? — Marc Gravell, Oct 02 '13 at 09:23
possible duplicate of [Can LINQ-to-SQL omit unspecified columns on insert so a database default value is used?](http://stackoverflow.com/questions/2555206/can-linq-to-sql-omit-unspecified-columns-on-insert-so-a-database-default-value-i) — James, Oct 02 '13 at 09:30
Maybe it would make more sense if the dob field would be named 'closed'. But as I understand it's better to use dates in far future as defaults rather than nulls because it will make querying faster in large datasets. We know that a person file is open if the closed date is greater than the current date. Correct me if I'm wrong. — neo112, Oct 02 '13 at 09:31
@neo112 that sounds like a micro-optimization to me... I'd rather use a default that makes more sense and *slightly* slower than one that's slightly faster but makes no sense. — James, Oct 02 '13 at 09:38

score 2 · Accepted Answer · answered Oct 02 '13 at 09:48

2

Are you saying that you want the dob field to allow nulls or that you want the dob to be a defaul value?

if it is default value the i think you should pop the following to set the date in the person class

public partial class Person
{
    public Person(){
        dob = DateTime.Now; // or whatever
    }

    public int id { get; set; }
    public string name { get; set; }
    public System.DateTime dob { get; set; }
}

if you are saying that you want to allow nulls then change the dob to be

public Nullable<DateTime> dob {get;set;}

or

public DateTime? dob {get;set;}

answered Oct 02 '13 at 09:48

Adween

2,792
2
18
20

Defining the date in the default constructor works well. Would upvote if I could. – neo112 Oct 02 '13 at 09:58
1

It is a good solution, but you still have to be careful with SqlDateTime.MinValue. – Rom Eh Oct 02 '13 at 10:08

Rom Eh · Answer 2 · 2013-10-02T10:13:21.503

In fact, that the .Net DateTime and the SQL datetime types are not fully compatible. The min datetime in .Net is 0000/01/01, whereas in SQL the min datetime is 1753/01/01.

You can try to use the datetime2 SQL type instead of datetime : http://msdn.microsoft.com/en-us/library/bb677335.aspx. With this type, the min date is the same as the .Net one.

You can also try a workaround on your dob field, by specifying an Nullable DateTime. (DateTime?). When you instantiate your object, the DateTime.Min is automatically set to your dob field in the Person class. In this cas, you have to authorize NULL values in your database.

You finally can use a pattern like that :

private DateTime _dob;

// Field bind to your DataModel in the EDMX
private SqlDateTime coreDob
{
    get
    {
        if (_dob < SqlDateTime.MinValue)
            _dob = (DateTime)SqlDateTime.MinValue;

        return _dob;
    }
    set
    {
        if (value < SqlDateTime.MinValue)
            _dob = (DateTime)SqlDateTime.MinValue;
        else
            _dob = (DateTime)value;
    }
}

// Field use in your code
public DateTime dob
{
    get { return _dob; }
    set
    {
        if (value < SqlDateTime.MinValue)
            _dob = (DateTime)SqlDateTime.MinValue;
        else
            _dob = value;
    }
}

This kind of pattern prevents overflows error with SQL dates.

I tried this `public System.DateTime ? dob { get; set; }` but without specifying the person.dob attribute I get an EntityValidationError — neo112, Oct 02 '13 at 09:49
Yes it was preditable, because your field is a not null. I will correct my answer in a few minutes. Can you try the datetime2 please ? — Rom Eh, Oct 02 '13 at 09:51

AgentFire · Answer 3 · 2013-10-02T10:54:08.007

-1

Try adding Column(IsDataBaseGenerated=true) to the id column.

Also, you have to set dob property before insertion since it is NOT NULL in sql database.

Edit:

Since you are using the EF over the Linq to Sql, the IsDataBaseGenerated property is not supported the way you expecting it to be, instead, there is a great article describing the workflow with database generated properties.

edited Oct 02 '13 at 10:54

answered Oct 02 '13 at 09:22

AgentFire

8,944
8
43
90

1

He's specifically asking how he avoids having to set dob property before insertion. He has a default value set in the database, and would like that to be used. – RB. Oct 02 '13 at 09:23
2

from the question: "I've tried adding `Column(IsDataBaseGenerated=true)`" – Marc Gravell Oct 02 '13 at 09:23
`using System.Data.Linq.Mapping;` -> The type or namespace 'Linq' does not exist in the namespace 'System.Data' – neo112 Oct 02 '13 at 10:04
@neo112 try peeking [here](http://social.technet.microsoft.com/wiki/contents/articles/3825.entity-framework-faq-entitykey.aspx#Does_the_Entity_Framework_support_server-generated_key_values). – AgentFire Oct 02 '13 at 10:30
@AgentFire thank you for that link. I set the dob value to 'computed', this works well when I dont specify the dob on the Person instance before db.SaveChanged. However, it also uses the DB generated default values when I do specify a dob. So, when I set dob field to '01.01.2000' and do db.SaveChanged, the value that gets inserted to the DB is still '12.12.3000'. Is there something I'm missing here? – neo112 Oct 02 '13 at 11:05
@neo112 yes, you might miss the auto-generation of the models and their properties. In `linq-to-sql` autogeneratad model, for example, a property type is `DateTime`, not nullable, and the engine truely knows if you set the property or not. – AgentFire Oct 02 '13 at 11:19

LINQ to SQL, not null, use default value

3 Answers3