Fluent mapping & composite foreign keys

[Ashley]

Hi,

I may be doing something incredibly stupid but I can't seem to get all fields in a composite foreign key to save the correct values.

Here's my scenario:
We have table1 with fields Key and Number. 
We have table2 which contains both fields as its primary key.

I create a new object from table1 and attach it to a new object I create from table2. I then save this data and while table1 has the correct data, table2 only contains the data from the key field and not the number field.

I mapped the tables like this:

var docConfiguration = new MappingConfiguration<objecta>();
docConfiguration.MapType(doc => new {
    DocKey = doc.DocKey,
    DocNo = doc.DocNo
}).UseDefaultMap().ToTable("table1");
docConfiguration.HasProperty(x => x.DocKey).IsIdentity(KeyGenerator.Autoinc);
configurations.Add(docConfiguration);

var docKeyConfiguration = new MappingConfiguration<objectb>();
docKeyConfiguration.MapType(docKey => new {
    DocNo = docKey.DocNo,
    KeyNo = docKey.KeyNo
}).ToTable("Table2");
docKeyConfiguration.HasProperty(x => x.DocNo).IsIdentity();
docKeyConfiguration.HasProperty(x => x.KeyNo).IsIdentity();
docKeyConfiguration.HasAssociation(docKey => docKey.Doc).HasConstraint((docKey, doc) => docKey.DocNo == doc.DocNo);
docKeyConfiguration.HasAssociation(docKey => docKey.Doc).HasConstraint((docKey, doc) => docKey.KeyNo == doc.DocKey);
configurations.Add(docKeyConfiguration);

Please assist.

Regards,
Ashley

[Boris Georgiev]

Hi Ashley,

If I understood correctly your design, you want one-to-one association with composite foreign key between two tables.
There are some problems in the code, that prevent it from correct execution.
You are trying to generate composite foreign key constraint using a non-primary key column. All columns which are part of the constraint should be marked as primary key. If you have composite foreign key constraint, you can not have an auto-increment column, this is not supported scenario in Telerik OpenAccess ORM. 

At the moment OpenAccess ORM has a limitation that allows only primary key columns in the source table for composite foreign key constraint.

The way you create associations, you are creating two different constraints. Here is example how to create one composite foreign key constraint:

docKeyConfiguration.HasAssociation(x => x.Table1Ref).WithOpposite(y => y.Table2Ref).HasConstraint((x, y) => x.KeyNumber == y.DocKey && x.DocNumber == y.DocNumber).IsManaged();

If you want to use auto-increment primary key column in "Table1" you need to use UniqueIdGenerator
which OpenAccess ORM provide. In order to implement this you should create the voa_keygen table manually, and set KeyGenerator to DocKey column from Table1:

UniqueIdGenerator idGenerator = new UniqueIdGenerator("KeyGenerator");

Table1 table1 = new Table1();
table1.DocKey = context.GetUniqueId(idGenerator);

Doing it this way, you will implement AutoInc on client side, not on a server side..

Here is a script how to generate voa_keygen table:

-- Telerik.OpenAccess.Runtime.KeyGenerator.HighLowRelationalKeyGenerator
CREATE TABLE [voa_keygen] (
    [table_name] varchar(64) NOT NULL,
    [last_used_id] int NOT NULL,
    CONSTRAINT [pk_voa_keygen] PRIMARY KEY ([table_name])
)

For your convenience I created a sample application for demonstration.

I hope this information is helpful for you.
Do come back in case you need further assistance.
 
Kind regards,
Boris Georgiev
the Telerik team

[Ashley]

Hi Boris,

Thanks for that, I've decided to manage the relationships manually for now.

What I want to do now is set up a 1:N relationship between 3 objects. Table1 having a 1:N relationship with both Table2 and Table3. I've modified the project you attached like this:

public class Table1

{
     public Table1() {
          Table2s = new List<Table2>();
          Table3s = new List<Table3>();
     }


    public int Id { get; set; }

    public int DocNumber { get; set; }

    public IList<Table2> Table2s { get; set; }

    public IList<Table3> Table3s { get; set; }

}

 

public class Table2

{

    public int DocNumber { get; set; }

    public int KeyNumber { get; set; }

    public Table1 Table1Ref { get; set; }

}

 

public class Table3 {

    public int DocNumber { get; set; }

    public int KeyNumber { get; set; }

    public Table1 Table1Ref { get; set; }

}

With the mapping defined:

List<MappingConfiguration> configurations = new List<MappingConfiguration>();

 

var docConfiguration = new MappingConfiguration<Table1>();

docConfiguration.MapType(doc => new {

    Id = doc.Id,

    DocNo = doc.DocNumber

}).ToTable("Table1");

//Whether or not I have this defined as IsIdentity() with the UniqueIdGenerator creating the values or not makes no difference. I still end up with the same output.


docConfiguration.HasProperty(x => x.Id).IsIdentity(KeyGenerator.Autoinc);

configurations.Add(docConfiguration);

 

var docKeyConfiguration = new MappingConfiguration<Table2>();

