Limit collection to retrieve only recent entries for readonly entity

Question

The User entity can have thousands of UserOperations. Sometimes I don't want to retrieve (for readonly entity) all of them but only "the recent 10 OR not completed".

public class SimpleForm
{
    public class User : EntityBase
    {
        //  ...

        private ISet<UserOperation> _recentOperations = new HashedSet<UserOperation>();
        public virtual ISet<UserOperation> RecentOperations { get { return _recentOperations; } set { _recentOperations = value; } }
    }
}

So how can I specify it? I think I could use mapping overrides?

I understand I could make this with a seperate query but can it be done by entity mapping?

Also I wonder if there is a possibility to do the some for non-readonly entity where I can modify the collection of operations?

UPDATE

I tried to use

DateTime dateTime = (DateTime.UtcNow - TimeSpan.FromDays(15));
mapping.HasMany(x => x.RecentOperations)
       .Where(x => x.EndedAt == null || x.EndedAt < dateTime);

but it says "Unable to convert expression to SQL".

I replaced it with

mapping.HasMany(x => x.RecentOperations)
                .Where(x => x.EndedAt == null);

and now it throws null reference exception inside

в FluentNHibernate.Utils.ExpressionToSql.Convert(Object value) в FluentNHibernate.Utils.ExpressionToSql.Convert(ConstantExpression expression) в FluentNHibernate.Utils.ExpressionToSql.Convert[T](Expression`1 expression, UnaryExpression body)

I am not really sure about your question, but you need to do `OrderByDescending` on your `DateTime` field and then use `Enumerable.Take(10)` to get 10 recent records. — Habib, Aug 06 '14 at 13:12
@Habib But is it possible to map an entity in such way so I can just use Session.Get(id)? — Vlad, Aug 06 '14 at 13:14
I am not sure if that can be done, I will wait for the answers here as well — Habib, Aug 06 '14 at 13:17

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

There are 2 general ways how to filter mapped collections.

The first is a bit rigid, fixed, in a mapping defined where="" clause:

6.2. Mapping a Collection (...in fluent .Where(bool expr) or .Where(Sql statement string)

The second and maybe really suitable in this scenario, is dynamic version called filter:

18.1. NHibernate filters

NHibernate adds the ability to pre-define filter criteria and attach those filters at both a class and a collection level. A filter criteria is the ability to define a restriction clause very similiar to the existing "where" attribute available on the class and various collection elements. Except these filter conditions can be parameterized. The application can then make the decision at runtime whether given filters should be enabled and what their parameter values should be. Filters can be used like database views, but parameterized inside the application....

The implementation in fluent would look like this:

public class RecentFilter : FilterDefinition
{
    public RecentFilter()
    {
        WithName("RecentFilter")
            .WithCondition("( :EndedAtDate IS NULL OR EndedAt < :EndedAtDate )")
            .AddParameter("EndedAtDate",NHibernate.NHibernateUtil.DateTime);
    }
}

this is the filter, and here is its usage in a fluent mapping:

mapping
   .HasMany(x => x.RecentOperations)
   ...
   .ApplyFilter<RecentFilter>();

In runtime, we can turn filter on/off on the ISession level:

session.EnableFilter("RecentFilter")
       .SetParameter("EndedAtDate",DateTime.Now.AddDays(-15));

See also:

edited May 23 '17 at 12:29

Community

1
1

answered Aug 07 '14 at 15:40

Radim Köhler

122,561
47
239
335

Why it throws "No such filter configured when I call EnableFilter()? – Vlad Aug 11 '14 at 13:42
Check that filter was configured... I will be back later to show more...watch my links please... – Radim Köhler Aug 11 '14 at 13:49
Already solved that. But I have two entities: User and Friend.User (simplified user entity, the same table). Each user has a collection of friends. I need to create two seperate filters because I want to load friend operations for less days and I can specify filters only per session. Or there is another approach? – Vlad Aug 11 '14 at 14:02
Firstly sorry for later response, wasn't near... With filters you should play. Short summary: you can declar more filters per Collection or per Class. They do have different names. Then you can decide which of them will be applied. Even two of them for one class inside of one session. And even more... you can double map the collection... but one should be readonly. Then, one collection could have use one filter, while the other the second filter. Do not forget to use only ONE colleciton as Writable. Pleas, do play with that, at the end you will love it ;) good luck with NHibernate – Radim Köhler Aug 13 '14 at 12:59
By the way, can you explain why my Where() condition does not work? See the question. – Vlad Aug 13 '14 at 15:10
Yes, the issue would be related to the fact, that Where can contain: 1) simple boolean expression or 2) SQL string. But in your case it was not 2) sql string.. nor the **simple** boolean expression. Otherwords: convert your C# stuff into SQL expression and the Where will work ;) NHibernate is powerful ;) – Radim Köhler Aug 13 '14 at 15:11

Limit collection to retrieve only recent entries for readonly entity

1 Answers1

Linked