How do you do joins using ORM like FluentNHibernate?

[Influently NHiberater]

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.

[Joe Brockhaus]

How you have ur mappings setup is kinda crucial.

If you want the employee always loaded with the committee (i.e. -
every time you session.load<committee>(id), you want the query to be a
join), then you need to have in the committee mapping constructor:
base.references<employee>(x=>x.Employee)
.keycolumn("empidcolname")
.fetch.join();

I'm not exactly sure on that syntax (specifically the .references &
keycolumn. It might be association & foreignkey?) .. But that should
be pretty close.

You probably don't want the employee having a bi-directional reference
back to the committe (for employee re-use in other associations.
depends on ur model tho. It could lead to some nasty n+1 sql if
committee has other collections,dependencies), so u wont need an
association/reference in the employee.

> --
> 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 fluent-n...@googlegroups.com.
> To unsubscribe from this group, send email to
> fluent-nhibern...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/fluent-nhibernate?hl=en.
>
>

--
Sent from my mobile device

------
Joe Brockhaus
joe.br...@gmail.com
------------

[Tim Scott]

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.

> --
> 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 fluent-n...@googlegroups.com (mailto:fluent-n...@googlegroups.com).
> To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com (mailto:fluent-nhibern...@googlegroups.com).

[Joe Brockhaus]

does using Linq like that, with a select, equate to projections with criteria? (aka, would it ignore fetching an association that is not included in the select, that might be mapped with a join? it's just much more legible than criteria :D)

------
Joe Brockhaus
joe.br...@gmail.com
------------

To post to this group, send email to fluent-n...@googlegroups.com.
To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com.

[Tim Scott]

Yes, Select is a projection. It basically the same as ICriteria using a projection list and AliasToBeanTransformer.

I find the best way to use the NH Linq provider is to start by assuming it's a clean abstraction. That is, act like you're dealing with an in memory collection. When it breaks, and it will in more complex scenarios, fall back to ICriteria.

On Saturday, October 8, 2011 at 10:08 AM, Joe Brockhaus wrote:

> does using Linq like that, with a select, equate to projections with criteria? (aka, would it ignore fetching an association that is not included in the select, that might be mapped with a join? it's just much more legible than criteria :D)
>
> ------
> Joe Brockhaus

> joe.br...@gmail.com (mailto:joe.br...@gmail.com)

> ------------
>
>
> On Fri, Sep 30, 2011 at 12:14 AM, Tim Scott <tsc...@lunaversesoftware.com (mailto:tsc...@lunaversesoftware.com)> wrote:
> > 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 (http://x.id),

> > FirstName = x.FirstName,
> > LastName = x.LastName,

> > CommitteeName = x.Committee.Name (http://x.Committee.Name)

> > > To post to this group, send email to fluent-n...@googlegroups.com (mailto:fluent-n...@googlegroups.com) (mailto:fluent-n...@googlegroups.com).
> > > To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com (mailto:fluent-nhibernate%2Bunsu...@googlegroups.com) (mailto:fluent-nhibern...@googlegroups.com (mailto:fluent-nhibernate%2Bunsu...@googlegroups.com)).

> > > 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 fluent-n...@googlegroups.com (mailto:fluent-n...@googlegroups.com).

> > To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com (mailto:fluent-nhibernate%2Bunsu...@googlegroups.com).