Return a list of ids using ICriteria

dagda1 · **Joined:** Fri May 05, 2006 11:54 am **Posts:** 51

Hi,

Is it possible to return a list of ids using ICriteria?

So far I have the following but it just does not work:

Code:

return session.CreateCriteria(typeof(Plan))
                .SetProjection(Projections.Id())
                .Add(Expression.In("BusinessFunction.Uid", businessFunctionUids))
                .List<Plan>();

Can anyone point me in the right direction?

Cheers

Paul

merge_s.rottem · **Joined:** Tue Aug 23, 2005 5:52 am **Posts:** 335

Assuming BusinessFunction is a relationship property of Plan you need to add an alias to your criteria for BusinessFunction and use the alias in your IN expression instead of BusinessFunction.

Code:

return session.CreateCriteria(typeof(Plan))
                .CreateAlias("BusinessFunction", "bf")
                .SetProjection(Projections.Id())
                .Add(Expression.In("bf.Uid", businessFunctionUids))
                .List<Plan>();

Cheers,

Symon.

dagda1 · **Joined:** Fri May 05, 2006 11:54 am **Posts:** 51

Actually wnat I want to do is return a list of ids from the plans table.

I do not want to load the entire plan each time.

Is this possible with ICriteria or should I be using HQL?

Cheers

Paul

FlukeFan · **Posted:** Fri Jan 18, 2008 6:42 am

Hi,

When you set a projection, you are no longer returning the objects, so I'm imagining the .List<Plan>() will fail with an invalid cast? (You didn't describe the error you were getting.)

Code:

IList planIds =
    session.CreateCriteria(typeof(Plan))
        .CreateAlias("BusinessFunction", "BusinessFunction")
        .Add(Expression.In("BusinessFunction.Uid", businessFunctionUids)) 
        .SetProjection(Projections.Id())
        .List();

foreach (int id in planIds)
{
    Console.WriteLine(id);
}

Incidentally, I doubt there will be any performance improvement by only returning the IDs as opposed to the full object.

Regards,
Richard

dagda1 · **Joined:** Fri May 05, 2006 11:54 am **Posts:** 51

What I want to is get the ids and then use session.Get<T> to retrieve them from the session.

Does this seem reasonable.

The problem I have is different users get differnt lists depending on the businessunitid.

Can I use the querycache in this scenario?

Thanks

Paul

FlukeFan · **Posted:** Fri Jan 18, 2008 7:05 am

Hi Paul,

dagda1 wrote:

What I want to is get the ids and then use session.Get<T> to retrieve them from the session.

Personally then, I would just get the list of Plan objects (without a projection):

Code:

return
    session.CreateCriteria(typeof(Plan)) 
        .CreateAlias("BusinessFunction", "BusinessFunction") 
        .Add(Expression.In("BusinessFunction.Uid", businessFunctionUids)) 
        .List<Plan>(); 

Unless there's a performance problem after implementing it this way, I wouldn't resort to using the QueryCache (imho).

Regards,
Richard

dagda1 · **Joined:** Fri May 05, 2006 11:54 am **Posts:** 51

I thought only a session.Get retrieved objects from the session.

Thanks for that.

That is a big help.

dagda1 · **Joined:** Fri May 05, 2006 11:54 am **Posts:** 51

Hi,

I have the following generic function that I use:

Code:

public IList<T> GetAll<T>(params string[] sortProperties)
{
    ISession session = _sessionManager.OpenSession();

    ICriteria crit = session.CreateCriteria(typeof(T));
    if (sortProperties != null)
    {
        foreach (string sortProperty in sortProperties)
        {
            crit.AddOrder(Order.Asc(sortProperty));
        }
    }
    return crit.List<T>();  
}

I have been runing a trace through Sql Server and when I run the above method the objects are definitely not coming fom the cache when I call this method.

The app.I am working on is an ASP.NET and I have an HTTP Module that loads the authenticated user using session.Get<User>. The user is definitely coming from the cache but the objects retrieved via thethe above method a re not.

Am I right in thinking only objects being retrieved via session.Get are coming from the session

FlukeFan · **Posted:** Fri Jan 18, 2008 9:09 am

Hi Paul,

I'm not sure what you mean by 'coming from the session'.

If you you session.Load(), you (typically) get a proxy object that won't hit the database until its properties/methods are accessed.

If you use session.Get(), the database will be hit to get the object (unless its already in the Identity Map).

In both of the above scenarios, if the object has already been loaded from the database, then NHibernate will return you the object straight from memory without rehitting the database. (using its Identity Map).

If you use either HQL or ICriteria to issue a query, the database will be hit.

Regards,
Richard

michal · **Posted:** Sat Jan 19, 2008 3:19 pm

Hi

If I understand well you want to make use 2-nd level cache for "Plan" objects. As far I know if you use query cache, and mark the query as cacheable, if you decide to return full objects, nhibernate will do two important things :
1) cache list of identifiers in the query cache
2) cache every entity returned by query in the 2-nd level cache

It is true for HQL queries, you can test it very well with log4net (priority="ALL"):