docKeyConfiguration.MapType(docKey => new {

    DocNo = docKey.DocNumber,

    KeyNo = docKey.KeyNumber

}).ToTable("Table2");

 

docKeyConfiguration.HasProperty(x => x.DocNumber).IsIdentity();

docKeyConfiguration.HasProperty(x => x.KeyNumber).IsIdentity();

 

docKeyConfiguration.HasAssociation(x => x.Table1Ref).WithOpposite(y => y.Table2s).HasConstraint((x, y) => x.DocNumber == y.DocNumber).IsManaged();

 

var docGenConfiguration = new MappingConfiguration<Table3>();

docGenConfiguration.MapType(docKey => new {

    DocNo = docKey.DocNumber,

    KeyNo = docKey.KeyNumber

}).ToTable("Table3");

 

docGenConfiguration.HasProperty(x => x.DocNumber).IsIdentity();

docGenConfiguration.HasProperty(x => x.KeyNumber).IsIdentity();

 

docGenConfiguration.HasAssociation(x => x.Table1Ref).WithOpposite(y => y.Table3s).HasConstraint((x, y) => x.DocNumber == y.DocNumber).IsManaged();

 

configurations.Add(docKeyConfiguration);

configurations.Add(docGenConfiguration);

return configurations;

And created in code like:

using (var context = new FluentModelContext()) {
    var schemaHandler = context.GetSchemaHandler();
    EnsureDB(schemaHandler);

    for (int i = 0; i < 10; i++) {
        var table1 = new Table1();
        table1.DocNumber = i;

        var table2a = new Table2();
        table2a.Table1Ref = table1;
        table2a.KeyNumber = 0;

        var table2b = new Table2();
        table2b.Table1Ref = table1;
        table2b.KeyNumber = 1;

        var table2c = new Table2();
        table2c.Table1Ref = table1;
        table2c.KeyNumber = 2;

        var table3a = new Table3();
        table3a.Table1Ref = table1;
        table3a.KeyNumber = 0;

        var table3b = new Table3();
        table3b.Table1Ref = table1;
        table3b.KeyNumber = 1;

        var table3c = new Table3();
        table3c.Table1Ref = table1;
        table3c.KeyNumber = 2;

        table1.Table2s.Add(table2a);
        table1.Table2s.Add(table2b);
        table1.Table2s.Add(table2c);
        table1.Table3s.Add(table3a);
        table1.Table3s.Add(table3b);
        table1.Table3s.Add(table3c);

        context.Add(table1);
    }

    context.SaveChanges();
}

And the result I get is:
Table1:
Id DocNo
11 0
12 1
13 2
14 3
15 4
16 5
17 6
18 7
19 8
20 9

Table2:
DocNo KeyNo
11 0
11 1
11 2
12 0
12 1
12 2
13 0
13 1
13 2
14 0
14 1
14 2
15 0
15 1
15 2
16 0
16 1
16 2
17 0
17 1
17 2
18 0
18 1
18 2
19 0
19 1
19 2
20 0
20 1
20 2

Table3 (actual):
DocNo KeyNo
11 0
11 1
11 2
12 0
12 1
12 2
13 0
13 1
13 2
14 0
14 1
14 2
15 0
15 1
15 2
16 0
16 1
16 2
17 0
17 1
17 2
18 0
18 1
18 2
19 0
19 1
19 2
20 0
20 1
20 2

This is what I expect:
Table3 (expected):
DocNo KeyNo
0 0
0 1
0 2
1 0
1 1
1 2
2 0
2 1
2 2
3 0
3 1
3 2
4 0
4 1
4 2
5 0
5 1
5 2
6 0
6 1
6 2
7 0
7 1
7 2
8 0
8 1
8 2
9 0
9 1
9 2


Despite setting the DocNumber field to be the referenced field the child objects are using the Id field (presumably because it's the primary key).

I have 2 questions:
1. Is it possible to achieve what I need with OpenAccess? I am working on a legacy system and I cannot alter the table definitions.
2. What is the point of specifying the column to use for foreign keys if OpenAccess is going to ignore my column and use the primary key instead?

Thank you for your assistance.

Regards,
Ashley

[Boris Georgiev]

Hello Ashley,

Unfortunately you have encountered a limitation of Telerik OpenAccess ORM. Its not a valid scenario to set the "1" part of a 1:N relationship to a column that is not a primary key. Like you guessed when creating such relationships in runtime, OpenAccess tries to convert  the scenario to a valid one by pointing to the primary key column instead.

One option for you is to work without this association - in case it is not important for your business logic. In case it is, we can try to design some workaround for you, depending on your abilities to change anything in the database. As we understand you could not alter the table definition, but can you add new views or stored procedures?

We are looking forward to your reply.

Greetings,
Boris Georgiev
the Telerik team

[Ashley]

Hi Boris,

I will work without the association. Do you have any plans to implement this functionality at any point?

Regards,
Ashley

[Ivailo]

Hi Ashley,

In the short run, we do not have plans to implement that scenario, as it would involve severe changes in our core runtime logic. However, we have it in our backlog and you might see it in one of the future versions of OpenAccess ORM.

Greetings,
Ivailo
the Telerik team