QueryOver with Join and Distinct

Question

I use the follow QueryOver:

var query = searchTermRepository.GetAllOver()
     .Where(Restrictions.On<Entities.SearchTerm>(c => c.Text).IsLike(filter.Value, MatchMode.Start))
     .Select(Projections.Distinct(Projections.Property<Entities.SearchTerm>(x => x.Contact)))
     .Inner.JoinQueryOver(x => x.Contact).Take(100);

This creates:

SELECT distinct TOP ( 100 /* @p0 */ ) this_.ContactId as y0_
FROM   SearchTerm this_
       inner join Contact contact1_
         on this_.ContactId = contact1_.Id
       left outer join Company contact1_1_
         on contact1_.Id = contact1_1_.Id
       left outer join Person contact1_2_
         on contact1_.Id = contact1_2_.Id
       left outer join Branch contact1_3_
         on contact1_.Id = contact1_3_.Id
       left outer join ContactGroup contact1_4_
         on contact1_.Id = contact1_4_.Id
WHERE  this_.Text like 'koc%%' /* @p1 */

But I want

SELECT distinct TOP ( 100 /* @p0 */ )  this_.ContactId as y0_, contact1_.*
FROM   SearchTerm this_
       inner join Contact contact1_
         on this_.ContactId = contact1_.Id
       left outer join Company contact1_1_
         on contact1_.Id = contact1_1_.Id
       left outer join Person contact1_2_
         on contact1_.Id = contact1_2_.Id
       left outer join Branch contact1_3_
         on contact1_.Id = contact1_3_.Id
       left outer join ContactGroup contact1_4_
         on contact1_.Id = contact1_4_.Id
WHERE  this_.Text like 'koc%%' /* @p1 */

I want select all Properties of Contact.

Best Regards, Thomas

Felonious

Answer 1

You do have to explicitly specify all of the columns that you want to project. There's no way around this that I know of.

Here's some quick code off the top of my head that uses QueryOver:

Contact contact = null;

Session
.QueryOver(() => contact)
.SelectList(list => list
    .Select(Projections.Distinct(Projections.Property(x => x.Contact))) 
    .Select(c => c.Id).WithAlias(() => contact.Id)
    .Select(c => c.FirstName).WithAlias(() => contact.FirstName)
... and so on

You'll then need to transform this to your object using the AliasToBean transformer.

Mitre

Do not forget about "OrderBy" which essential for distinct query – Melena

Answer 2

You arent projecting all of the properties of contact and should be. I don't know if there is a short hand way to say "all of the properties on contact" or if you just need to do one at a time, but right now you are just saying "distinct top 100 searchterm.contactid"

Microbalance

Answer 3

Previous code here listed didn't help me..

I had a issue with this aswell. Firstly the Distinct does work, but only after the QueryOver.List.ToList() method was called, so the query.skip wouldn't work properly, paging over the duplicates, creating the list, then reducing my paged amount because of the duplicates.

Easiest thing i found to do was.. simply create a list of unique ids first, then do your pagination on the Ids themselves..

Then on your result set you can simply perform a Id and retrieve the ids only in your newly paginated id result set.

//Create your query as usual.. apply criteria.. do what ever you want.

//Get a unique set of ids from the result set.
var idList = query.
.Select(x => x.Id)
.List<long>().Distinct().ToList();

//Do your pagination here over those ids
List<long> pagedIds = idList.Skip(0).Take(10).ToList();

//Here what used to be non distinct resultset, now is..
List<T> resultquery.Where(() => 
item.Id.IsIn(pagedIds))
.List<Person>()
.ToList();

Special thanks to.. https://julianjelfs.wordpress.com/2009/04/03/nhibernate-removing-duplicates-combined-with-paging/

Tisiphone