This is a migrated thread and some comments may be shown as answers.

ORM Handling of SQL Default Values and Nulls

36 Answers 592 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

This question is locked. New answers and comments are not allowed.

John asked on 02 Mar 2009, 01:41 AM

I have a SQL Server Database and one of my tables has an column type of uniqueidentifier with a default value of newid() and allow nulls set to true on the column.

Issue:

When adding a new record using ORM and not filling out a uniqueidentifier column, the record gets inserted into the database and a null is inserted into the database even when sql server has a default value defined. What should happen is sql should insert the default newid() like in the case of records a,c,& d below. ORM seems to be passing something other than null to sql. Record C is the inserted record that ORM added. all other records where inserted null using a different tool other than ORM.

A	df46590b-f7c4-47bf-90e4-ebf2409623e4	1	True	Apple	2009-02-18 23:24:15.963
B	e53c92c8-bdd5-42b8-ae29-88812f8c8b74	1	True	Big	2009-02-18 23:29:13.033
C	NULL	1	NULL	Cat	NULL
D	f8923ced-6d67-4a52-bbe8-3f4339296564	1	NULL	Dog	2009-03-01 20:15:07.130

<Telerik.OpenAccess.FieldAlias(

"_sourceGUID")> _

Public Property SourceGUID() As Nullable(Of Guid)

Get

Return _sourceGUID

End Get

Set(ByVal Value As Nullable(Of Guid))

Me._sourceGUID = Value

End Set

End Property

36 Answers, 1 is accepted

answered on 29 Oct 2010, 05:35 PM

Hello Daniel,

Currently the only way to work with default values would be to write some additional code that sets it in the constructor of the entity. This way you will not have to set the field to calculated and you will be able to remove the additional set in the code. We do plan to improve this in the future however I cannot provide you with an exact time frame.

Regards,
Petar
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items

answered on 17 Dec 2010, 02:25 AM

