CODEWITHSUNDEEP
c#entity-frameworklinq-to-sql
neo112
neo112

Reputation: 1773

LINQ to SQL, not null, use default value

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?

Upvotes: 3

Views: 6252

Answers (3)

AgentFire
AgentFire

Reputation: 9780

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.

Upvotes: -1

Rom Eh
Rom Eh

Reputation: 2063

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.

Upvotes: 0

Adween
Adween

Reputation: 2820

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;}

Upvotes: 2

Related Questions