How to create one to one relationship in DevExpress XPO classes?

Question

Shortly,

Here's a schema

User Table

Category Table

1. A user have a categoryID
2. All I need to populate a GridControl with User_ID, CategoryID, CategoryName
3. Note that CategoryName is only belongs to second table (Category Table)

What I do Is:

Create Models (User, Category) Note that primary key is composite so it must be structure https://supportcenter.devexpress.com/ticket/details/a2615/xpo-how-to-map-persistent-objects-to-database-tables-with-compound-or-composite-multi#

// Composite key must be a struct
public struct user_key
{
    [Persistent("user_brn")]
    public int user_brn { get; set; }
    [Persistent("user_num")]
    public int user_num { get; set; }
}

[Persistent("Users")]
public class User : XPLiteObject
{
    public User(Session session) : base(session) { }

    [Key, Persistent] 
    public user_key key;    // user_brn, user_num

    [Persistent("user_name")]
    public string user_name { get; set; }

    [Persistent("CategoryID")]
    public int CategoryID { get; set; }

    [NonPersistent, Association("user_category")]
    public Category Category
    {
        get; set;
    }

    [PersistentAlias("Category.CategoryName")]
    public string CategoryName { get; set; }            // I think it will work. But it doesn't
}


[Persistent("Category")]
public class Category : XPLiteObject
{
    public Category(Session session) : base(session) { }

    [Key, Persistent("CategoryID")]
    public int CategoryID { get; set; }

    [Persistent("CategoryName")]
    public string CategoryName { get; set; }

    private User _user;
    [NonPersistent, Association("user_category")]
    public User User { get; set; }
}

GridView code

 IDataLayer dal = 
 XpoDefault.GetDataLayer(MSSqlConnectionProvider.GetConnectionString(" 
 (local)", "testdb"), AutoCreateOption.DatabaseAndSchema);
 Session session = new Session(dal);
 XPCollection collection = new XPCollection(session, typeof(User));
 gridControl1.DataSource = collection;

Result: = fail

What I should do? Any help

Answer 1

In your User class, remove the property CategoryId and alter your Category property as follows:

[Persistent("CategoryID")]
public Category Category
{
    get; set;
}

and change your CategoryName property:

[PersistentAlias("Category.CategoryName")]
public string CategoryName 
{ 
    get
    {
        return (string)EvaluateAlias(nameof(CategoryName));
    }
}  

The reference from Category back to User is trickier, since there's no persistent field to back it. You could try it with a free join:

[PersistentAlias("[<User>][Category = '@This.CategoryID'].Single()")]
public User User 
{ 
    get
    {
        return (User)EvaluateAlias(nameof(User));
    }
}