EntityDataSet fetch all data and then applying where clause

Question

I am using a asp.net GridView control and setting DatasourceId to EntityDataSource as below .in the page load setting the GridDataSource.EntityTypeFilter to a View name and also adding a where clause as GridDataSource.Where = sWhereClause

The View has million records but the Where condition filter out the record .The EntityDataSource first getting all million record in Sub-Query then applying the Where which timing out command. its generating the query as below. I want the where clause shouldgo with ViewName select statement itself not with sub-query table Extent1.

SELECT TOP (20) 
[Filter1].[COL1],
[Filter1].[COL2]
[Filter1].[Col3] 
FROM ( 
SELECT [Extent1].[COL1] , [Extent1].[COL2], [Extent1].[COL3]
, row_number() OVER (ORDER BY [Extent1].[COL1] ASC
) AS [row_number]

FROM 
(
SELECT 
ViewName.V1 , 
ViewName.V2
ViewName.V3
ViewName.V4

FROM [dbo].ViewName
)
AS [Extent1]


WHERE ([Extent1].[COL1] LIKE '%FilterValue%') 
    OR ([Extent1].[COL1] LIKE '%FilterValue%') OR ([Extent1].[COL2] LIKE '%FilterValue%') OR ([Extent1].[COL3] LIKE '%FilterValue%') )
)  AS [Filter1]
WHERE [Filter1].[row_number] > 0
ORDER BY [Filter1].COL1] ASC

Thanks and appreciate any help in advance.

Answer 1

The EntityDataSource first getting all million record in Sub-Query then applying the Where which timing out command

Just because the WHERE clause is not in the subquery does not mean that subquery isn't filtered. SQL Server (assuming that's what you're using), can "push down" the WHERE clause predicates into the subquery, and into base tables in view definition.

But that won't make any difference here as your multiple LIKE predicates have to be evaluated for every single row in the output of the view, then all the rows have to be sorted to find the top 20.