I'm targeting NHibernate 3.1. I'm working with a 3rd party database which I must treat as read-only. I'd like to query it as follows:

select distinct A.Code, A.Description, A.Category, O.Int_Option as IntOption from AML A left join OPT O on A.Supplier = O.Supplier where O.Int_Option = 'Y'

My first thought was to create a typedlist but there is no PK, FK relationship between AMT and OPT and it doesn't seem possible in the designer.

Is there another way? Am I approaching the problem incorrectly?

Cheers

Pete

You can define a relation (model-only) manualy in the designer. Then you would be able to add both entities to a typedList.

That's what I look at first of all but I want to join the two tables on a field that isn't the primary key. The relationship designer won't let me do that. Is there another way?

You can either change the Identifying key of the entity, or if you want to maintain the current PK, you may re-map the table to another (new) entity, and set the required key as the PK, and use it in the TypedList.

Also you can opt for using a code-based DynamicList, rather than the TypedList, and in code you can define relations between any fields.

I'll look into remapping this evening but I'm really interested in the DynamicList approach. Is this supported using the nHibernate framework and if so, can you point me in the direction of an example.

Thanks for you help!

You can use DynamicList only with LLBLGen Pro Framework. As you are using NHibernate you should look into the NH way to do this.

You also could use TypedViews. That is: a database view mapped onto a model object (Entity or TypedView).

You can either change the Identifying key of the entity, or if you want to maintain the current PK, you may re-map the table to another (new) entity, and set the required key as the PK, and use it in the TypedList.

As I needed to maintain the current PK I tried to create another entity and map it to the same table with different keys. Unfortunately when you do this, the validator tell you that the model is wrong as the target has multiple keys assigned. Is there a way around this?

I've looked for an NHIbernate way of doing dynamic lists across unrelated tables and it seems there isn't one. You can only retrieve data from entities where the relationship is defined in xml. The more I use NHibernate the more it feels like a waste of time.

Hmm....

you can do joins in HQL though. NHibernate doesn't use FK fields, but you can specify joins in HQL like you would in SQL, which should be able to give you what you want.

(yes I agree, NHibernate isn't really flexible )