LINQ to SQL Instantiation
page 4 of 8
by Brian Mains
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 43823/ 76

Default Values

Every Database Management System supports default values; whenever a field is left out of an insert statement, the database automatically inserts a default for that row/column. This saves some time, especially when entering data manually into the database. However, there is a challenge with this: default values do not seem to work well in LINQ. Let me explain.

Suppose you have a column (CreatedDate) in the database system that uses the datetime SQL Server data type. This translates to DateTime in the .NET framework, and is required and has a default value of getdate(). The challenge is this: by default, a DateTime value is represented as a value of "1/1/0001" in the .NET framework. This is a valid date and the database system tries to supply this date to SQL Server in the insert.

SQL Server rejects this value because the created date should be a value between 1/1/1753 and 12/31/9999 for the datetime type, which causes an exception and unfortunately no details about the business object type name or property name are provided, meaning that someone may have a flashback of the COBOL days in trying to hunt down a missing punctuation.

With that said, when instantiating business objects, you have to supply the required properties to the object, even though default values are already specified. Identities are an exception because this is a different construct, and LINQ to SQL handles this accordingly. However, if you use GUID's with your tables as the primary key, you have to call Guid.NewGuid() manually to create an ID.

Listing 5

order.OrderKey = Guid.NewGuid();
order.CreatedDate = DateTime.Now;

In a sense, this is better than using an integer identity value because the primary key value is immediately known, and it is not as necessary to call the context's SubmitChanges() method right away.

View Entire Article

User Comments

Title: Another way to do this...?   
Name: Funka!
Date: 2009-08-14 5:17:13 PM
Another possibility it seems is to create a partial class and partial method "OnCreated" ?
For example:
public partial class Order
partial void OnCreated()
_OrderKey = Guid.NewGuid();
_CreatedDate = DateTime.Now;
This way, you don't need to keep remembering to do this every time you instantiate an object.

It should also be noted that you avoid your specific error (but not the general need for setting default values) by setting the LTS entity property's "Auto Generated Value" to true --- LTS will know not to supply a value to these columns when inserting, so you can just let SQL server fill in these defaults.

Title: Great Article !!!   
Name: Jash
Date: 2009-04-13 3:24:14 PM
Thank you Brian really useful article.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2022  |  Page Processed at 2022-08-15 5:59:22 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search