First run : (ceche is empty)

Code:

2008-01-19 20:08:57,110 [8] find: from interfejsLib.Kolumna k where k.User=:user and k.Grid=:grid
(...)
2008-01-19 20:08:57,111 [8] checking cached query results in region: NHibernate.Cache.StandardQueryCache
2008-01-19 20:08:57,111 [8] Fetching object 'NHibernate-Cache:NHibernate.Cache.StandardQueryCache:sql: select kolumna0_.Id as Id78_, kolumna0_.IdUser as IdUser78_, kolumna0_.IdProfil as IdProfil78_, kolumna0_.Grid as Grid78_, kolumna0_.Nazwa as Nazwa78_ from galKolumny kolumna0_ where (kolumna0_.IdUser=? )and(kolumna0_.Grid=? ); parameters: []; named parameters: {grid=webartykulylista_dgArtykuly, user=User#1}; first row: 0@49980227' from the cache.
2008-01-19 20:08:57,111 [8] query results were not found in cache
2008-01-19 20:08:57,111 [8] HQL: from interfejsLib.Kolumna k where k.User=:user and k.Grid=:grid
(...)
2008-01-19 20:08:57,112 [8] processing result set
(...)
2008-01-19 20:08:57,229 [8] adding entity to second-level cache [interfejsLib.Kolumna#16]
2008-01-19 20:08:57,229 [8] Caching: interfejsLib.Kolumna, interfejsLib, Version=2.3.2940.35563, Culture=neutral, PublicKeyToken=null#16
(...)
2008-01-19 20:08:57,230 [8] adding entity to second-level cache [interfejsLib.Kolumna#19]
2008-01-19 20:08:57,230 [8] Caching: interfejsLib.Kolumna, interfejsLib, Version=2.3.2940.35563, Culture=neutral, PublicKeyToken=null#19
(...)
2008-01-19 20:08:57,231 [8] caching query results in region: NHibernate.Cache.StandardQueryCache
2008-01-19 20:08:57,231 [8] adding new data: key=NHibernate-Cache:NHibernate.Cache.StandardQueryCache:sql: select kolumna0_.Id as Id78_, kolumna0_.IdUser as IdUser78_, kolumna0_.IdProfil as IdProfil78_, kolumna0_.Grid as Grid78_, kolumna0_.Nazwa as Nazwa78_ from galKolumny kolumna0_ where (kolumna0_.IdUser=? )and(kolumna0_.Grid=? ); parameters: []; named parameters: {grid=webartykulylista_dgArtykuly, user=User#1}; first row: 0@49980227&value=System.Collections.ArrayList

Second run : (cache is full - no database hit!)

Code:

2008-01-19 20:12:15,449 [8] find: from interfejsLib.Kolumna k where k.User=:user and k.Grid=:grid
(...)
2008-01-19 20:12:15,452 [8] checking cached query results in region: NHibernate.Cache.StandardQueryCache
(...)
2008-01-19 20:12:15,452 [8] returning cached query results
2008-01-19 20:12:15,452 [8] loading [Kolumna#16]
2008-01-19 20:12:15,452 [8] attempting to resolve [Kolumna#16]
2008-01-19 20:12:15,452 [8] Cache lookup: interfejsLib.Kolumna, interfejsLib, Version=2.3.2940.35563, Culture=neutral, PublicKeyToken=null#16
2008-01-19 20:12:15,452 [8] Fetching object 'NHibernate-Cache:interfejsLib.Kolumna:interfejsLib.Kolumna, interfejsLib, Version=2.3.2940.35563, Culture=neutral, PublicKeyToken=null#16@16' from the cache.
2008-01-19 20:12:15,452 [8] Cache hit: interfejsLib.Kolumna, interfejsLib, Version=2.3.2940.35563, Culture=neutral, PublicKeyToken=null#16
2008-01-19 20:12:15,453 [8] resolved object in second-level cache [interfejsLib.Kolumna#16]
(...)
2008-01-19 20:12:15,453 [8] loading [Kolumna#19]
2008-01-19 20:12:15,453 [8] attempting to resolve [Kolumna#19]
2008-01-19 20:12:15,453 [8] Cache lookup: interfejsLib.Kolumna, interfejsLib, Version=2.3.2940.35563, Culture=neutral, PublicKeyToken=null#19
2008-01-19 20:12:15,453 [8] Fetching object 'NHibernate-Cache:interfejsLib.Kolumna:interfejsLib.Kolumna, interfejsLib, Version=2.3.2940.35563, Culture=neutral, PublicKeyToken=null#19@19' from the cache.
2008-01-19 20:12:15,453 [8] Cache hit: interfejsLib.Kolumna, interfejsLib, Version=2.3.2940.35563, Culture=neutral, PublicKeyToken=null#19
2008-01-19 20:12:15,453 [8] resolved object in second-level cache [interfejsLib.Kolumna#19]
(...)

If it is true for ICriteria too (I suggest you to check it), best approach would be fetching full objects with query cache and 2-nd level cache enabled.