IsDistinct on PersonCollection - my bad or bad design?

Hi!

I need to get my hands on a PersonCollection based on the interests the different persons have:

//---------------------------------------

PersonInterestTableInfo pInterest = TablesInfo.GetPersonInterestTableInfo();

PersonCollection.CustomSearch query = new PersonCollection.CustomSearch();
query.Restriction = query.TableInfo.PersonId.Equal(pInterest.PersonId).
And(query.TableInfo.CountryId.Equal(S.Parameter(208))).
And(pInterest.PinterestIdx.In
(
S.Parameter(Convert.ToInt32(InterestType.ContractManager)),
S.Parameter(Convert.ToInt32(InterestType.TravelManager)))
);

//---------------------------------------

As this collection may return the same persons (by design), I want to add distinct to the query.

query.IsDistinct = true;

This compiles and runs. But when I execute the query I get the following exception from SuperOffice.Data: "It is not legal to use distinct when working with Large Objects (LOB)! Source: SuperOffice.Data.SQL.Select"

My question: Why enable me to do this at all? If my code is correct I would recommend you to remove functionality that isn't legal Smile

Question number 2: Do I really have to carry out a select first and then feed the PersonCollection with person_id in order to get what I want? I would rather have the IsDistinct to work as it should.

Re: IsDistinct on PersonCollection - my bad or bad design?

1. The query language is used in several places, so removing distinct would remove it everywhere, which would be bad. We could remove the PersonCollection type instead. hmm?

2. Person entities are rather large objects, populated eagerly. In other words, when you ask for person, you are getting the person's address, interests, postit text, phone, email, pictures (blob) and so on.

Entities are more of a one-at-a-time thing. They are intended for editing, not searches.

The entity collections seemed like a good idea at the time, but seem to cause trouble, because they hide more complexity than people think.

You are usually better off using a PersonRows collection instead - this will not pull in all the extra tables behind your back. IsDistinct will then work as you expect.

Oh - your query needs a join restriction to link the pInterest to the person table. Otherwise you are going to get a cross-product.

Von: Christian Mogensen 10. Okt 2010

RE: IsDistinct on PersonCollection - my bad or bad design?

Old thread but I just had same error with IsDistinct: "It is not legal to use distinct when working with Large Objects (LOB)! Source: SuperOffice.Data.SQL.Select"

I was using ContactRows.CustomSearch. So in what situations you can safely use IsDistinct?

 

Von: Mikko Tillikainen 13. Dez 2019