Why must I pass the current date in LINQ when I have a default constraint in my database?

[Ronald S. Cook]

I'm new to LINQ (to SQL) and just doing a simple insert.

This works fine:

_Diagnosi.DiagnosisName = DiagnosisNameTextBox.Text.Trim();
_Diagnosi.DiagnosisDescription = DiagnosisDescriptionTextBox.Text.Trim();
_Diagnosi.DiagnosisCreated = System.DateTime.Now();

COWFeedyardDataClassesDataContext dc = new
COWFeedyardDataClassesDataContext();
dc.Diagnosis.InsertOnSubmit(_Diagnosi);
dc.SubmitChanges();

However, I have default constraints on my database that will take care of
inserting the appropriate date, so I would like to not have to pass it in
each time. BUT, if I don't have that DiagnosisCreated line, then #12:00:00
AM# will be passed.

The result is error: "SqlDateTime overflow. Must be between 1/1/1753
12:00:00 AM and 12/31/9999 11:59:59 PM."

How can I not have to pass a date/time from the client in every instance?

Thanks very much for any assistance,
Ron

 

[Nicholas Paldino [.NET/C# MVP]]

Ronald,

There really isn't a good solution here. There is a flag on the Column
attribute, IsDbGenerated which causes the value to be loaded after an
update, but in this case, it doesn't really help you.

As far as I can tell, the only way to do this would be to have a class
that derives from DataContext (or your generated data context) and add a new
method which, when called, will omit the properties that are auto-generated
on insert.

Of course, it also means you have to override the methods which perform
the translation between the Expression and the SQL that is sent to SQL
Server, which is probably going to entail a good deal of work.

 

[Ronald S. Cook]

Wow, that's really a kluge work-around, no? I noticed that all of the
default constraints in the database are disregarded. I have a "newid()" as
one for the PK and it doesn't "kick in".

So does this mean that I have to consider all default constraints in my
database worthless and re-create the intended effect on my class in LINQ?

Thanks very much.

Ronald,

There really isn't a good solution here. There is a flag on the Column
attribute, IsDbGenerated which causes the value to be loaded after an
update, but in this case, it doesn't really help you.

As far as I can tell, the only way to do this would be to have a class
that derives from DataContext (or your generated data context) and add a
new method which, when called, will omit the properties that are
auto-generated on insert.

Of course, it also means you have to override the methods which perform
the translation between the Expression and the SQL that is sent to SQL
Server, which is probably going to entail a good deal of work.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I'm new to LINQ (to SQL) and just doing a simple insert.

This works fine:

_Diagnosi.DiagnosisName = DiagnosisNameTextBox.Text.Trim();
_Diagnosi.DiagnosisDescription = DiagnosisDescriptionTextBox.Text.Trim();
_Diagnosi.DiagnosisCreated = System.DateTime.Now();

COWFeedyardDataClassesDataContext dc = new
COWFeedyardDataClassesDataContext();
dc.Diagnosis.InsertOnSubmit(_Diagnosi);
dc.SubmitChanges();

However, I have default constraints on my database that will take care of
inserting the appropriate date, so I would like to not have to pass it in
each time. BUT, if I don't have that DiagnosisCreated line, then
#12:00:00 AM# will be passed.

The result is error: "SqlDateTime overflow. Must be between 1/1/1753
12:00:00 AM and 12/31/9999 11:59:59 PM."

How can I not have to pass a date/time from the client in every instance?

Thanks very much for any assistance,
Ron

 

[Nicholas Paldino [.NET/C# MVP]]

Ronald,

Yes, you could recreate the intended effect on your class in LINQ, but I
think that the data context is a better course, as it would be just as much
of a pain to recreate the logic for the default values (and get it done
correctly, because ultimately, the DB is creating the value, and for some
default constraints, you can't predict reliably what the value will be).

With the data context solution, I envision that you would extend your
objects to indicate for each property whether or not a value has been
assigned to it and should be inserted into the database. For some
columns/properties, you could use null (assuming the column is not null) but
for null columns, you need another way to indicate that the database should
provide the default value.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Wow, that's really a kluge work-around, no? I noticed that all of the
default constraints in the database are disregarded. I have a "newid()"
as one for the PK and it doesn't "kick in".

So does this mean that I have to consider all default constraints in my
database worthless and re-create the intended effect on my class in LINQ?

Thanks very much.

Ronald,

There really isn't a good solution here. There is a flag on the
Column attribute, IsDbGenerated which causes the value to be loaded after
an update, but in this case, it doesn't really help you.

As far as I can tell, the only way to do this would be to have a class
that derives from DataContext (or your generated data context) and add a
new method which, when called, will omit the properties that are
auto-generated on insert.

Of course, it also means you have to override the methods which
perform the translation between the Expression and the SQL that is sent
to SQL Server, which is probably going to entail a good deal of work.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I'm new to LINQ (to SQL) and just doing a simple insert.

This works fine:

_Diagnosi.DiagnosisName = DiagnosisNameTextBox.Text.Trim();
_Diagnosi.DiagnosisDescription =
DiagnosisDescriptionTextBox.Text.Trim();
_Diagnosi.DiagnosisCreated = System.DateTime.Now();

COWFeedyardDataClassesDataContext dc = new
COWFeedyardDataClassesDataContext();
dc.Diagnosis.InsertOnSubmit(_Diagnosi);
dc.SubmitChanges();

However, I have default constraints on my database that will take care
of inserting the appropriate date, so I would like to not have to pass
it in each time. BUT, if I don't have that DiagnosisCreated line, then
#12:00:00 AM# will be passed.

The result is error: "SqlDateTime overflow. Must be between 1/1/1753
12:00:00 AM and 12/31/9999 11:59:59 PM."

How can I not have to pass a date/time from the client in every
instance?

Thanks very much for any assistance,
Ron

 

[Ronald S. Cook]

Thanks Nicholas!

Ronald,

Yes, you could recreate the intended effect on your class in LINQ, but
I think that the data context is a better course, as it would be just as
much of a pain to recreate the logic for the default values (and get it
done correctly, because ultimately, the DB is creating the value, and for
some default constraints, you can't predict reliably what the value will
be).

With the data context solution, I envision that you would extend your
objects to indicate for each property whether or not a value has been
assigned to it and should be inserted into the database. For some
columns/properties, you could use null (assuming the column is not null)
but for null columns, you need another way to indicate that the database
should provide the default value.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Wow, that's really a kluge work-around, no? I noticed that all of the
default constraints in the database are disregarded. I have a "newid()"
as one for the PK and it doesn't "kick in".

So does this mean that I have to consider all default constraints in my
database worthless and re-create the intended effect on my class in LINQ?

Thanks very much.

Ronald,

There really isn't a good solution here. There is a flag on the
Column attribute, IsDbGenerated which causes the value to be loaded
after an update, but in this case, it doesn't really help you.

As far as I can tell, the only way to do this would be to have a
class that derives from DataContext (or your generated data context) and
add a new method which, when called, will omit the properties that are
auto-generated on insert.

Of course, it also means you have to override the methods which
perform the translation between the Expression and the SQL that is sent
to SQL Server, which is probably going to entail a good deal of work.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I'm new to LINQ (to SQL) and just doing a simple insert.

This works fine:

_Diagnosi.DiagnosisName = DiagnosisNameTextBox.Text.Trim();
_Diagnosi.DiagnosisDescription =
DiagnosisDescriptionTextBox.Text.Trim();
_Diagnosi.DiagnosisCreated = System.DateTime.Now();

COWFeedyardDataClassesDataContext dc = new
COWFeedyardDataClassesDataContext();
dc.Diagnosis.InsertOnSubmit(_Diagnosi);
dc.SubmitChanges();

However, I have default constraints on my database that will take care
of inserting the appropriate date, so I would like to not have to pass
it in each time. BUT, if I don't have that DiagnosisCreated line, then
#12:00:00 AM# will be passed.

The result is error: "SqlDateTime overflow. Must be between 1/1/1753
12:00:00 AM and 12/31/9999 11:59:59 PM."

How can I not have to pass a date/time from the client in every
instance?

Thanks very much for any assistance,
Ron

 

[Scott Roberts]

So does this mean that I have to consider all default constraints in my

database worthless and re-create the intended effect on my class in LINQ?

One could argue that setting defaults in the database has always been
worthless. That person might say that the whole idea of having a Business
Object is to put your Business Rules in there - and that includes default
values.