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:
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