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
– Magnus Grindal Bakken, Commented Oct 2, 2013 at 9: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
– Marc Gravell, Commented Oct 2, 2013 at 9:23
possible duplicate of Can LINQ-to-SQL omit unspecified columns on insert so a database default value is used? — James
– James, Commented Oct 2, 2013 at 9: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
– neo112, Commented Oct 2, 2013 at 9: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
– James, Commented Oct 2, 2013 at 9:38

Adween · Accepted Answer · 2013-10-02 09:48:16Z

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 2, 2013 at 9:48

Adween

2,8202 gold badges20 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

neo112 Over a year ago

Defining the date in the default constructor works well. Would upvote if I could.

Rom Eh Over a year ago

It is a good solution, but you still have to be careful with SqlDateTime.MinValue.

Rom Eh · Accepted Answer · 2013-10-02 10:13:21Z

0

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.

edited Oct 2, 2013 at 10:13

answered Oct 2, 2013 at 9:32

Rom Eh

2,0711 gold badge17 silver badges34 bronze badges

2 Comments

neo112 Over a year ago

I tried this public System.DateTime ? dob { get; set; } but without specifying the person.dob attribute I get an EntityValidationError

Rom Eh Over a year ago

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 ?

AgentFire · Accepted Answer · 2013-10-02 10:54:08Z

-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 2, 2013 at 10:54

answered Oct 2, 2013 at 9:22

AgentFire

9,8398 gold badges46 silver badges98 bronze badges

6 Comments

RB. Over a year ago

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.

Marc Gravell Over a year ago

from the question: "I've tried adding Column(IsDataBaseGenerated=true)"

neo112 Over a year ago

using System.Data.Linq.Mapping; -> The type or namespace 'Linq' does not exist in the namespace 'System.Data'

AgentFire Over a year ago

@neo112 try peeking here.

neo112 Over a year ago

@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?

|

Collectives™ on Stack Overflow

LINQ to SQL, not null, use default value

3 Answers 3

2 Comments

2 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related