CODEWITHSUNDEEP
nhibernatefluent-nhibernate
Anthony
Anthony

Reputation: 9571

Why is NHibernate pulling more data than is needed?

I'm running the following query using NHibernate:

var query = session.QueryOver<TaskMeeting>()
    .JoinQueryOver(x => x.Task)
    .Where(x => x.CloseOn == null)
    .List();

Which then generates the following SQL:

SELECT
    this_.Id as Id89_1_,
    this_.TaskMeetingTypeID as TaskMeet2_89_1_,
    this_.DateTime as DateTime89_1_,
    this_.Description as Descript4_89_1_,
    this_.TaskID as TaskID89_1_,
    ltaskalias1_.Id as Id87_0_,
    ltaskalias1_.Title as Title87_0_,
    ltaskalias1_.Description as Descript7_87_0_,
    ltaskalias1_.CreatedOn as CreatedOn87_0_,
    ltaskalias1_.LastUpdated as LastUpda9_87_0_,
    ltaskalias1_.ClosedOn as ClosedOn87_0_,
FROM
    dbo.[TaskMeeting] this_ 
inner join
    dbo.[Task] ltaskalias1_ 
        on this_.TaskID=ltaskalias1_.Id 
WHERE
    ltaskalias1_.ClosedOn is null

Is pulling joined table information normal behavior? I would think it should only be selecting data pertaining to the root entity unless I specify a .Fetch clause or manually select the data.

I'm using Fluent NHibernate for the class mappings, but they are basic mappings for something like this, no eager loading specified - just simple Map and References where appropriate.

I've tried removing the where clause thinking perhaps that was the cause. The additional SELECTs continue to persist unless I remove the join itself.

Upvotes: 0

Views: 114

Answers (2)

Thomas Lazar
Thomas Lazar

Reputation: 335

The way you queried your data, you get back a generic IList<> containing TaskMeeting entities. So it's normal for NHibernate to query all columns of your TaskMeeting table to put that data into your TaskMeeting entity properties. But i guess that is known already.

But because you want to restrict the data through another table you have to make a join to it. And that's the expensive part for the database server. Querying those few additional columns is peanuts compared to it and NHibernate might as well use the data to fill the Task references in your TaskMeeting entities.

At least that's how i understand it.

Upvotes: 1

Fran
Fran

Reputation: 6520

The quick answer is you told it to. You called JoinQueryOver which is going to create a join query on the task.

An easier way to get only what you want is to create a linq query and project only the fields you want into am anonymous type. That will generate a query with only the columns declared in the anonymous type.

var meetings = from m in session.Query () where m.Task.ClosedOn <> null select new { somefield = m.Task.Name, ...};

Upvotes: 0

Related Questions