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:
SELECT
query 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.Synchronize
method. This is to let NHibernate know about the tables which are referred to inside the SELECT
query.SELECT
statement should be mapped to the identifier property of the entity. This is not required if the name of the identifier column in the SELECT
statement matches the name of the identifier property on the entity. In the previous example, a call to the Id
method is added to show the readers how to map an identifier.SELECT
statement. In the example, a call to the Property
method 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 = 1155
Notice how NHibernate has placed our SELECT
statement as a nested SELECT
inside the query that it generated.