Hello. I'm fetching an EntityCollection using an INNER JOIN in order to filter on some field, and the generated query always contains a DISTINCT statement. Can that be programmatically removed? I tried getting access to the query internals using QuerySpec.OnPrepareForExecutionCallBack, but AllowDuplicates is a getter and cannot be set.

Not sure I'm following. Why do you want duplicates when you are fetching entities?

Maybe you should be fetching a dynamic query instead of entities.

I don't want duplicate entities. But my query is backed by a .NET cache dependency and the SQL Query Notification mechanism does not seem to function with DISTINCT. Using Dynamic Queries may be a possible workaround. I will try it out.

Using DynamicQuery instead of EntityQuery worked for me. Thanks. I'd be interested though to know if there's a way to suppress DISTINCT even for entity fetches.

The engine emits 'Distinct' into the query if it suspects there are duplicates in the resultset. E.g. select c.* from Customer c inner join Order o ... will result in duplicate customer rows, so it emits distinct. This is an optimization to limit a lot of duplicate rows being fetched as filtering out duplicated entity instances on the client is slow.

There's no API for disabling this behavior as it's in general what you want. Is there a reason the DISTINCT keyword can't be executed (you mention a cache but not why distinct fails).

There's a way to remove it tho, which is to alter the SQL query that's executed. Using Adapter, create a partial class of DataAccessAdapter and add some flag to signal DISTINCT should be removed (so you only remove it in certain queries).

Then override OnFetchEntityCollection(..) You'll get the IRetrievalQuery passed in as the first argument. In that object, the Command's CommandText can be altered based on e.g. your flag you added, and with a simple string replace you could e.g. replace SELECT DISTINCT with SELECT. This is pretty low level but could work in filtering it out in the cases you can't avoid it.

Here's the document that says DISTINCT should not be part of the query: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms181122(v=sql.105)

Thanks for the low-level workaround!

Ah, thanks I see there's another workaround The DynamicQueryEngine has a setting, DistinctFilteringPreferenceToUse, which if set to DistinctFilteringPreferenceType.AlwaysClientSide will mark the query to be filtered client side for duplicates and will not emit DISTINCT. To set this setting you don't want to do that on a global level (which is possible) but you can do that on a per adapter instance level, by overriding CreateDynamicQueryEngine in a derived class of DataAccessAdapter. In the override, first call the base method, then set the property DistinctFilteringPreferenceToUse, based on a flag you add whether you want distinct or not