I too need to use default values on the server and was wondering how Open Access generates the insert statement. It looks like it is very inefficient if it always inserts a value into every field. If a default is changed in the database, we don't want to have to recompile the whole system and deploy new DLL's (not to mention that the system could fall over if we didn't make the code change). We also don't want to have to manage the defaults in code and in the database - that would be a recipe for disaster!

In other ORM's that we've used they internally have a dirty flag for each field which means that any inserts or updates are only applied to fields that have actually changed or have new values. There's no point in generating an insert statement consisting of all fields for a table that has, say, 30 fields if I have only added a value for one of the fields. If a Customer table has 30 fields and I insert a new record, for example setting just the 'FirstName' field, then the corresponding update should be something like INSERT INTO Customer(FirstName) VALUES('JOHN')

I noticed this post has been ongoing for a long time and need to know if this is something that will be implemented soon (or at least before I retire :-)

answered on 15 Apr 2011, 03:52 PM

Hi Patar,

I too am amazed that this is a low priority for Telerik. We are regularly asked to use existing databases and cannot guarantee that they don't have any default values set in any of the tables. Also, what happens if we develop a system and someone adds a new column to a table and sets a default value for that field. With Open Access it looks like we'd screw the system up and have to look through all of our code for places that were inserting new records into the table, make the necessary changes to the code, recompile and redeploy.

Also we have a real world situation whereby we want people to sign up to a web site by only entering their name, email address and password. The table we use has 25 columns and our columns have defaults and do not allow null values (e.g. CurrencyCode column defaults to 'GBP') - do we have to write code to enter default values into the other 22 columns?

If we create a new record in Open Access and only insert the 3 fields mentioned above, what SQL is generated?

Should it not be the following:

INSERT INTO Customer(Name, Email, Password) VALUES('myName', 'emailAddress', 'myPassword')

What code would Open Access create?

answered on 20 Apr 2011, 09:54 AM

Hi RDNZL,

There is a setting in the dsl designer that instructs that a field will get a default value from the database. You can use it when you are doing your inserts however modifying the values later will not be available. The only real workaround would be to manually add those properties to your default constructor so that your objects gets initialized with the correct values. We will re-discuss the priority of this item in our team and perhaps we will start implementing a possible solution for one of our next service packs.

Best wishes,
Petar
the Telerik team

answered on 13 Oct 2011, 03:42 PM

Hi Guys,

I would like to shed some light on how this issue is progressing.

We have discussed and analyzed it, without underestimating the severity of the problem. Considering the growing interest in the issue raised in PITS, we have decided to implement some solution to allow automated usage of default values defined in the database. Due to the heavy development needed to introduce such functionality, I will be introduced in Q1 2012.

Until then, please accept our apologies for the inconvenience caused. The temporary workaround as described is to set the default values manually in a partial class constructor (not in the auto-generated class, since it might be replaced).

I will post any news that we have on this feature in this thread and in PITS.

Kind regards,
Ivailo
the Telerik team

Check out the latest stable build of Telerik OpenAccess ORM. Download it and benefit from our new Project Templates.

answered on 06 Dec 2011, 11:49 AM

Hi Steve,

Unfortunately the workaround is not applicable to the OpenAccessLinqDataSource. The workaround suggests that you create a partial class to your class that is mapped to a table with default values and in that partial class create a constructor that would assign the default values on each entity create. While the workaround works in most cases it will not work for the data source because the data source always uses the parameterless constructor and that cannot be overwritten.

All the best,
Petar
the Telerik team

Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

answered on 24 Feb 2012, 02:22 PM

Hi Keith,

We have been waiting for this to be implemented for over 2 years so I presume this is a low priority for the Open Access team. The ORM is unfortunately not very good at producing efficient SQL code and from what I can gather attempts to insert NULL's into fields that you have not explicitly entered data for. We use the Entity Spaces ORM which allows us to have fields with default values e.g. our Customer table has over 20 columns, some of which have default values e.g. the SignUpDate column defaults to "getUTCDate()" - to add a quick signup from a web page we have:

var customer = new Customer();
customer.FirstName = "Kevin";
customer.LastName = "Farrow";
customer.EmailAddress = "kevin@revisionsoftware.com";
customer.Save();

the SQL produced is:

INSERT INTO Customer(FirstName, LastName, EmailAddress) VALUES('Kevin', 'Farrow', 'kevin@revisionsoftware.com')

with no silly workarounds!!!!

Kind Regards,
Kevin Farrow

answered on 01 Mar 2012, 10:28 AM

Hello,

We agree that this development took too long and we have to think about releasing the support for default values. Unfortunately no ORM can offer you all the features in the same time and there will always be some limitations. We are going to try bringing those limitations down to minimum in 2012 as this is one of our goals for this year.

We strongly believe that Telerik OpenAccess ORM is a mature product that can really help you in the process of creating your Data Access Layer, but of course you are free to choose the most suitable solution for your particular scenario. I hope that you will find plenty of other useful features in OpenAccess that cannot be found in other similar products - such as the Visual Designer, support for a huge list of databases, the Batch Operations dialog, Profiler, automated generation of Stored Procedure methods, etc.

We will keep the PITS item for the default values updated so those of you that need the default values feature will be notified as soon as we plan its release.

Greetings,
Ivailo
the Telerik team

Telerik OpenAccess ORM Q1 2012 release is here! Check out what's new or download a free trial >>

answered on 25 Jun 2012, 11:47 AM

Could you please explain why the columns have to be nullable in order to use default values?

In order for Open Access to be a viable solution we need to use it against existing databases and not have to make any changes.

For example, we have a column called Gender in our Customer table that we don't ever want to be null. We also set '3' as the default value (possible values are: 1=Male, 2-Female, 3=Unknown). This business rule means that no one should ever be able to add a new Customer record without setting this column, either from an application OR by importing records OR by directly inserting using SQL.

Consider the following code that creates a new record into a table that has 4 columns, CustomerId (auto generated identity), FirstName, LastName, Gender:

Customer customer = new Customer();
customer.FirstName = 'Kevin';
customer.LastName = 'Farrow';
customer.Save();

then I would expect your code to generate the following SQL (or something similar)

INSERT INTO Customer(FirstName, LastName) SELECT 'Kevin', 'Farrow'

From the error message 'Cannot insert the value NULL into column' it appears you are trying to insert a value into the Gender field even though the above code hasn't referenced that field. If the Customer table has 50 columns the that means you update ALL 50 columns on each insert or update which would be very inefficient.

answered on 27 Jun 2012, 11:50 AM

Hello Kevin,

Consider the following situation:
You have an integer field in your database that has a default value of 10. You are creating a new instance of the type holding the integer field and you are not setting this field (the field is initialized to 0 from the constructor). Later on you are inserting that instance in the database. What you would expect is that the value of 10 is inserted in the column corresponding to the integer field. Now consider the same scenario but you explicitly set the value of 0 to the integer field and then insert it. Normally you would expect that the default value would be omitted and a 0 will be inserted.
Here is where the actual problem begins. Currently OpenAccess does not have means to tell the difference between a zero that has been set by the constructor and a 0 that has been explicitly set. That is why we only support default values for fields that are nullable. This way you will still be able to set 0 to an integer field and the null would mean that the field was not initialized.

Kind regards,
Petar
the Telerik team

OpenAccess ORM Q2'12 Now Available! Get your hands on all the new stuff.