How to create DevExpress XPO dynamically with multiple primary-keys table?

Question

I try example in this topic https://github.com/DevExpress-Examples/XPO_how-to-create-an-xpclassinfo-descendant-to-dynamically-build-a-persistent-class-structure-e1729

But It only works for single primary key tables. So i searched more Found this: https://github.com/DevExpress-Examples/XPO_how-to-create-persistent-classes-mapped-to-tables-with-a-composite-primary-key-at-runtime-e4606

Buts there's big different and I think its not satisfy because it speaking about composite key( one Key which have many columns) So all I need please an example of creating XPO dynamically from SQL -Server Table: My Table Schema as following

The XPOCollectionSource then binding to grid in server-mode… Thats all I need.

Code I Use

XPServerCollectionSource GetServerModeSourceForTable(IDbConnection connection, string tableName) {  
    XPDictionary dict = new ReflectionDictionary();  
    XPClassInfo classInfo = dict.CreateClass(dict.QueryClassInfo(typeof(LiteDataObject)),  
        tableName);  
    DBTable[] tables = ((ConnectionProviderSql)XpoDefault.GetConnectionProvider(connection,  
        AutoCreateOption.None)).GetStorageTables(tableName);  
    foreach (DBColumn col in tables[0].Columns) {  
        XPMemberInfo member = classInfo.CreateMember(col.Name, DBColumn.GetType(  
            col.ColumnType));  
        if (tables[0].PrimaryKey.Columns.Contains(col.Name))  
            member.AddAttribute(new KeyAttribute());  
    }  
    return new XPServerCollectionSource(new Session(XpoDefault.GetDataLayer(  
        connection, dict, AutoCreateOption.None)), classInfo);  
}  

At a glance. How to use XPServerCollectionSource with Dynamically created XPO object. with two primary keys.

Answer 1

I don't know if this is what you want, but I'll share my code using XPInstantFeedbackSource.

You can get more information at How To Implement The Instant Feedback Mode Xpo

XPLiteObject LogXPOModel

[Persistent("log.t_log")]
public class LogXPOModel : XPLiteObject
{
    [Key, DevExpress.Xpo.DisplayName("id")]
    public long id { get; set; }

    [Key, DevExpress.Xpo.DisplayName("recv_time")]    
    public long recv_time

    ...
}

Window MainWindow

public class MainWindow : Window
{
    public XPInstantFeedbackSource SqliteXPInstantFeedbackSource

    public MainWindow() {
        ...
        SqliteXPInstantFeedbackSource = new XPInstantFeedbackSource();
        SqliteXPInstantFeedbackSource.ObjectType = typeof(LogXPOModel);
        SqliteXPInstantFeedbackSource.ResolveSession += SqliteXPInstantFeedbackSource_ResolveSession;
        SqliteXPInstantFeedbackSource.DismissSession += SqliteXPInstantFeedbackSource_DismissSession;
        View.Grid.ItemsSource = SqliteXPInstantFeedbackSource;
    }
    
    ...
}

Like that — my table has two keys; and, it does not incur any problems.

Answer 2

https://github.com/DevExpress-Examples/XPO_how-to-create-persistent-classes-mapped-to-tables-with-a-composite-primary-key-at-runtime-e4606/blob/19.2.7%2B/CS/XpoConsoleApplication/XPComplexCustomMemberInfo.cs

This is a class must used. who need more details about that. I can help him for free.

Implementation can be like:

[NonPersistent]
public class XPDynamicObject  : XPLiteObject
{
    public XPDynamicObject(Session session) : base(session) {}
    public XPDynamicObject(Session session, XPClassInfo classInfo) : base(session, classInfo) { }
}


       Button_Click or any event :

       XPDynamicObject.AutoSaveOnEndEdit = false;

        ReflectionDictionary dic = new ReflectionDictionary();
        var classInfo = dic.CreateClass(dic.GetClassInfo(typeof(XPDynamicObject)), "general.users");

        // WE MUST get schema from database .... via ConnectionProviderSql this is only way 
        var provider = XpoDefault.GetConnectionProvider(MSSqlConnectionProvider.GetConnectionString("(local)", "testdb"), AutoCreateOption.None) as ConnectionProviderSql;

        
        // Composite Key - this is only way to add composite key dynamically
        XPComplexCustomMemberInfo user_key = new 
        XPComplexCustomMemberInfo(classInfo, "user_key", typeof(object), new KeyAttribute(), new PersistentAttribute(), new BrowsableAttribute(false));
        user_key.AddSubMember("user_brn", typeof(int), new PersistentAttribute("user_brn"));
        user_key.AddSubMember("user_num", typeof(int), new PersistentAttribute("user_num"));

        var user_name = classInfo.CreateMember("user_name", typeof(string));
        user_name.AddAttribute(new PersistentAttribute("user_name"));
        

        dal = new SimpleDataLayer(dic, provider);

        XPServerCollectionSource xpServerCollectionSource = new XPServerCollectionSource(session, classInfo); // XPServerCollectionSource Only Editable server-mode datasource via ALlowNew, AllowEdit, AllowRemove properties

GridControl.DataSource = xpServerCollectionSource ;

Full Cheat Sheet (Design-Time and Run-Time XPOs)

