LINQ to SQL Basics – Part 1 (Bug?)

I have recently made the jump into LINQ and experienced allot of hours of headaches and pains but working through them. Few things that use to be a drag just seem so much easier now. But more of that in later posts. Let me start off with the basics.

The example in this post is using the Northwind database and executed using LINQPad

Simple things don’t seem so simple anymore using LINQ (hmmm didn’t I just say LINQ makes things easier?).

Let’s consider the following LINQ statement:

Customers ct = new Customers { CustomerID = “AAAAA”, CompanyName = “Aaaaaa” };
Customers.InsertOnSubmit(ct);
SubmitChanges();

Straight forward, we create a new customer with CustomerID = “AAAAA” and CompanyName = “Aaaaa”. Set so that when we submit the changes that the customer is inserted. Then submit the query to the database. The following SQL is created to do the insert:

INSERT
INTO
[Customers]([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10)

– @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [AAAAA]

– @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Aaaaaa]

– @p2: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p3: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p4: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p5: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p6: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p7: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p8: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p9: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p10: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

Not too shabby you might think. Now let’s complicate things a bit. Run the following script on your database:

ALTER
TABLE dbo.Customers ADD

CreateDate datetime
NOT
NULL
CONSTRAINT DF_Customers_CreateDate DEFAULT
getdate()

GO

ALTER
TABLE dbo.Customers SET (LOCK_ESCALATION
=
TABLE)

GO

This script creates a new column on the Customers table called CreateDate with a default of getdate.

Ok, now run the below insert script on your SQL database:

insert
into Customers(CustomerID, CompanyName)

values(‘BBBBB’,
‘Bbbbb’)

go

Cool!! (1 row(s) affected) What else did you suspect? J

Let’s do the same in LINQ… Run the below statement in LINQPad:

Customers ct = new Customers { CustomerID = “CCCCC”, CompanyName = “Ccccc” };
Customers.InsertOnSubmit(ct);
SubmitChanges();

Mmmmm!!! What this?!?!… It doesn’t work…

The following error is displayed:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

Let’s have a look at the SQL generated:

INSERT
INTO
[Customers]([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax], [CreateDate])

VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11)

– @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [CCCCC]

– @p1: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Ccccc]

– @p2: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p3: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p4: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p5: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p6: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p7: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p8: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p9: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

– @p10: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [Null]

The createdate field is also included in the insert statement and no value for @P11. This is a bit weird! LINQ up to its many tricks again, which it has up its sleeve. But let’s go on and see what should be done to get past this.

So all we do is basically just included createdate in the LINQ statement:

Customers ct = new Customers { CustomerID = “CCCCC”, CompanyName = “Ccccc”, CreateDate = Datetime.Now };
Customers.InsertOnSubmit(ct);
SubmitChanges();

Now it’s hunky-dory!

I did a bit of further investigation. Created a new field (Varchar(50), Nullable, Default value=”me”). Did an insert from SQL and the default value was set, but from LINQ the field stayed null. So from this I came to the following conclusion: LINQ ignores all the default value’s and binding’s setup on your database!

Hopefully someone might be able to prove me wrong on this. Am I missing something?

Advertisement