Geek Answers Handbook

QueryOver with connectivity and differences

I am using the following 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 to select all the properties of the contact.

Best regards, Thomas

+7
source share
3 answers

You need to explicitly specify all the columns that you want to project. I do not know about that.

Here is some quick code from 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

Then you will need to convert this to your object using the AliasToBean transformer.

+9
source

You arent designing all contact properties and should be. I don’t know if there is a short way to say “all properties on contact” or if you just need to do one at a time, but right now you just say “great version 100 searchterm.contactid”

0
source

The previous code here did not help me ..

I also had a problem with this. First, Distinct works, but only after the QueryOver.List.ToList () method has been called, so query.skip will not work properly, redrawing duplicates, creating a list, and then decreasing my calculated amount due to duplicates .

The simplest thing I found is to simply create a list of unique identifiers first, and then paginate the identifiers themselves.

Then, on your result set, you can simply execute the identifier and get the identifiers only in your new paginated 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 .. https://julianjelfs.wordpress.com/2009/04/03/nhibernate-removing-duplicates-combined-with-paging/

0
source

More articles:


All Articles