CODEWITHSUNDEEP
c#sql.netnhibernateicriteria
Spetastium
Spetastium

Reputation: 205

Joining two unrelated view-tables with nhibernate and ICriteria

I have two entities based on two views. The mappings looks like this:

Entiy A: 

<class name="SearchView" table="SearchView" dynamic-update="true" mutable="false" schema-action="none">
    <id name="Id" type="Guid" column="Id" />
    <property name="Id" column="Id" type="Guid" />
    <property name="Expires" column="Expires" type="DateTime" />
    <property name="VerificationNumber" column="VerificationNumber" type="Int32" />
    <property name="InvoiceNo" column="InvoiceNo" type="Int32" length="50" />
    <property name="Status" column="FakturaStatus" type="Int32" />
</class>

Entity B: 

<class name="SearchInvoiceResourceLookUpView" table="SearchInvoiceResourceLookUpView" dynamic-update="true" mutable="false" schema-action="none">
    <id name="Id" type="Guid" column="Id" />

    <property name="InvoiceId" column="InvoiceId" type="Guid" />
    <property name="ResourceId" column="ResourceId" type="Guid" />        
</class>

Entity A is based on a table view that is a flattened view of a more complex table-structure, for search optimization. Now i want to be able to get all the rows from Entity A where the Id is in column "InvoiceId" in Entity B for a specific value of "ResourceId" in Entity B by using NHibernate and the Criteria-API. The both tables are views and they have no declared relationship. I have tried the following code in C# but it doesn't work:

        var criteria = _session.CreateCriteria(typeof(SearchView));
            criteria.CreateAlias("SearchInvoiceResourceLookUpView", "srf",JoinType.InnerJoin)
                .Add(Restrictions.EqProperty("sfr.InvoiceId", "Id"))
                .Add(Restrictions.Eq("sfr.ResourceId", invoiceResId));

The raw SQL for this purpose would be:

SELECT * FROM SearchView
JOIN SearchInvoiceResourceLookUpView srf on srf.InvoiceId = Id 
WHERE srf.ResourceId = '[Inser resource id here]'

How do i solve this?

Is there another, better way to do this?

Upvotes: 0

Views: 1523

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123891

In scenarios whithout explicit mapping between our entities, we can use only HQL.

Multiple classes may appear, resulting in a cartesian product or "cross" join. 

from Formula, Parameter
from Formula as form, Parameter as param

So in the case above we would have HQL like this:

FROM SearchView AS sv, SearchInvoiceResourceLookUpView AS srf
WHERE srf.InvoiceId = sv.Id
AND srf.ResourceId = '[Inser resource id here]'

Also some SELECT should be used and maybe DTO with Result Transformer...

Upvotes: 3

Related Questions