fluent-nhibernate
fluent-nhibernate copied to clipboard
nhibernate
Not able to join on a joined table because not able to change Foreign key column
Hi.. I was thrilled when I found out how to use the Join() mapping method, but then was sad when I tried to nest it to join to another table for creating of a report object map. One of those situations where I need a report and the data I need is in bunch of tables, and also some of those tables will not have records (OUTER JOIN).
It seems the major thing missing (and easy to add?) is the ability to change the Foreign Key Column.. I construct the Join inside another Join's code and it "works" but it still uses the root table's ID field to link to the other table, which in my case is wrong. If I could just change that, it seems it should be OK. Even if it used the primary ID of the table that was joined it would be OK (though not ideal)
Table("`TABLE_1`");
Schema("SCHEMA_1");
Id(x => x.XYZ, "XYZ").GeneratedBy.Native();
Join("TABLE_2", j =>
{
j.Schema("SCHEMA_1");
j.Optional();
j.Fetch.Join();
j.KeyColumn("XYZ");
// TABLE_2 is a linking table between TABLE_1 and TABLE_3.
// assume fields: TABLE_2.XYZ, TABLE_2.ABC
Join("TABLE_3", j2 =>
{
j2.Schema("SCHEMA_2"); // It happens to be in another schema, but shouldn't matter.
j2.Optional();
j2.Fetch.Join();
j2.KeyColumn("ABC");
// This table ends up being joined by TABLE_1.XYZ:
// ON TABLE_1.XYZ = TABLE_3.ABC
// but it needs to use TABLE_2.ABC so that we end up with:
// ON TABLE_2.ABC = TABLE_3.ABC
});
});
I would have to rebuild my test cases for this, but it is no secret there is a limitation and it's straightforward to create.
Here are some other articles (though they are old) that point out alternatives to not being able to do this: http://stackoverflow.com/questions/6776889/fluentnhibernate-nested-component-mapping-results-in-nhiberate-queryexception
http://stackoverflow.com/questions/5835633/fluent-nhibernate-join-tables-in-mapping-not-using-primary-key
Contrasting with NHibernate mapping by code: http://notherdev.blogspot.com/2012/01/mapping-by-code-join.html
To create this case, the other table mappings would actually have no concept of each other.. simply because we don't always want them to. And that is not the point either.. The Join works fine (ideal for some reporting class that does not deserve to push any relationships among the other tables) for just 1 "neighboring" table.
All you have to do is have schema like this:
TABLE1: TABLE1_PRIMARY_ID, TABLE2_ID, TABLE1_DATA
TABLE2: TABLE2_PRIMARY_ID, TABLE3_ID, TABLE2_DATA
TABLE3: TABLE3_PRIMARY_ID, TABLE3_DATA
Writing a Join mapping works fine if you are creating a Fluent mapping in 1 class that will use TABLE 1 and 2, but you can not continue that further, such as into TABLE 3. As the code example indicates, you'd think that because of the { } that the scope would kind of assume that the "inside join" would use TABLE2 and TABLE3 in the query, but it is using TABLE1, and there is no way to tell it not to.
Is this still an issue? I've been struggling with it for over 10 hours with no luck.
I am not aware of any updates to fix this. But I have figured out a much better work around which has been here all along and I was very upset that I didn't figure it out sooner.. however online articles did not bring it up at all, except for 1 off the wall topic.
And here is quick one.. giving it the name of "theta join": http://stackoverflow.com/questions/23865962/join-tables-in-nhibernate-without-mapping
So the point is that we can write our LINQ queries as we need them, without being forced to do the mapping in the mapping files. That is ideal because not ever situation calls for the same kind of mapping relationships.
If you write your query this way, if you do have a relationship mapped in the files, the query will use a nice JOIN created SQL. If not, it will still do the same thing but will convert it to a WHERE condition.
Warning, however, when using this "theta join" method, you want to make sure that in your list of "from xyz in abc" lines, that anything pulling from NHibernate's Session comes LAST... in case you decide to mix in-memory lists with something coming from NHibernate in the same query.
I'm going to update the stackoverflow question with some more examples.
And the other alternative is to switch to using the built in NHibernate Mapping By Code (or something like that) which is relatively new. It has this ability. I was going to try to use it, but wanted first to figure out if it was possible to have both Fluent and NHib mappings in the same project to switch over slowly. I did not have luck so I just stopped and with this LINQ method I no longer care as much.
oh.. that's too bad. What did you switch to?
On Tue, Dec 23, 2014 at 11:00 AM, christopherbauer <[email protected]
wrote:
We stopped using nhibernate due to this issue :(
— Reply to this email directly or view it on GitHub https://github.com/jagregory/fluent-nhibernate/issues/272#issuecomment-67965797 .
We were using nhibernate in order to provide an ORM connection to as400/db2, and we're just going to implement a query object/interpreter pattern that allows us to control the join predicates because although we own the datatables, we cannot add any of the key fields necessary to make it work.
Just came here to check, if NHibernate 5 would have this fixed. Apparently not.
in native mapping by code Nhibernate 5.1.3 its possible to set foregn key:
var joinTable = typeof(Category).Name.ToLower();
Join("category_id", j =>
{
j.Optional(true);
j.Property(p=>p.CategoryName,m=>m.Column("Name"));
j.Key(km=>km.Column("id"));
j.Table(joinTable);
});
@megafetis
I am sorry but that code did not work for me. It only gave me a join by primary key and ignored the "category_id"-part. Have you really tried that?
I did try (with inspiration from http://notherdev.blogspot.com/2012/01/mapping-by-code-join.html):
Join("tableName", j =>
{
j.Optional(true);
j.Key(k =>
{
k.Column("id");
k.PropertyRef(x => x.tableNameId);
});
j.Property(p=>p.TableNameValue, m=>m.Column("Value"));
}
);
But then I got: Can't reference a property of another entity.
It seems like this is not possible at all through mapping. My first try was through FluentNhibernate and there was no problem creating a join on the primary key, but a left join on another property seems impossible. And mapping with code only gives me that error.
SQL View is not an option either for me, because of the "left join" that would also mean that the view cannot be indexed and the performance is therefore not good enough.
Theta join is possible, but becomes problematic in relation to all other mappings that is working - a merging step would then be necessary.
Any ideas on how to get this working with Fluent or Mapping With Code?
