Hi,

when using IncludeFieldsList then "Primary Key and Foreign Key fields are always fetched, regardless of whether they're excluded or included" as "...they're necessary to fetch related entities later on"

My idea was to use IncludeFieldsList to define exactly which fields need to be fetched in order to utilize covering index thus avoid Key Lookups in execution plan when quering table with 500M+ rows.

But not all FKs are part of covering index and because IncludeFieldsList fetches also all FKs, then SELECT contains useless fields slowing down query execution due to wasteful Key Lookups for FK fields.

It would be great to have an option to suppress automatic fetching FKs in IncludeFieldsList, so that SELECT will contain only fields explicitly defined in IncludeFieldsList.

I seems now the only way is to use QuerySpec Projection.

The way it works internally means the FK fields have to be fetched, sorry. I've checked and there's no extension point to modify the query other than at the latest point right before the SQL is generated. How it works is basically it removes the IFieldPersistenceInfo objects from the array it has to pass to the query producer for the fields which are excluded. It uses a function (see e.g. DataAccessAdapterCore.FetchEntityUsingFilter, line 5161) to strip these out and it skips pk's and fk's. So it then passes it on to _queryCreationManager.CreateSelectDQ, and that basically passes the QueryParameters object to the DQE. If a FieldPersistenceInfo object is missing (so it's null), the field isn't generated in the projection list, so if in e.g. the DQE it is stripped out it won't end up in the projection.

However the whole system is setup so FKs are present, simply because if the entity uses prefetch paths they won't work for instance.

What I'm puzzled a bit about is that the fields in the projection are obtained from the resulting set, indices aren't used for that, they're used with joins and predicates, so the predicates applied in the query touch the index, hence it's a bit odd the fields in the projection will cause extra key lookups?