Plan Guide for the LINQ-SQL Query

Question

I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in 2 seconds. I checked the query plan cache and noticed that the query is using the cached plan.I followed the below steps to create a plan guide for that query;

• Called sp_create_plan_guide_from_handle to create a plan.

sp_create_plan_guide_from_handle 'Search_With_County',0x06000600950F7C38C0FD5F135102000001000000000000000000000000000000000000000000000000000000

• From SSMS i did Drop and create for that plan guide and changed the @hints from the XML to N'OPTION(RECOMPILE)'

The Plan guide looks like the one below.I have simplified the query.

EXEC sp_create_plan_guide @name = N'[IncidentSerach_With_County]', @stmt = N'SELECT TOP (@p__linq__7) 
    [SearchModel].[IncidentName] AS [IncidentName], 
    [SearchModel].[NatureOfIncident] AS [NatureOfIncident], 
    [SearchModel].[InvestBy] AS [InvestBy], 
    [SearchModel].[SecondaryOfficer] AS [SecondaryOfficer], 
    [SearchModel].[Disposed] AS [Disposed], 
    [SearchModel].[Property] AS [Property], 
    [SearchModel].[PropertyDescriptions] AS [PropertyDescriptions], 
    [SearchModel].[Forfeiture] AS [Forfeiture], 
    [SearchModel].[ColdCaseNumber] AS [ColdCaseNumber], 
    [SearchModel].[DateOccurred] AS [DateOccurred], 
    [SearchModel].[TimeOccurred] AS [TimeOccurred], 
    [SearchModel].[FirstSubmissionDate] AS [FirstSubmissionDate], 
    [SearchModel].[IsReportOffline] AS [IsReportOffline], 
    [SearchModel].[IsSupplement] AS [IsSupplement], 
    [SearchModel].[PrimaryOfficerId] AS [PrimaryOfficerId], 
    [SearchModel].[SecurityLevel] AS [SecurityLevel]
    FROM [dbo].[SearchModel] AS [SearchModel]) AS [Extent1]
            LEFT OUTER JOIN [dbo].[IncidentDetailsPages] AS [Extent2] ON [Extent1].[IncidentDetailPageId] = [Extent2].[Id]
            WHERE ((N''Public'' = [Extent1].[SecurityLevel]) OR (N''Private'' = [Extent1].[SecurityLevel]) OR ([Extent1].[PrimaryOfficerId] = @p__linq__0) ) AND ([Extent1].[ReportDateTime] >= @p__linq__3) AND ([Extent1].[ReportDateTime] <= @p__linq__4) AND (([Extent1].[County] = @p__linq__5) OR (([Extent1].[County] IS NULL) AND (@p__linq__5 IS NULL))) AND ( NOT EXISTS (SELECT 
                1 AS [C1]
                FROM  (SELECT 
                    N''MI3300287'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                UNION ALL
                    SELECT 
                    N''MI3300567'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
                UNION ALL
                    SELECT 
                    N''MI3300568'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
                WHERE ([UnionAll2].[C1] = [Extent1].[AgencyOri]) OR (CASE WHEN ([UnionAll2].[C1] <> [Extent1].[AgencyOri]) THEN cast(1 as bit) WHEN ([UnionAll2].[C1] = [Extent1].[AgencyOri]) THEN cast(0 as bit) END IS NULL)
            )) AND ([Extent1].[IsReportOffline] <> 1) AND ([Extent1].[IsSupplement] <> 1)
        )  AS [Project6]
    )  AS [Project6]
    WHERE [Project6].[row_number] > @p__linq__6
    ORDER BY [Project6].[AgencyOri] ASC, [Project6].[ReportIncidentNumber] ASC, [Project6].[SupplementNumber] ASC', @type = N'SQL', @module_or_batch = N'SELECT TOP (@p__linq__7) 
    [SearchModel].[IncidentName] AS [IncidentName], 
    [SearchModel].[NatureOfIncident] AS [NatureOfIncident], 
    [SearchModel].[InvestBy] AS [InvestBy], 
    [SearchModel].[SecondaryOfficer] AS [SecondaryOfficer], 
    [SearchModel].[Disposed] AS [Disposed], 
    [SearchModel].[Property] AS [Property], 
    [SearchModel].[PropertyDescriptions] AS [PropertyDescriptions], 
    [SearchModel].[Forfeiture] AS [Forfeiture], 
    [SearchModel].[ColdCaseNumber] AS [ColdCaseNumber], 
    [SearchModel].[DateOccurred] AS [DateOccurred], 
    [SearchModel].[TimeOccurred] AS [TimeOccurred], 
    [SearchModel].[FirstSubmissionDate] AS [FirstSubmissionDate], 
    [SearchModel].[IsReportOffline] AS [IsReportOffline], 
    [SearchModel].[IsSupplement] AS [IsSupplement], 
    [SearchModel].[PrimaryOfficerId] AS [PrimaryOfficerId], 
    [SearchModel].[SecurityLevel] AS [SecurityLevel]
    FROM [dbo].[SearchModel] AS [SearchModel]) AS [Extent1]
            LEFT OUTER JOIN [dbo].[IncidentDetailsPages] AS [Extent2] ON [Extent1].[IncidentDetailPageId] = [Extent2].[Id]
            WHERE ((N''Public'' = [Extent1].[SecurityLevel]) OR (N''Private'' = [Extent1].[SecurityLevel]) OR ([Extent1].[PrimaryOfficerId] = @p__linq__0)) AND ([Extent1].[ReportDateTime] >= @p__linq__3) AND ([Extent1].[ReportDateTime] <= @p__linq__4) AND (([Extent1].[County] = @p__linq__5) OR (([Extent1].[County] IS NULL) AND (@p__linq__5 IS NULL))) AND ( NOT EXISTS (SELECT 
                1 AS [C1]
                FROM  (SELECT 
                    N''MI3300287'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                UNION ALL
                    SELECT 
                    N''MI3300567'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
                UNION ALL
                    SELECT 
                    N''MI3300568'' AS [C1]
                    FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
                WHERE ([UnionAll2].[C1] = [Extent1].[AgencyOri]) OR (CASE WHEN ([UnionAll2].[C1] <> [Extent1].[AgencyOri]) THEN cast(1 as bit) WHEN ([UnionAll2].[C1] = [Extent1].[AgencyOri]) THEN cast(0 as bit) END IS NULL)
            )) AND ([Extent1].[IsReportOffline] <> 1) AND ([Extent1].[IsSupplement] <> 1)
        )  AS [Project6]
    )  AS [Project6]
    WHERE [Project6].[row_number] > @p__linq__6
    ORDER BY [Project6].[AgencyOri] ASC, [Project6].[ReportIncidentNumber] ASC, [Project6].[SupplementNumber] ASC', @params = N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 varchar(8000),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 varchar(8000),@p__linq__6 int,@p__linq__7 int', @hints = N'OPTION(RECOMPILE)'
GO

After creating the plan guide also when i run the query from application it is slow.The query is still using the plan from plan cache.

It was supposed to be simple and i used sp_create_plan_guide_from_handle so the query text is exact.

Please suggest if you find any issues in the approach i followed.This is the first time i am creating plan guide.

This is the reference i followed.

As i am not able to give OPTION(RECOMPILE) on the LINQ-SQL generated query,i am generating plan guide with query hint OPTION(RECOMPILE).

I would like to know if this is how someone create plan guide for my scenario.

The reasons i am going with plan guide is as below:

I have tried to improve the query earlier and i was successful to an extend. I did change the views used in the query and that made the query to run parallel which brough big impact in the performance. I am not able to change a single part in the dynamic sql ,as it is LINQ-SQL generated query and application code change is not a viable option for me. So i have to use plan guides/query store as far as i know.

So please provide me a solution which is suitable to my situation.