You can certainly do this with NHibernate. However, I disagree that we rarely
query just one table. We rarely do joins like the one illustrated in a business
domain context. Your example query is more common for reporting or presentation
needs. In many systems -- commonly small to medium sized systems -- you use a
normalized relational database to handle multiple concerns: business domain,
reporting and presentation. For larger system sit's not uncommon to have a
separate de-normalized (or NoSql) read data store to support reporting and
presentation.
Oaky, enough philosophy. I assume that we are talking about the former case.
You can do it one of 4 ways:
1) Fetch lists of entities, and map them to DTOs in memory. You can hand-code
your mapping or use a tool like AutoMapper.
2) Fetch DTOs using NH Linq.
3) Fetch DTOs using ICriteria.
4) Fetch DTOs using HQL.
Option #1 is pretty obvious. Options 3 and 4 you can find what you need in the
Google. Hint: search for AliasToBeanTransfromer.
Here is an example of case #2:
var employees = session.Query<Employee>()
.Where(x => x.EmploymentDate < date && x.Status == UserStatus.Active) //for
example
.Select(x => new EmployeeListDto
{
Id = x.id,
FirstName = x.FirstName,
LastName = x.LastName,
CommitteeName = x.Committee.Name
});
Notice that I am returning at DTO not an entity. It's a simple data container
with no behavior. I'm marshaling data to my UI and that's it. If I want to do
something to one of these employees, I will fetch the entity using
session.Get(id), and there I will find all kinds of business logic and behavior.
Using Linq to get DTOs straight from the database is way cool, but realize that
it currently has pretty drastic limitations. Usually, I try Linq first, then if
I run into trouble I move on to ICriteria, then HQL, then in memory and in very
rare cases I might even (gasp) use straight SQL.
On Thursday, September 29, 2011 at 10:31 PM, Influently NHiberater wrote:
> How does an ORM framework handle joins like what we do in plain SQL? Is it
> the case that an ORM framework doesn't explicitly join two or more tables?
>
> Suppose I have the following two tables in my database:
>
> Employees
> {
> Eid int primary key,
> FirstName varchar(20),
> LastName varchar(20)
> }
>
> Committees
> {
> Cid int primary key,
> Name varchar(50), <--- This is the committee name.
> ChairId int references Employees.Eid
> }
>
> And I have my POCO classes, EmployeeMap and CommitteeMap defined.
>
> Now how can I use Linq or FluentNHibernate Linq to get something like what
> the following SQL query would give me?
>
> SELECT e.FirstName, e.LastName, c.Name
> FROM Employees e
> INNER JOIN Committees c
> ON e.Eid = c.ChairId
>
> All FluentNHibernate examples I've seen online query only one table, and
> which is pretty simple and straightforward, but in reality, we rarely query
> only one table.
>
> Can anyone give me a working example or a pointer to such a thing? Thank you.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Fluent NHibernate" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/fluent-nhibernate/-/gqPPQqqV7D0J.
> To post to this group, send email to [email protected]
> (mailto:[email protected]).
> To unsubscribe from this group, send email to
> [email protected]
> (mailto:[email protected]).
> For more options, visit this group at
> http://groups.google.com/group/fluent-nhibernate?hl=en.
--
You received this message because you are subscribed to the Google Groups
"Fluent NHibernate" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/fluent-nhibernate?hl=en.