For Searching for Object by Key or Condition use following:

Edit: 2021-05-24

For those who asks about Associations with composite keys. Its really nightmare. By default Xpo doesn't provide that. instead you can use a Session.GetObjectByKey() in child-class and a new XPCollection in parent class. But actually its something little slowly that native way with an artificial key.

At a glance: Use Artifcial / Single Key Column for Better any ORM compatibility. Otherwise It will be nightmare in database later. If you for example support another or replace current ORM.

Here's a code for associations with composite keys. (Note that you need to convert it dynamically like above pictures. You maybe need to create custom XPMemberInfo and override GetValue() to return Session.GetObjectByKey() and create new XPCollection. (really bad stuff to do and worst ever and non-supported ever by Xpo. So Composite-Keys technically are bad way except if you need create Single Table bind it to grid. EX:- SalesOrderDetail table which doesn't have more child tables)

[Persistent("users")]
public class user : XPLiteObject
{
    public user(Session session) : base(session) { }
    public user(Session session, XPClassInfo classInfo) : base(session, classInfo) { }

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

    private XPCollection<Trans> _trans;
    public XPCollection<Trans> trans
    {
        get
        {
            if (_trans == null)
                _trans = new XPCollection<Trans>(Session, CriteriaOperator.Parse($"user_brn = {user_key.user_brn} AND user_num = {user_key.user_num}"));
            return _trans;
        }
    }

}

public class Trans : XPLiteObject
{
    public Trans(Session session) : base(session) { }
    public Trans(Session session, XPClassInfo classInfo) : base(session, classInfo) { }

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

    public int user_brn { get; set; }
    public int user_num { get; set; }

    public user user
    {
        get
        {
            var key = new user_key();
            key.user_brn = 1;
            key.user_num = 3;
            var obj = Session.GetObjectByKey<user>(key, true);
            return obj;
        }
    }
}

// Composite-Key
public struct user_key
{
    [Persistent("user_brn")]
    public int user_brn { get; set; }
    [Persistent("user_num")]
    public int user_num { get; set; }
}

A Dynamic run-time version from above model:

    class XPCompositeAssociationMemberInfo : XPCustomMemberInfo
    {
        public XPCompositeAssociationMemberInfo(XPClassInfo owner, string propertyName, Type propertyType, XPClassInfo referenceType, bool nonPersistent, bool nonPublic) : base(owner, propertyName, propertyType, referenceType, nonPersistent, nonPublic)
        {
        }

        public override object GetValue(object theObject)
        {
            XPDynamicObject val = theObject as XPDynamicObject;
            if(val != null)
            {
                var user_num = val.GetMemberValue("user_num");
                IdList vals = new IdList();
                vals.Add(1);
                vals.Add(user_num);
                return val.Session.GetObjectByKey(ReferenceType, vals);
            }
            return val;
        }
    }


  XPDynamicObject.AutoSaveOnEndEdit = false;

        ReflectionDictionary dic = new ReflectionDictionary();
        var userClassInfo = dic.CreateClass(dic.GetClassInfo(typeof(XPDynamicObject)), "users");

        // WE MUST get schema from database .... via ConnectionProviderSql this is only way 
        var provider = XpoDefault.GetConnectionProvider(MSSqlConnectionProvider.GetConnectionString("(local)", "testdb"), AutoCreateOption.None) as ConnectionProviderSql;
        //DBTable Table = provider.GetStorageTables("users")[0];
        
        // Composite Key - this is only way to add composite key dynamically
        XPComplexCustomMemberInfo user_key = new XPComplexCustomMemberInfo(userClassInfo, "user_key", typeof(object), new KeyAttribute(), new PersistentAttribute(), new BrowsableAttribute(false));
        user_key.AddSubMember("user_brn", typeof(int), new PersistentAttribute("user_brn"));
        user_key.AddSubMember("user_num", typeof(int), new PersistentAttribute("user_num"));
        
        var user_name = userClassInfo.CreateMember("user_name", typeof(string));
        user_name.AddAttribute(new PersistentAttribute("user_name"));

        var transClassInfo = dic.CreateClass(dic.GetClassInfo(typeof(XPDynamicObject)), "Trans");
        var TransID = transClassInfo.CreateMember("TransID", typeof(int), new KeyAttribute());
        var user_brn = transClassInfo.CreateMember("user_brn", typeof(int));
        var user_num = transClassInfo.CreateMember("user_num", typeof(int));
        XPCompositeAssociationMemberInfo userMI = new XPCompositeAssociationMemberInfo(transClassInfo, "user", typeof(object), userClassInfo, true, false);



        dal = new SimpleDataLayer(dic, provider);            
        Session session = new Session(dal);
        XPServerCollectionSource xpServerCollectionSource = new XPServerCollectionSource(session, transClassInfo); // XPServerCollectionSource Only Editable server-mode datasource via ALlowNew, AllowEdit, AllowRemove properties                        
        xpServerCollectionSource.DisplayableProperties = "TransID;user.user_key.user_num;user.user_name";

Finally, IMPORTANT !

Its really very very hard to achieve Composite-Keys cases at runtime in any Xpo. Instead replace with Single Column Key(Identity or Artificial one. GUID one etc.)