select superclass without joining subclass

[brandon law]

I'm trying to select count for a superclass that has a lot of records.  The select is joining all the subclasses( about 4 tables) that inherit from the super.

I use Fluent NHibernate to map the classes with SubClassMap.

How can I select only the superclass?

Thanks in advance!

setup:

/*------------------------------------------------------

CLASSES

------------------------------------------------------*/

public class Subscriber

{

     public virtual Guid Id {get;set;}

     public virutal string UserName {get;set;}

}

public class Organization : Subscriber

{

  public virtual string Name {get;set;}

}

I'm trying this to get the count of subscribers where Subscriber.username = variable..

/*------------------------------------------------------

DAL

------------------------------------------------------*/

var count = session.CreateCriteria<Subscriber>()

                    .Add(Restrictions.Eq("WwwUsername", login))

                    .SetProjection(Projections.Count("WwwUsername")).UniqueResult();

[Marios Skounakis]

I don't think that there is way other than sql to do what you want.

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Joseph Lam]

I really hope NH can support this. The whole idea of ORM is to provide
goodness of OO such as inheritance/polymorphism. Not having ability to
precisely control the level of inheritance in queries can cause
significant unneeded performance overhead, especially for more complex
OO scenarios which is where we need NH the most.

> > "nhusers" group.> To post to this group, send email tonh...@googlegroups.com.

[Gunnar Liljas]

Not including the join would break the contract (mapping) that has been defined. While it would be nice to have this as an option, I really don't think that it's a shortcoming of NHibernate. To get the best SQL queries possible, plain SQL should be used.

/G

2011/10/26 Joseph Lam <jla...@gmail.com>

To post to this group, send email to nhu...@googlegroups.com.

[Jason Meckley]

I think the problem is NH query, not the generated sql.
your query asks
count the number of subscribers where username = login
a subscriber on it's own doesn't mean anything to NH. it requires the subclasses. Therefore NH needs to join all the related table that define an entity.

instead you could define the query
count the number of subscriber ids where username = login.
This *might* alter how NH interprets the query. maybe NH would detect that you are only selecting a common property which is contained in a single table and not join the other tables.
HQL
select count(id) from Subscriber where wwwUserName = :login
(you can then convert this to criteria or queryover if you like)

remember: the queries you writing via NH are object queries that are then translated into sql queries. not sql queries directly. If you want the "best" sql then writing sql directly is your best option. otherwise you need to decide what trade offs you can accept between performance and maintainability.

[brandon law]

Thank you Jason!  That solved it.  Everything you all said makes sense.  I appreciate the input.. Still learning how to query with NHibernate.

--
You received this message because you are subscribed to the Google Groups "nhusers" group.

To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/t4GUSwDbj5cJ.

To post to this group, send email to nhu...@googlegroups.com.