Views cater to the situation where we want to read data from multiple legacy tables and map it to a single domain entity. But under the hood, view is just a SELECT query. NHibernate offers another way to deal with the SELECT query directly, so that there is no need to have a view created in the database. This can be useful when you are not able to create views in the database for some reason. Some RDMBS have limited support for views, in which case we could use a subselect feature.
In a subselect feature, we would use the same query that we used to create the view, but this time, we will map to the entity directly. The following mapping definition demonstrates this:
public class EmployeeSubselectMapping : ClassMapping<Employee>
{
public EmployeeSubselectMapping()
{
Subselect(@"SELECT dbo.Employee.Id, dbo.Employee.Firstname,
dbo.Employee.Lastname, dbo.Employee.DateOfJoining,
dbo.Address.AddressLine1, dbo.Address.AddressLine2
FROM dbo.Address INNER JOIN
dbo.Employee ON dbo.Address.Employee_Id = dbo.Employee.Id");
Mutable(false);
Synchronize("Employee", "Address");
Id(e => e.Id, x => x.Column("Id"));
Property(e => e.Firstname, x => x.Column("Firstname"));
}
}There are four important elements to understand here:
- Subselect: This method is used to declare the
SELECTquery that should be used to query the entity being mapped. We have got the exact same SQL here, that we used to create the view earlier. - Table synchronization: Next we placed a call to the
Synchronizemethod. This is to let NHibernate know about the tables which are referred to inside theSELECTquery. - Mapping ID: We also need to configure which column that was returned from the
SELECTstatement should be mapped to the identifier property of the entity. This is not required if the name of the identifier column in theSELECTstatement matches the name of the identifier property on the entity. In the previous example, a call to theIdmethod is added to show the readers how to map an identifier. - Mapping properties: An identifier property is mapped in the same way that other properties also need to be mapped, if their names do not match with the names of the columns returned by the
SELECTstatement. In the example, a call to thePropertymethod shows how to do this.
Once this mapping is in place, we can query the Employee entity as we would query it normally. Internally, NHibernate would generate a subselect statement using the SELECT statement that we supplied in the mapping, and add any additional filtering criteria that we supply in our query. For instance, if we use ISession.Get<T> to query an entity instance with a particular ID, then NHibernate would generate the following SQL:
SELECT
employee0_.id as id0_0_
FROM
( SELECT dbo.Employee.Id, dbo.Employee.Firstname,
dbo.Employee.Lastname, dbo.Employee.DateOfJoining,
dbo.Address.AddressLine1, dbo.Address.AddressLine2
FROM
dbo.Address
INNER JOIN
dbo.Employee
ON dbo.Address.Employee_Id = dbo.Employee.Id ) employee0_
WHERE
employee0_.id=@p0;
@p0 = 1155Notice how NHibernate has placed our SELECT statement as a nested SELECT inside the query that it generated.