LINQ-to-SQL Enumerable expression with Binary = null fails

Question

This is a strange LINQ-to-SQL problem which can't evaluate as an Enumerable (in SQL) but I can evaluate client side. I think it's related to my testing Binary property as 'null'.

I need to determine when my Job is complete, which means that all Files in that Job have at least some data in their binary FileContents property. The procedure is to find all Files for a given JobId, and if any of those Files have FileContents = null then the Job is not yet complete. If all of the Files have some FileContents then the Job is complete.

The first attempt:

bool isJobComplete = !context.Files.Any
    (f => f.JobId == this.JobId && f.FileContents == null);

does not work! I receive a SQL Timeout exception. I also tried other variations on the same thing and receive the same result, e.g.

var filesInJob = context.Files.Where(f => f.JobId == this.JobId);
bool isJobComplete = !filesInJob.Any(jf => jf.FileContents == null);

The resolution is to retrieve a List of all the Files, and then check the FileContents client-side. This is far from ideal, as sometimes I will have many large files in a Job.

List<File> filesInJob = context.Files.Where(f => f.JobId == this.JobId).ToList();
bool isJobComplete = !filesInJob.Any(jf => jf.FileContents == null);

The SQL generated on the non-working expression:

SELECT
    <CASE
        WHEN EXISTS<
            SELECT NULL AS [EMPTY]
            FROM [dbo].[File] as [t0]
            WHERE <[t0].[JobId] = @p)> AND <[t0].[FileContents] IS NULL>
            > THEN 1
        ELSE 0
    END> AS [value1]

Does anyone have any experience of this, or an explanation of why LINQ can't talk null to SQL?

EDIT:

Running SELECT TOP 1 * FROM [File] WHERE [FileContents] IS NULL also gives a timeout. Is this an issue where SQL cannot search on Binary type fields? If so, is there a SQL command that will work with this scenario and how can we manipulate LINQ-to-SQL to generate such a command?

Answer 1

Smells to me like someone might have a lock. While the query is running, run an sp_who2 on your server. Is there something blocking the query?