ORM Handling of SQL Default Values and Nulls

37 posts, 0 answers
  1. John
    John avatar
    2 posts
    Member since:
    Dec 2008

    Posted 01 Mar 2009 Link to this post

    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

     

     

  2. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 02 Mar 2009 Link to this post

    Hi John,
    OpenAccess does not support server calculated values during insert at the moment. The only place where this can be used is if the field is the identity column. Is you column the pk column or a normal data column?

    The workaround is to set the Guid before you persist the new object. It doesn't matter if you do it on the client or the server .

    Kind regards,
    Jan Blessenohl
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. chris_cf
    chris_cf avatar
    53 posts
    Member since:
    Jul 2007

    Posted 29 Mar 2009 Link to this post

    Hi,
    If I do not specify a Guid for the newly created object and add it to the db, it adds the PK column (Guid) record as 00000000-0000-0000-0000-000000000000. I have the PK column set to (newid()) which will generate a new Guid on every new record insert.

    How do I get OA to recognize this and not insert the Guid with all zeros?
  4. Robert
    Robert avatar
    40 posts
    Member since:
    Jul 2008

    Posted 30 Mar 2009 Link to this post

    Hey There,

    Try setting the Key Generator mode to GUID for your pkey column in the forward mapping wizard class mapping settings.  That tells orm to generate the Guid for you.

    Hope this helps,
    Robert
  5. chris_cf
    chris_cf avatar
    53 posts
    Member since:
    Jul 2007

    Posted 30 Mar 2009 Link to this post

    Hi Robert,

    Thanks, but I am using reverse mapping so this will not work. Any other suggestions?
  6. Robert
    Robert avatar
    40 posts
    Member since:
    Jul 2008

    Posted 30 Mar 2009 Link to this post

    That's ok, I'm using reverse mapping as well but OpenAccess uses some of the configuration in forward mapping to know what to do.  Try and see what happens :-)
  7. chris_cf
    chris_cf avatar
    53 posts
    Member since:
    Jul 2007

    Posted 30 Mar 2009 Link to this post

    Thanks, I'll give this a try. I already switched the PK to int and it's auto incrementing. So, I will give it a shot on another table and let you know if it worked.
  8. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 03 Apr 2009 Link to this post

    Hello chris_cf,
    Roberts suggestion is exactly the right way to do it. Sometimes you have to use the Forward Wizard in the reverse scenario as well.

    Key generator is one example, the other is FetchPlans. You specify them forward to the xml file and the reverse process will just keep them.

    Best wishes,
    Jan Blessenohl
    the Telerik team

    Check out Telerik Trainer , the state of the art learning tool for Telerik products.
  9. John
    John avatar
    2 posts
    Member since:
    Dec 2008

    Posted 01 Jun 2009 Link to this post

    I was just getting back to you on the issue of not supporting calculated values on the insert.
    Its currently a show stopper for us because we support some business logic at the db level to handle our batch processing, not all of our business processes/logic are in application code, and don't want to maintain it in two places
    With that said, Is there plans to support this on the next release this summer?

    thanks

    john
  10. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 03 Jun 2009 Link to this post

    Hello John,
    Can  you give us a concreate example? Which values do you want to set on server side? Are these additional fields, what do they have to do with the persistent object?

    Thanks,
    Jan Blessenohl
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  11. Geoff Davis
    Geoff Davis avatar
    11 posts
    Member since:
    Oct 2009

    Posted 03 Feb 2010 Link to this post

    Hi Jan,

    I'm not sure if this has been resolved but I want to be able to use a default value in SQL Express 2008 for getutcdate(). It's vital that the database on my website generates this value on new records because my frontend db server is in the US and my backend db server is in the UK. I then use the utc date without having to take the timezone into consideration.

    Other ORM's i've used in the past do this and I have just taken this for granted that OpenAccess does as well.
  12. Jan Blessenohl
    Admin
    Jan Blessenohl avatar
    707 posts

    Posted 10 Feb 2010 Link to this post

    Hello Geoff Davis,
    We have just added a backend configuration setting that allows you to use a datetime with automatic calculated values on insert and update to generate utc dates. This will be available in the Q1 release in march.

    All the best,
    Jan Blessenohl
    the Telerik team

    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
    Follow the status of features or bugs in PITS and vote for them to affect their priority.
  13. Daniel Plomp
    Daniel Plomp avatar
    130 posts
    Member since:
    Feb 2004

    Posted 26 Oct 2010 Link to this post

    So, what should I do now to set a Guid field in SQL?

    Inside my SQL table I have a field that is of type 'GUID' and the default value is 'newid()'.
    I now have set inside my Domain Model the field 'Kind' to 'Calculated' and inside my code I manually generate a new GUID.

    Is this the way, or should I do it different?

    Thanks,
    Daniel
  14. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 29 Oct 2010 Link to this post

    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
  15. KevinFarrow
    KevinFarrow avatar
    7 posts
    Member since:
    Apr 2007

    Posted 16 Dec 2010 Link to this post

    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 :-)
  16. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 20 Dec 2010 Link to this post

    Hi RDNZL,

     We do plan to implement support for default values however no real work has been thrown into that area as for now. I will escalate the issue to be rediscussed in our team so that we can give it a proper time estimate and a possible implementation. 

    Greetings,
    Petar
    the Telerik team
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  17. KevinFarrow
    KevinFarrow avatar
    7 posts
    Member since:
    Apr 2007

    Posted 25 Mar 2011 Link to this post

    Hi,

    Has there been any development on adding support for default values?

    You said it was going to be discussed but i have not heard anything for over 3 months, so I presume letting customers know what is happening isn't a priority for you guys.

    Regards,
    Kevin Farrow
  18. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 30 Mar 2011 Link to this post

    Hello RDNZL,

     Its still on our ToDo list however the low interest in this feature has influenced our decision to postpone the development for the future. I will add a public item for this feature so that others people can vote for it and if it gains some popularity we will certainly rise the priority of implementing it.
    Please do let me know if that works for you.

    Regards,
    Petar
    the Telerik team
  19. Patrice Boissonneault
    Patrice Boissonneault avatar
    18 posts
    Member since:
    Mar 2010

    Posted 30 Mar 2011 Link to this post

    Low popularity?!  I think this is a must feature.  Just started implementation of the ORM with OA and hit this show stopper bug.

    RDNZL: You mention other ORM were handling that properly, can you please let us know which one is it?

    THanks.
  20. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 05 Apr 2011 Link to this post

    Hello Patrice Boissonneault,

     The pits id of this feature request is 5399. Please vote there so that we can get an actual status update on how important this feature is.

    Best wishes,
    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
  21. KevinFarrow
    KevinFarrow avatar
    7 posts
    Member since:
    Apr 2007

    Posted 15 Apr 2011 Link to this post

    Hi Patrice,

    We use EntitySpaces ORM (www.entityspaces.net) which is excellent and works with most databases. You can interrogate the outputted SQL and it works as it should with default columns. For example the following code:

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

    produces the following SQL:

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

    I cant understand why openAccess cannot do the same. I can only image that is is highly inefficient and produces a lot more SQL than is needed.
  22. KevinFarrow
    KevinFarrow avatar
    7 posts
    Member since:
    Apr 2007

    Posted 15 Apr 2011 Link to this post

    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?
  23. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 20 Apr 2011 Link to this post

    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
    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
  24. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 13 Oct 2011 Link to this post

    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.

  25. Steve
    Steve avatar
    1869 posts
    Member since:
    Dec 2008

    Posted 01 Dec 2011 Link to this post

    @Ivalio
      Can you elaborate on the workaround?...If I'm using the OpenAccessLinqDataSource to bind my objects and do auto CRUD operations on the RadControls, how would that work?

    Can't really do a partial class constructor override right...

    Steve
  26. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 06 Dec 2011 Link to this post

    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!

  27. Keith Heilveil
    Keith Heilveil avatar
    29 posts
    Member since:
    May 2005

    Posted 23 Feb 2012 Link to this post

    I just installed Q1 2012, and this still doesn't appear to be resolved. This is making using the product very difficult.
    Is there a real ETA to getting this functionality resolved?
  28. KevinFarrow
    KevinFarrow avatar
    7 posts
    Member since:
    Apr 2007

    Posted 24 Feb 2012 Link to this post

    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
  29. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 01 Mar 2012 Link to this post

    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 >>
  30. Ivailo
    Admin
    Ivailo avatar
    318 posts

    Posted 27 Apr 2012 Link to this post

    Hi,

    We are happy to announce that support for Default Values is now implemented in OpenAccess. Take a look at the relevant blog post, download the 2012.1.427.1 internal build to try the new functionality before the official Q2 2012 release and do not hesitate to share your comments.

     

    Regards,
    Ivailo
    the Telerik team
    Follow @OpenAccessORM Twitter channel to get first the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
Back to Top