1-to-1 relatioship in ORM Classic

[Alex]

Hello. What is the correct way to define 1-to-1 relationship in OpenAccess ORM Classic?

Let say I have 2 classes

User with fields Id, Email, Password
and
Settings with fields UserId, Setting1, Setting2, Setting3

I want to have property Settings UserSettings {get;set;} in User class, and I want ORM to found it in database using query similar to this one: SELECT FROM Settings WHERE UserID=User.ID

I want to have FK constraint created in Settings class on UserID field, and I want UserID to be PK in Settings class, because I want to ensure that only one instance of Settings class can be stored for particular user in my database.

Is it possible to implement in OpenAccess?

Thanks.

[IT-Als]

Hi Alex,

First of all 1:1 associations are possible in OA. But it is more about navigation.

If you have a field (userSettings) on the User class referencing one Settings class on the database level a foreign key will be created in the table representing the User class (say UserTable). You can then query the settings like:

select u.userSettings
from UserExtent as u
where u,id = ....

In the above example you navigate from the User class to the Settings class by using the userSettings field of the User class.
Doing so also solves your other issue with "ensure that only one instance of Settings class can be stored for particular user in my database". Since only one reference exists from the User to the Setting this is only one being there for a particular User instance.

I would do it the above way, since you get a lot of benefits and the only drawback as I see it is that you must change the way you query for the settings by using the UserExtent instead of the SettingsExtent.

regards

Henrik

[Alex]

Henrik, thanks for your reply.

First of all, I don't quite understand what is going to be stored in User.UserSettings field in database?

Second, the problem that I cant get it worked in my application, I tried different things and nothing seems to work.

I created new field in User class, which is holding instance of Settings class.

When doing forward mapping, wizard detects this class and allows me to specify field in my User table which is going to be used to reference Settings. But when i'm trying to build database, I do not see any FK getting created.

Also when I'm storing instance of User class in database, with UserSettings field instantiated, I see taht record is getting added to Settings table, but it contains wrong value in UserID field. For some reason it puts there 0, instead of actually value of User.Id field

Is it possible to get source code sample with this example implemented?

Thanks.

[IT-Als]

Hi Alex,

Can I see your source code?

You don't need to have a reference from the Settings class to the User class, only need to have one from the User class to the Settings class. And the value of this reference (the id of the Settings class instance associated to the User class instance) will be put in this field in the database.

As for the foreign key constraint in the forward mapping wizard you can specify whether to generate it or not on the User class (the userSettings field).

Update:
Pseudo code:

public class User
{
   private string Name;
   // Initialization of the variable is done immediately, hence I assume that a User instance cannot live without a Settings instance.
   private Settings userSettings = new Settings();
   ...
   ...
}

public class Settings
{
   string preferredCulture;
   ...
   ...
}


Regards

Henrik

[Alex]

Thanks again for your message.

I think I'm doing exactly like you described in pseudo-code.

Please check attached example of source code.

I have 'asked' forward mapping to generate FK with default name, but it did not generate it.

Furthermore when I'm inserting user instance into database with instantiated Settings object it creates record in User table, and creates record in Settings table, but in settings table it have 0 in UserId field, while in User.Id - it has new auto-generated identity value.

Please check my source code and let me know what I'm doing wrong (rename .png to .zip, for some reason forum not allows me to upload zip files ? )

[Alex]

it doesn't allow me to attach zip files :(

please find it here http://www.2shared.com/file/J_fggiIb/ORM1to1Example.html

there is the link on the bottom of them page - Save file to your PC: click here

I want to ask Telerik support. Why zip files are restricted?? How we should exchange source code with examples?

[IT-Als]

Okay, took a look at it.

First of all. You don't need the reference to userId in the Settings class. Because you already have a reference from the User class to the Settings class. This is also an int value field and it does not get populated upon Transaction.Commit. So delete it.

Also, make the Settings class derive from BaseEntity and decorate it with:

[Persistent(IdentityField = "_id", VersionField = "_version")]

just like the User class

Make sure you define the key generator to be AUTOINC also for the Settings class.

So, updated pseudo (based on your source):


[Persistent(IdentityField = "_id", VersionField = "_version")]
public class User : BaseEntity
{
   private string Name;
   // Initialization of the variable is done immediately, hence I assume that a User instance cannot live without a Settings instance.
   private Settings userSettings = new Settings();
   ...
   ...
}

[Persistent(IdentityField = "_id", VersionField = "_version")]
public class Settings : BaseEntity
{
   string preferredCulture;
   ...
   ...
}

Oh, by the way. You can only attach files to support tickets and not to open forum posts..

Regards

Henrik

[Alex]

Thanks Henrik. Adding auto-popuplated identity field to Settings class resolved my problem.

But my original intention was to use value of PK field of User table, and store it in field UserId in Settings table.
So Settings table will not have its own auto-incremented identity value, and will use values of Id field in User table as its primary key values. This way I can make sure that different records in User table will always reference different records in Settings table. and this is will be clear 1-to-1 relationship at database level.

So my ultimate goal was to build FK between fields User.Id and Settings.UserId. And do not inherit Settings from BaseEntity.

But your solution will work for me too, I just put unique index at User.SettingsId field.

Thanks for your help.

[IT-Als]

Glad it worked out for you Alex,

Just to clarify it. Because you're doing the initialization of the Settings field in the User class you will always end up with a 1:1 relationship, because when you instantiate an instance of a User class you will also have a reference to an instance of the Settings class, too.

Another solution could be to declare the Settings class as a struct. Doing so will embed the Settings inside the User class, not only on the class level but also on the database table level. Thus, you will only have the User class instance represented in the user table in your database and not the Settings class. The fields of the struct will be present (embedded) within the user table.

Regards

Henrik