OData / EF vs. Rewritten queries

odata.sql

SELECT TOP (30) 
[Project1].[PackageRegistrationKey] AS [PackageRegistrationKey], 
[Project1].[Id] AS [Id], 
[Project1].[Version] AS [Version], 
[Project1].[FlattenedAuthors] AS [FlattenedAuthors], 
[Project1].[Copyright] AS [Copyright], 
[Project1].[Created] AS [Created], 
[Project1].[FlattenedDependencies] AS [FlattenedDependencies], 
[Project1].[Description] AS [Description], 
[Project1].[DownloadCount1] AS [DownloadCount], 
[Project1].[ExternalPackageUrl] AS [ExternalPackageUrl], 
[Project1].[C1] AS [C1], 
[Project1].[IconUrl] AS [IconUrl], 
[Project1].[IsLatestStable] AS [IsLatestStable], 
[Project1].[Language] AS [Language], 
[Project1].[LastUpdated] AS [LastUpdated], 
[Project1].[LicenseUrl] AS [LicenseUrl], 
[Project1].[Hash] AS [Hash], 
[Project1].[HashAlgorithm] AS [HashAlgorithm], 
[Project1].[PackageFileSize] AS [PackageFileSize], 
[Project1].[ProjectUrl] AS [ProjectUrl], 
[Project1].[C2] AS [C2], 
[Project1].[ReleaseNotes] AS [ReleaseNotes], 
[Project1].[C3] AS [C3], 
[Project1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], 
[Project1].[Summary] AS [Summary], 
[Project1].[C4] AS [C4], 
[Project1].[C5] AS [C5], 
[Project1].[DownloadCount] AS [DownloadCount1], 
[Project1].[C6] AS [C6]
FROM ( SELECT [Project1].[PackageRegistrationKey] AS [PackageRegistrationKey], [Project1].[Copyright] AS [Copyright], [Project1].[Created] AS [Created], [Project1].[Description] AS [Description], [Project1].[ReleaseNotes] AS [ReleaseNotes], [Project1].[DownloadCount] AS [DownloadCount], [Project1].[ExternalPackageUrl] AS [ExternalPackageUrl], [Project1].[HashAlgorithm] AS [HashAlgorithm], [Project1].[Hash] AS [Hash], [Project1].[IconUrl] AS [IconUrl], [Project1].[IsLatestStable] AS [IsLatestStable], [Project1].[LastUpdated] AS [LastUpdated], [Project1].[LicenseUrl] AS [LicenseUrl], [Project1].[Language] AS [Language], [Project1].[PackageFileSize] AS [PackageFileSize], [Project1].[ProjectUrl] AS [ProjectUrl], [Project1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], [Project1].[Summary] AS [Summary], [Project1].[Version] AS [Version], [Project1].[FlattenedAuthors] AS [FlattenedAuthors], [Project1].[FlattenedDependencies] AS [FlattenedDependencies], [Project1].[Id] AS [Id], [Project1].[DownloadCount1] AS [DownloadCount1], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[C4] AS [C4], [Project1].[C5] AS [C5], [Project1].[C6] AS [C6], row_number() OVER (ORDER BY [Project1].[DownloadCount1] DESC, [Project1].[Id] ASC) AS [row_number]
  FROM ( SELECT 
    [Extent1].[PackageRegistrationKey] AS [PackageRegistrationKey], 
    [Extent1].[Copyright] AS [Copyright], 
    [Extent1].[Created] AS [Created], 
    [Extent1].[Description] AS [Description], 
    [Extent1].[ReleaseNotes] AS [ReleaseNotes], 
    [Extent1].[DownloadCount] AS [DownloadCount], 
    [Extent1].[ExternalPackageUrl] AS [ExternalPackageUrl], 
    [Extent1].[HashAlgorithm] AS [HashAlgorithm], 
    [Extent1].[Hash] AS [Hash], 
    [Extent1].[IconUrl] AS [IconUrl], 
    [Extent1].[IsLatestStable] AS [IsLatestStable], 
    [Extent1].[LastUpdated] AS [LastUpdated], 
    [Extent1].[LicenseUrl] AS [LicenseUrl], 
    [Extent1].[Language] AS [Language], 
    [Extent1].[PackageFileSize] AS [PackageFileSize], 
    [Extent1].[ProjectUrl] AS [ProjectUrl], 
    [Extent1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], 
    [Extent1].[Summary] AS [Summary], 
    [Extent1].[Version] AS [Version], 
    [Extent1].[FlattenedAuthors] AS [FlattenedAuthors], 
    [Extent1].[FlattenedDependencies] AS [FlattenedDependencies], 
    [Extent2].[Id] AS [Id], 
    [Extent3].[DownloadCount] AS [DownloadCount1], 
    N'packages/' + [Extent3].[Id] + N'/' + [Extent1].[Version] AS [C1], 
     CAST( CASE WHEN ([Extent1].[Listed] = 1) THEN [Extent1].[Published] ELSE NULL END AS datetime2) AS [C2], 
    N'package/ReportAbuse/' + [Extent3].[Id] + N'/' + [Extent1].[Version] AS [C3], 
    CASE WHEN ([Extent1].[Tags] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE N' ' + LTRIM(RTRIM([Extent1].[Tags])) + N' ' END AS [C4], 
    CASE WHEN ([Extent1].[Title] IS NULL) THEN [Extent3].[Id] ELSE [Extent1].[Title] END AS [C5], 
    cast(0 as float(53)) AS [C6]
    FROM [dbo].[Packages] AS [Extent1]
    INNER JOIN [dbo].[PackageRegistrations] AS [Extent2] ON [Extent1].[PackageRegistrationKey] = [Extent2].[Key]
    LEFT OUTER JOIN [dbo].[PackageRegistrations] AS [Extent3] ON [Extent1].[PackageRegistrationKey] = [Extent3].[Key]
    WHERE [Extent1].[IsPrerelease] <> cast(1 as bit)
  ) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 30
ORDER BY [Project1].[DownloadCount1] DESC, [Project1].[Id] ASC

odata_search.sql

SELECT TOP (30) 
[Project1].[PackageRegistrationKey] AS [PackageRegistrationKey], 
[Project1].[Id] AS [Id], 
[Project1].[Version] AS [Version], 
[Project1].[FlattenedAuthors] AS [FlattenedAuthors], 
[Project1].[Created] AS [Created], 
[Project1].[FlattenedDependencies] AS [FlattenedDependencies], 
[Project1].[Description] AS [Description], 
[Project1].[DownloadCount1] AS [DownloadCount], 
[Project1].[IconUrl] AS [IconUrl], 
[Project1].[IsLatestStable] AS [IsLatestStable], 
[Project1].[IsLatest] AS [IsLatest], 
[Project1].[IsPrerelease] AS [IsPrerelease], 
[Project1].[Published] AS [Published], 
[Project1].[LicenseUrl] AS [LicenseUrl], 
[Project1].[Language] AS [Language], 
[Project1].[Hash] AS [Hash], 
[Project1].[HashAlgorithm] AS [HashAlgorithm], 
[Project1].[PackageFileSize] AS [PackageFileSize], 
[Project1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], 
[Project1].[C1] AS [C1], 
[Project1].[Title] AS [Title], 
[Project1].[DownloadCount] AS [DownloadCount1]
FROM ( SELECT [Project1].[PackageRegistrationKey] AS [PackageRegistrationKey], [Project1].[Created] AS [Created], [Project1].[Description] AS [Description], 
[Project1].[DownloadCount] AS [DownloadCount], [Project1].[HashAlgorithm] AS [HashAlgorithm], [Project1].[Hash] AS [Hash], [Project1].[IconUrl] AS [IconUrl],
 [Project1].[IsLatest] AS [IsLatest], [Project1].[IsLatestStable] AS [IsLatestStable], [Project1].[LicenseUrl] AS [LicenseUrl],
  [Project1].[Language] AS [Language], [Project1].[Published] AS [Published], [Project1].[PackageFileSize] AS [PackageFileSize], 
  [Project1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], [Project1].[Title] AS [Title], [Project1].[Version] AS [Version], 
  [Project1].[IsPrerelease] AS [IsPrerelease], [Project1].[FlattenedAuthors] AS [FlattenedAuthors], [Project1].[FlattenedDependencies] AS [FlattenedDependencies], 
  [Project1].[Id] AS [Id], [Project1].[DownloadCount1] AS [DownloadCount1], [Project1].[C1] AS [C1], row_number() OVER (ORDER BY [Project1].[DownloadCount1] DESC, 
  [Project1].[Id] ASC, [Project1].[Version] ASC) AS [row_number]
        FROM ( SELECT 
                [Filter1].[PackageRegistrationKey] AS [PackageRegistrationKey], 
                [Filter1].[Created] AS [Created], 
                [Filter1].[Description] AS [Description], 
                [Filter1].[DownloadCount2] AS [DownloadCount], 
                [Filter1].[HashAlgorithm] AS [HashAlgorithm], 
                [Filter1].[Hash] AS [Hash], 
                [Filter1].[IconUrl] AS [IconUrl], 
                [Filter1].[IsLatest] AS [IsLatest], 
                [Filter1].[IsLatestStable] AS [IsLatestStable], 
                [Filter1].[LicenseUrl] AS [LicenseUrl], 
                [Filter1].[Language] AS [Language], 
                [Filter1].[Published] AS [Published], 
                [Filter1].[PackageFileSize] AS [PackageFileSize], 
                [Filter1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance], 
                [Filter1].[Title] AS [Title], 
                [Filter1].[Version] AS [Version], 
                [Filter1].[IsPrerelease] AS [IsPrerelease], 
                [Filter1].[FlattenedAuthors] AS [FlattenedAuthors], 
                [Filter1].[FlattenedDependencies] AS [FlattenedDependencies], 
                [Filter1].[Id] AS [Id], 
                [Extent3].[DownloadCount] AS [DownloadCount1], 
                CASE WHEN ([Filter1].[Listed] = 1) THEN [Filter1].[Published] ELSE null END AS [C1]
                FROM (SELECT [Extent1].[PackageRegistrationKey] AS [PackageRegistrationKey], [Extent1].[Created] AS [Created], [Extent1].[Description] AS [Description], 
        [Extent1].[DownloadCount] AS [DownloadCount2], [Extent1].[HashAlgorithm] AS [HashAlgorithm], [Extent1].[Hash] AS [Hash], [Extent1].[IconUrl] AS [IconUrl], 
        [Extent1].[IsLatest] AS [IsLatest], [Extent1].[IsLatestStable] AS [IsLatestStable], [Extent1].[LicenseUrl] AS [LicenseUrl], [Extent1].[Language] AS [Language],
         [Extent1].[Published] AS [Published], [Extent1].[PackageFileSize] AS [PackageFileSize], [Extent1].[RequiresLicenseAcceptance] AS [RequiresLicenseAcceptance],
          [Extent1].[Title] AS [Title], [Extent1].[Version] AS [Version], [Extent1].[Listed] AS [Listed], [Extent1].[IsPrerelease] AS [IsPrerelease], 
          [Extent1].[FlattenedAuthors] AS [FlattenedAuthors], [Extent1].[FlattenedDependencies] AS [FlattenedDependencies], [Extent2].[Id] AS [Id]
                        FROM [dbo].[Packages] AS [Extent1]
                        INNER JOIN [dbo].[PackageRegistrations] AS [Extent2] ON [Extent1].[PackageRegistrationKey] = [Extent2].[Key]
                        WHERE ([Extent1].[Listed] = 1) AND ([Extent1].[IsPrerelease] <> cast(1 as bit)) AND ([Extent1].[IsLatestStable] = 1) ) AS [Filter1]
                LEFT OUTER JOIN [dbo].[PackageRegistrations] AS [Extent3] ON [Filter1].[PackageRegistrationKey] = [Extent3].[Key]
                WHERE ([Filter1].[Id] LIKE '%jquery%' ESCAPE N'~') OR ([Filter1].[Id] LIKE '%ui%' ESCAPE N'~') 
        OR ([Filter1].[Title] LIKE '%jquery%' ESCAPE N'~') OR ([Filter1].[Title] LIKE '%ui%' ESCAPE N'~')
        ) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 30
ORDER BY [Project1].[DownloadCount1] DESC, [Project1].[Id] ASC, [Project1].[Version] ASC 

rewritten.sql

SELECT		TOP (30)
			Paged.PackageRegistrationKey
		,	Paged.Id
		,	Paged.Version
		,	Packages.FlattenedAuthors
		,	Packages.Copyright
		,	Packages.Created
		,	Packages.FlattenedDependencies
		,	Packages.Description
		,	PackageRegistrations.DownloadCount
		,	Packages.ExternalPackageUrl
		,	N'packages/' + PackageRegistrations.Id + N'/' + Packages.Version AS C1
		,	Packages.IconUrl
		,	Packages.IsLatestStable
		,	Packages.Language
		,	Packages.LastUpdated
		,	Packages.LicenseUrl
		,	Packages.Hash
		,	Packages.HashAlgorithm
		,	Packages.PackageFileSize
		,	Packages.ProjectUrl
		,	CASE Packages.Listed WHEN 1 THEN Packages.Published ELSE NULL END AS C2
		,	Packages.ReleaseNotes
		,	N'package/ReportAbuse/' + PackageRegistrations.Id + N'/' + Packages.Version AS C3
		,	Packages.RequiresLicenseAcceptance
		,	Packages.Summary
		,	CASE WHEN Packages.Tags IS NULL THEN CAST(NULL as varchar(1)) ELSE N' ' + LTRIM(RTRIM(Packages.Tags)) + N' ' END AS C4
		,	ISNULL(Packages.Title, PackageRegistrations.Id) AS C5
		,	Packages.DownloadCount AS DownloadCount1
		,	cast(0 as float(53)) AS C6
FROM		(
			
			SELECT		Filtered.Id
					,	Filtered.PackageRegistrationKey
					,	Filtered.Version
					,	Filtered.DownloadCount
					,	row_number() OVER (ORDER BY Filtered.DownloadCount DESC, Filtered.Id ASC) AS [row_number]
			FROM		(
						SELECT		PackageRegistrations.Id
								,	Packages.PackageRegistrationKey
								,	Packages.Version
								,	PackageRegistrations.DownloadCount
						FROM		Packages
						INNER JOIN	PackageRegistrations ON PackageRegistrations.[Key] = Packages.PackageRegistrationKey
						WHERE		Packages.IsPrerelease <> cast(1 as bit)
						) Filtered
			) Paged
INNER JOIN	PackageRegistrations ON PackageRegistrations.[Key] = Paged.PackageRegistrationKey
INNER JOIN	Packages ON Packages.PackageRegistrationKey = Paged.PackageRegistrationKey AND Packages.Version = Paged.Version
WHERE		Paged.[row_number] > 30
ORDER BY	PackageRegistrations.DownloadCount DESC
		,	Paged.Id

rewritten_search.sql

SELECT		TOP (30)
			Paged.PackageRegistrationKey
		,	Paged.Id
		,	Paged.Version
		,	Packages.FlattenedAuthors
		,	Packages.Copyright
		,	Packages.Created
		,	Packages.FlattenedDependencies
		,	Packages.Description
		,	PackageRegistrations.DownloadCount
		,	Packages.ExternalPackageUrl
		,	N'packages/' + PackageRegistrations.Id + N'/' + Packages.Version AS C1
		,	Packages.IconUrl
		,	Packages.IsLatestStable
		,	Packages.Language
		,	Packages.LastUpdated
		,	Packages.LicenseUrl
		,	Packages.Hash
		,	Packages.HashAlgorithm
		,	Packages.PackageFileSize
		,	Packages.ProjectUrl
		,	CASE Packages.Listed WHEN 1 THEN Packages.Published ELSE NULL END AS C2
		,	Packages.ReleaseNotes
		,	N'package/ReportAbuse/' + PackageRegistrations.Id + N'/' + Packages.Version AS C3
		,	Packages.RequiresLicenseAcceptance
		,	Packages.Summary
		,	CASE WHEN Packages.Tags IS NULL THEN CAST(NULL as varchar(1)) ELSE N' ' + LTRIM(RTRIM(Packages.Tags)) + N' ' END AS C4
		,	ISNULL(Packages.Title, PackageRegistrations.Id) AS C5
		,	Packages.DownloadCount AS DownloadCount1
		,	cast(0 as float(53)) AS C6
FROM		(
			
			SELECT		Filtered.Id
					,	Filtered.PackageRegistrationKey
					,	Filtered.Version
					,	Filtered.DownloadCount
					,	row_number() OVER (ORDER BY Filtered.DownloadCount DESC, Filtered.Id ASC) AS [row_number]
			FROM		(
						SELECT		PackageRegistrations.Id
								,	Packages.PackageRegistrationKey
								,	Packages.Version
								,	PackageRegistrations.DownloadCount
						FROM		Packages
						INNER JOIN	PackageRegistrations ON PackageRegistrations.[Key] = Packages.PackageRegistrationKey
						WHERE		((((Packages.IsPrerelease <> cast(1 as bit)))))
								((((AND	Packages.IsLatestStable = 1))))
								((((AND	Packages.IsLatest = 1))))
								AND	(
										PackageRegistrations.Id LIKE '%jquery%' ESCAPE N'~'
									OR	PackageRegistrations.Id LIKE '%ui%' ESCAPE N'~'
									
									OR	Packages.Title LIKE '%jquery%' ESCAPE N'~'
									OR	Packages.Title LIKE '%ui%' ESCAPE N'~'
									
									OR	Packages.Tags LIKE '%jquery%' ESCAPE N'~'
									OR	Packages.Tags LIKE '%ui%' ESCAPE N'~'
									)
						) Filtered
			) Paged
INNER JOIN	PackageRegistrations ON PackageRegistrations.[Key] = Paged.PackageRegistrationKey
INNER JOIN	Packages ON Packages.PackageRegistrationKey = Paged.PackageRegistrationKey AND Packages.Version = Paged.Version
WHERE		Paged.[row_number] > 30
ORDER BY	PackageRegistrations.DownloadCount DESC
		,	Paged.Id