How to get great results using nHibernate and QueryOver API?

Question

I have this repository method

public IList<Message> ListMessagesBy(string text, IList<Tag> tags, int pageIndex, out int count, out int pageSize) { pageSize = 10; var likeString = string.Format("%{0}%", text); var query = session.QueryOver<Message>() .Where(Restrictions.On<Message>(m => m.Text).IsLike(likeString) || Restrictions.On<Message>(m => m.Fullname).IsLike(likeString)); if (tags.Count > 0) { var tagIds = tags.Select(t => t.Id).ToList(); query .JoinQueryOver<Tag>(m => m.Tags) .WhereRestrictionOn(t => t.Id).IsInG(tagIds); } count = 0; if(pageIndex < 0) { count = query.ToRowCountQuery().FutureValue<int>().Value; pageIndex = 0; } return query.OrderBy(m => m.Created).Desc.Skip(pageIndex * pageSize).Take(pageSize).List(); } 

You provide a free text search string and tag list. The problem is that if a message has more than one tag, it appears in duplicate time. I want to get an excellent result based on the Message object. I looked

 Projections.Distinct 

But this requires a list of properties for a single question. This post is my root of the entity, most likely a way to get this behavior without providing all the properties of the object?

Thanks in advance, Anders

Answer 1

If you are using the ICriteria API, you need to:

 .SetResultTransformer(new DistinctEntityRootTransformer()) 

If you use the QueryOver API, you need to:

 .TransformUsing(Transformers.DistinctRootEntity) 

But be careful, this all happens on the client side, so all duplicate lines are still stretched.

Answer 2

Try something like this

 public IPagedList<Client> Find(int pageIndex, int pageSize) { Client clientAlias = null; var query = Session.QueryOver<Client>(() => clientAlias) .Select( Projections.Distinct( Projections.ProjectionList() .Add(Projections.Property<Client>(x => x.Id).As("Id")) .Add(Projections.Property<Client>(x => x.Name).As("Name")) .Add(Projections.Property<Client>(x => x.Surname).As("Surname")) .Add(Projections.Property<Client>(x => x.GivenName).As("GivenName")) .Add(Projections.Property<Client>(x => x.EmailAddress).As("EmailAddress")) .Add(Projections.Property<Client>(x => x.MobilePhone).As("MobilePhone")) ) ) .TransformUsing(Transformers.AliasToBean<Client>()) .OrderBy(() => clientAlias.Surname).Asc .ThenBy(() => clientAlias.GivenName).Asc; var count = query .ToRowCountQuery() .FutureValue<int>(); return query .Take(pageSize) .Skip(Pagination.FirstResult(pageIndex, pageSize)) .List<Client>() .ToPagedList(pageIndex, pageSize, count.Value); } 

Answer 3

You can use SelectList and GroupBy, for example:

 tags.SelectList(t => t.SelectGroup(x => x.Id)) 

It should work and produce the same query plan as a separate one.

If you need multiple elements in a group, do something like:

 tags.SelectList(t => t.SelectGroup(x => x.Id) .SelectGroup(x => x.Name) ) 

Answer 4

I recently created a select select method based on the associated object type. It applies this to an IQueryOver object (class property). The method also has access to the nhibernate configuration. You can add them as method parameters. It needs work for production, but the method works fine in dev, only used it for one object.

This method was created because I am trying to list my data at the server level, and an excellent result transformer will not work.

After receiving a collection of objects (query.List ()), you may need to reload the objects in order to populate one on many child objects. Many to one match will be proxied for lazy loads.

  public void DistinctRootProjectionList<E>() { var classMapping = Context.Config.GetClassMapping(typeof(E)); var propertyIterator = classMapping.UnjoinedPropertyIterator; List<IProjection> projections = new List<IProjection>(); ProjectionList list = Projections.ProjectionList(); list.Add(Projections.Property(classMapping.IdentifierProperty.Name), classMapping.IdentifierProperty.Name); foreach (var item in propertyIterator) { if (item.Value.IsSimpleValue || item.Value.Type.IsEntityType) { list.Add(Projections.Property(item.Name), item.Name); } } query.UnderlyingCriteria.SetProjection(Projections.Distinct(list)); query.TransformUsing(Transformers.AliasToBean<E>()); } 

The code I used to load one of many relationships ... T is an entity type.

 for (int i = 0; i < resp.Data.Count; i++) { resp.Data[i] = session.Load<T>(GetInstanceIdValue(resp.Data[i])); }