linq to sql string property from non-null column with default

Question

I have a LINQ to SQL class "VoucherRecord" based on a simple table. One property "Note" is a string that represents an nvarchar(255) column, which is non-nullable and has a default value of empty string ('').

If I instantiate a VoucherRecord the initial value of the Note property is null. If I add it using a DataContext's InsertOnSubmit method, I get a SQL error message:

Cannot insert the value NULL into column 'Note', table 'foo.bar.tblVoucher'; column does not allow nulls. INSERT fails.

Why isn't the database default kicking in? What sort of query could bypass the default anyway? How do I view the generated sql for this action?

Thanks for your help!

Nick Craver · Accepted Answer · 2010-03-13 17:52:39Z

2

If you omit the column, the value becomes the database default, but anything you insert is used instead of the default, example:

INSERT INTO MyTable (ID, VoucherRecord) Values(34, NULL) -- Null is used
INSERT INTO MyTable (ID) Values(34) -- Default is used

Picture for example you have a column that defaults to anything but NULL, but you specifically want NULL...for that to ever work, whatever value you specify MUST override the default, even in the case of NULL.

You need to set Auto-Sync to OnInsert, Auto Generated Value to true and Nullable to false for your column to work. See here for a full run-down with explanation on the Linq side.

For viewing the generated SQL, I have to recommend LinqPad

edited Mar 13, 2010 at 17:52

answered Mar 13, 2010 at 17:47

Nick Craver

631k138 gold badges1.3k silver badges1.2k bronze badges

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

7 Comments

James Orr Over a year ago

I see what you mean. But if I set my Note column to AutoGenerated = true, I can't set its value, which is bad. I get the error " Value of member 'Note' of an object of type 'VoucherRecord' changed. A member that is computed or generated by the database cannot be changed."

James Orr Over a year ago

And if I only set AutoSync to Insert without setting AutoGenerated to True, I return to the original error message. Hmm!

Nick Craver Over a year ago

@Barry - Do you have Nullable set to false in your Linq model?

James Orr Over a year ago

Yes, Nullable is set to false.

Nick Craver Over a year ago

@Barry - Try this partial to go with the same class: public partial class VoucherRecord { partial void OnCreated() { Note = Note ?? string.Empty; } } Leaving AutoGenerated set to false

|

Collectives™ on Stack Overflow

linq to sql string property from non-null column with default

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related