I have a documents table in my database. The document table structure is like

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

In the documents table there are 100k records.

This is the C# method which is fetching single document on the basis of objectid.

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();
}

In this query is not fetching data and it seems the issue is caused by datalength function.

How can I optimize this query?

0 Answers