Complex relations between entities

[Peter Bakker]

Hallo Anyone,

I'm new using OpenAccess ORM, but so far very pleased with the results.
But, I'm wondering how to set a relationship between entities where the inner select statement isn't as straightforward as usual.

Example: A dossier has dossiernode in a tree structure
- Master  table 'Dossiers' (ID, Name, etc...)
- Child table 'DossierNodes' (ID, ParentID, DossierID, Name, ..etc)

The relationship between Dossiers and DossierNodes should be:
Dossiers.ID=DossierNodes.DossierID And ParentID=NULL

So far I’ve seen and read, everything is done by de 'Forward' and 'Reverse' wizard.
Is there a way to bypass the wizard to make this possible.

Thanks in advance.
Peter

[PetarP]

Hi Peter Bakker,

Can you please provide us with some more information regarding your project. Do you have an already defined database? If this is the case, you will have to run the reverse mapping wizard as this would be the only way to map your tables to a class model where all the relationships a properly handled.

If you have your classes and you don't have a database, then you are not obliged to use any of the Forward or the Reverse mapping wizards. Than Telerik OpenAccess ORM will construct the database for you with some default settings.

Also if you have your classes you will be able to edit the app.config file and specify everything you need without using the wizard. Nevertheless using the Forward mapping wizard is by far easier that inserting your own setting by hand.
Please clarify your goals a little bit more so we can assist you further.

Greetings,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Peter Bakker]

Hello Petar,

Thank for your reply.
It is a situation in a old project of mine that I was using to learn the OpenAccessORM framework.

Database
Table :Categories
[   ID        Int    PK, Identity(1,1) Not Null
    Name    varchar(50), Not Null, 
    
    ..the rest is not important...
]
Table : CategoryClassifications
[    ID                   Int    PK, Identity(1,1) Not Null
    Category_ID    Int    Not Null //One-to-many reference to the Categories table
    Parent_ID        Int    Null    //One-to-many reference to it self 
    Name    varchar(50)
    .. the rest is not important...
]
Data Example:
1. Cat A
        Classification A1
            Classification A1.1
            Classification A1.2
        Classification A2
        Classification A3
2 Cat B
        Classification B1
        Classification B2

Object Example:
Category cat = new Category(A)
cat.Childs //should hold a list of "A1, A2, A3"
To make the relationship for 'Childs' a reference to Category_ID is not enough. The conditions should also make sure that it is the root classification of that category "Parent_ID Is Null".

This is basic SQL functionality and I was wondering how the model this in your framework.
How do I model a relationship that consist of more conditions (also using const like NULL)?

Thanks
Peter

[PetarP]

Hello Peter Bakker,

You can achieve the desired functionality using our forward mapping approach. Here is how the code should look like:

[Telerik.OpenAccess.Persistent()]
    public class CategoryClassifications
    {
        private int iD;

        public int ID
        {
            get { return iD; }
            set { iD = value; }
        }
        private IList<Category> category=new List<Category>();

        public IList<Category> Category
        {
            get { return category; }
            set { category = value; }
        }
        private IList<CategoryClassifications> categoryClassification=new List<CategoryClassifications>();

        public IList<CategoryClassifications> CategoryClassification
        {
            get { return categoryClassification; }
            set { categoryClassification = value; }
        }
        private string name;

        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        private CategoryClassifications categoryClassificationInv; //inverse field

        public CategoryClassifications CategoryClassificationInv
        {
            get { return categoryClassificationInv; }
            set { categoryClassificationInv = value; }
        }
    }

[Telerik.OpenAccess.Persistent(IdentityField = "iD")]
    public class Category
    {
        private int iD;

        public int ID
        {
            get { return iD; }
            set { iD = value; }
        }
        private string name;

        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        private CategoryClassifications categoryClassification; //inverse field

        public CategoryClassifications CategoryClassification
        {
            get { return categoryClassification; }
            set { categoryClassification = value; }
        }
    }

As you can see the CategoryClassification class holds a list of Category Classification objects and a single Category Classification object. This is done in order to avoid the generation of a join table and allow you to access the parent object through the child one. Have in mind that you should uncheck the join table check box in the forward mapping wizard when generating your database.

In addition when you use forward mapping, you should go through every field and adjust the settings that best suits your scenario (as varchar(50) instead of varchar(255)).
The Nullable constraint is specified when you define your field as follows:

private string? name;

If you would like to implement some custom logic for validating during insert you can easily take advantage of the IInstanceCallbacks interface. Implementing the methods of this interface will give you control over your persistent object thus enabling you to perform custom validation. Please have a look at our Knowledge base article that deals with this topic.

Kind regards,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Peter Bakker]

Hello Petar,

Again thanks for your reply.
But I don't understand your solution.
I your solution the Category object does not have childs, but as you can see in my previous post the Category object has a list of classifications. Should be something like this:

[Telerik.OpenAccess.Persistent(IdentityField = "iD")]
public class Category
{
   private int    iD;
   private string name;
   private IList<CategoryClassifications> categoryClassification = List<CategoryClassifications>();

   public int ID
   {   get { return iD; }
       set { iD = value; }
   }

   public string Name
   {   get { return name; }
       set { name = value; }
   }

   public IList<CategoryClassifications> CategoryClassification
   {   get { return categoryClassification; }
       set { categoryClassification = value; }
   }

}

I understand that I can define a 'inverse' field for making the relationship between the Category and CategoryClassifications.

[Telerik.OpenAccess.Persistent()]
public class CategoryClassifications
{
   private int    iD;
   private string name;
   private IList<CategoryClassifications> childs = new List<CategoryClassifications>();

   //inverse field to make sure the Category class has a list of Classifications
   private Category categoryInv;

   //inverse field to hold a reference to it self
   private CategoryClassification parentInv

   public int ID
   {   get { return iD; }
       set { iD = value; }
   }

   public string Name
   {   get { return name; }
       set { name = value; }
   }

   public IList<CategoryClassification> Childs
   {   get { return childs; }
       set { childs = value; }
   }

}

If I understand correctly the 
    private Category categoryInv;
within the 'CategoryClassification' class makes sure the class 'Category' can hold a list of CategoryClassifications.

But, the CategoryClassification class is constructed to hold a tree of classifications.
The child list of a category should only contain the root elements of the classification tree.
How do I make sure the 'Category.Childs' list holds only the root elements of the tree.

Is the OpenAccessORM framework adding a thirth (hidden) table to hold only root elements of the classification??

Greetings
Peter

[PetarP]

Hello Peter Bakker,

In the PreStore() method you should allow only entries of type CategoryClassification that do not have a parent. This way you will ensure that only root elements of type CategoryClassification will make their way in the CategoryClassification collection of the Category object. The logic for filtering should be located in the PostLoad() method that is executed before the commit.

Kind regards,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.