Problem joining with table that has an ID of zero

I am getting the following error joining between two tables where the foreign key has a zero as it's value.  Here is the exception I am getting:

---------------------------
Debugger Exception Notification
---------------------------
Project AureliusTest2.exe raised exception class EIdNotSetException with message 'Id not set on entity of class TStage.'.
---------------------------
Break   Continue   Help   
---------------------------

My object has a join to another object as follows:

TLoadDetail = class
  ....
    [Association([TAssociationProp.Required],[])]
    [JoinColumn('STAGE_ID', [TColumnProp.Required], 'ID')]
    FStageID :TStage;
  ....

Also the Association documentation states :

"Required
Associated object is required. When Aurelius executes a SELECT statement to load the object, it will use an INNER JOIN to retrieve data for the associated object. "
 
Yet the SQL monitor shows a left join not an inner join 

...
FROM LOAD_DETAILS A
  LEFT JOIN STAGES B ON (B.ID = A.STAGE_ID)

It looks to me like Aurelius is treating a zero integer as a null but I actually want the value to be zero.



  

The problem is not the join, but the id equals to zero. TMS Aurelius considers integer id with zero value equals to a null value, so it's not supported, unfortunately.

I remember when I met you last year at the TMS day in Belgium you saying something in passing about treating 0 integers as nulls which I found curious at the time.  So I thought this would likely be your answer but it does cause problems for the job I have been looking at using Aurelius with, I think I will stick with datasets for a while longer till I'm more familiar with all these little gotcha's. 


The other point I made about the join was really a side point as it seems to be a bug in Aurelius, at least its behaving differently to how its documented.

Anyway thanks again for your speedy assistance.  

Hi Steve, yes, that's how it is now. Actually this can (and will, eventually) be solved when we add the possibility for the user to define the "null" value for an identifier. Currently, there is no such option and Aurelius treats 0 as null. But with that option you could set a -1, for example.

The issue here is that when you create an instance of an entity (TStage, for example), the Id property (or better, the FId field) is initialized to zero by the compiler. Thus, if you pass such instance to a method SaveOrUpdate, for example, or even Save, strange things can happen if zero is considered a valid Id. Aurelius will not know, for example, if the object must be updated (because you have set id to 0 and wants indeed update an object with id=0) or if the object must be inserted because the id has not been set yet.
So for this to work in future, you will have an option to define that Id can be 0 and -1 value should be treated as null, for example. But then, in the Create method of TStage class (or any other initialization place you think fits best) you should make sure that Fid is initialized to -1, so if you later set this property to 0, Aurelius can't tell if the object is a new one (id not set) or if it's an existing one.

Hi!


Is thos now possible?

I have an existing database (and I can't modify it) that have ta TAXES table where the ID column is the tax rate. Example below

[CODE]
TAX DESCRIPTION IS_ACTIVE VALID_FROM
0 no tax 1 2019-01-01
22 High tax rate (22%) 1 2019-01-01
9.5 Low tax rate (9.5%) 0 2019-01-01
[CODE]

The index in the DB is a combination of columns TAX and VALID_FROM.

the problem is when TAX = 0, Aurelus doesn't allow this. Is there any switch or something like this to allow 0zero tax - only for this table?

You can add attribute IdUnsavedValue to tell Aurelius consider a different integer value as null value instead of 0:



[IdUnsavedValue(-1)]
TMyClass = class

1 Like