Hi,

I can override the following methods in my custom DataAccessAdapter to modify the generated SQL:

protected override void OnFetchEntity(IRetrievalQuery selectQuery, IEntityFields2 fieldsToFetch)
        {
            selectQuery.Command.CommandText = RewriteQuery(selectQuery);
            base.OnFetchEntity(selectQuery, fieldsToFetch);
        }

        protected override void OnFetchTypedList(IRetrievalQuery selectQuery, IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill)
        {
            selectQuery.Command.CommandText = RewriteQuery(selectQuery);
            base.OnFetchTypedList(selectQuery, fieldCollectionToFetch, dataTableToFill);
        }

        protected override void OnFetchTypedView(IRetrievalQuery selectQuery, IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill)
        {
            selectQuery.Command.CommandText = RewriteQuery(selectQuery);
            base.OnFetchTypedList(selectQuery, fieldCollectionToFetch, dataTableToFill);
        }

        protected override void OnFetchEntityCollection(IRetrievalQuery selectQuery, IEntityCollection2 entityCollectionToFetch)
        {
            selectQuery.Command.CommandText = RewriteQuery(selectQuery);
            base.OnFetchEntityCollection(selectQuery, entityCollectionToFetch);
        }

This works in LINQ for the ToList() method call, but what event do I override to modify the generated SQL sor the LINQ Count() method call.

The reason I'm trying to do this is that I need the generated SQL to filter records by a specified date range. The problem with sp_executesql is that if the date range is not an equality operator (i.e. a >= and/or <= where condition in my case for a date range), the index used uses the date range as a predicate search rather than a seek predicate. I want the date range filter condition to use the full capacity of my index by getting sql server to use the date range as a seek predicate. It seems that sql server won't use inequality as seek predicates for parameterized query conditions for sp_executesql. The only way to force stupid SQL server to use the inequality as a seek predicate is if you modify the generated sql to overwrite parameterized filter date condition with the actual parameter values. This overwriting strategy works like a charm for the ToList() linq calls as I can subscribe to the aforementioned (in my previous post) DataAccessAdapter events to modify the generated sql, but it appears that the LINQ count() uses a different DataAccessAdapter event. What is that event if any exposed?

Would overriding CreateRowCountDQ work?

I tried overriding CreateSelectDQ and it worked. However, is this the best way to do it?

You can do it overriding CreateRowCountDQ or CreateSelectDQ, as CreateRowCountDQ calls CreateSelectDQ. If you do it on CreateRowCountDQ you are on the outer Count query, unlike CreateSelectDQ where you are on the inner query. So the best place depends. Use CreateSelectDQ if you think you need this for all queries (not just the Count ones).

That works. Thanks