I've run into a situation where the Table A object needs to map some Table B records as a one to many, but Table B has so many records linked to a particular Table A record that I want to further restrict which records make it into the one to many. This is a legacy database structure which cannot easily be changed because of other legacy programs in production.
Lets say table B looks like this:
TableBPKey (int not null)
TableAFKey (int not null)
TableBColumn1 (char(4) null)
TableBColumn2 (varchar(50) null)
Lets say TableBColumn1 records might contain any of 'N' 4 character codes that classify the table B record in some fashion.
Anyways, lets say I've already mapped the one to many using the TableAFKey column. Can I also tell the mapping that I only want those records where TableBColumn1 == "ABCD" for instance?
Anything using mapping by code would be preferred solution. If it can be done in the old xml mappings but not Mapping by code I'll take that as a solution too.
Thanks!
map.Where() is what I wanted. I tested it and it works. You can put an sql expression that would work after an sql Where clause and it would restrict the results that way.
Set<TableBObject>(x => x.TableBRecordsABCDOnly, map =>
{
map.Inverse(true);
map.Cascade(Cascade.All);
map.Key(k => k.Column(c => c.Name("TableAFKey")));
map.Where("TableBColumn1 = 'ABCD'");
},
action => action.OneToMany());
I believe the "Formula" method on the One-to-many is what you're after. You should be able to use this to specify a additional SQL criteria, for example "is_active = 1".
You can find more info here. http://notherdev.blogspot.com.au/2012/02/mapping-by-code-map.html
(Updated link)