c# - 由于性能问题，无法使用 SQL Server 数据长度函数获取数据

Question

我的数据库中有一个文档表。文档表结构就像

CREATE TABLE [dbo].[Documents]
(
    [DocumentId] [BIGINT] NOT NULL IDENTITY(1, 1),      
    [ObjectType] [VARCHAR](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,       
    [ObjectId] [BIGINT] NOT NULL,       
    [DocumentName] [VARCHAR](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,        
    [DocumentFile] [VARBINARY](MAX) NOT NULL,       
    [isTemp] [BIT] NOT NULL CONSTRAINT [DF_Documents_isTemp] DEFAULT ((0)),     
    [IsActive] [BIT] NOT NULL,      
    [RowGuid] [UNIQUEIDENTIFIER] NOT NULL CONSTRAINT [DF_Documents_RowGuid] DEFAULT (newid()),      
    [CreatedBy] [BIGINT] NOT NULL,      
    [CreatedOn] [DATETIME] NOT NULL,        
    [UpdatedBy] [BIGINT] NULL,      
    [UpdatedOn] [DATETIME] NULL             
)       
GO      

-- Constraints and Indexes      
ALTER TABLE [dbo].[Documents] 
    ADD CONSTRAINT [PK_Documents] 
        PRIMARY KEY CLUSTERED ([DocumentId])        
GO      

CREATE NONCLUSTERED INDEX [ix_DocumentName] 
ON [dbo].[Documents] ([DocumentName])       
GO      

CREATE NONCLUSTERED INDEX [ix_ObjectId] 
ON [dbo].[Documents] ([ObjectId])       
GO      

CREATE NONCLUSTERED INDEX [ix_ObjectType] 
ON [dbo].[Documents] ([ObjectType])     
GO

在文档表中有 100k 条记录。

这是基于 objectid 获取单个文档的 C# 方法。

public IQueryable<Document> GetDocumentData(long objectId, string objectType)
{
        var searchResults = new MerrickEntities().Documents.Where(c => c.ObjectId == objectId && c.ObjectType == objectType && SqlFunctions.DataLength(c.DocumentFile) > 0);
        return searchResults.AsQueryable();
}

在此查询中未获取数据，似乎问题是由 datalength 函数引起的。

如何优化此查询？

标签： c#sql-serverperformancelinqlinq-to-sql