Using SQLProjection on a related entity with NHibernate

I am trying to work out how to perform the following using the Criteria API.

I have 3 related entities, e.g. Tenant, Building and Owner whe开发者_如何学Gore A Tenant has a Building property and a Building has an Owner Property. The Owner has First and Lastname properties.

I want to retrieve all Tenants and the Owners full name

i.e. the SQL would be

select o.FirstName + ' ' + o.LastName as OwnerName, t.Name from Tenant t inner join Building b on t.BuildingId = b.BuildingId inner join [Owner] o on b.OwnerId = o.OwnerId

When I create a query for Tenant using the Criteria API, is there a way I can add a projection which will give me the concatenated owner name? (I have not discovered how to add a SQLProjection for anything other than the root entity)

Thanks.

Add to each ICriteria (each entity) an alias. Then within projection you can use notation:

*alias.property*

Concatenation you should do within your DTO object.

In my View / DTO object (whatever you'd call it), I would add an additional property like this:

public MyDTO
{
    public string FirstName
    {
       get;
       private set;
    }

    public string LastName
    {
        get;
        private set;
    }

    public string Name
    {
        get
        {
            return String.Format ("{0} {1}", FirstName, LastName);
        } 
    }
}

Unfortunately I don't use the ICriteria API so I can't help you with that specifically. However, with Linq to NHibernate I'm pretty sure you would be able to do something like this:

var tenantsAndOwners = session.Linq<Tenant>().Select(tenant => new { TenantName = tenant.Name, OwnerFullName = tenant.Building.Owner.FirstName + " " + tenant.Building.Owner.LastName });

This would return an anonymous type with the two properties 'TenantName' and 'OwnerFullName'. The anonymous type would exist only in the current scope, i.e., could not be passed as the return type of a method.

I'm afraid this code is untested as I'm too busy clutching my coffee and shivering, but I'm pretty sure something like this would work. And Linq to NHibernate is absolutely awesome.