Provide binary download links where needed using ASP.NET MVC3 Linq to SQL, maintaining Deferred Loading

I am using a VARBINARY(MAX) column to store binary files in a SQL Server table. The other columns in the same table are used to store attributes about the file, such as a description and a time stamp indicating when the file was uploaded.

I created a view in MVC3 that lists the attributes and a link which the user can use to download the binary file. Some records have a file to download and others do not.

At first the view rendered very slowly because Linq to SQL was including the binary column in the SELECT clause. I changed the Delay Loaded (Linq to SQL Deferred Loading) property for the binary column in the domain model to true to prevent this happening.

I needed a boolean to tell me which records actually have a binary so that the download link only appears when there is a file to download so I added a parameter called BinaryExists to my view model.

namespace MyProject.Models.ViewModels
{
    public class DownloadsViewModel
 开发者_JAVA百科   {
        public int DownloadsID { get; set; }
        public DateTime DateUploaded { get; set; }
        public string Description { get; set; }
        public Boolean BinaryExists { get; set; }

        public DownloadsViewModel()
        {
        }

        public DownloadsViewModel(Downloads dModel)
        {
            this.DownloadsID = dModel.DownloadsID;
            this.DateUploaded = dModel.DateUploaded;
            this.Description = dModel.Description;
            this.BinaryExists = dModel.Binary != null;
        }
    }
}

Adding this.BinaryExists = dModel.Binary != null; resulted in Linq to SQL downloading all of the binaries and slowing down the view again.

I want Linq to SQL to provide the boolean indicator without negatively affecting the view performance by downloading the binary. This can be accomplished in T-SQL using the following query.

SELECT
    DownloadsID,
    DateUploaded,
    Description,
    CASE
        WHEN Binary IS NULL THEN
            0
        ELSE
            1
        END AS BinaryExists
FROM Downloads

The CASE statement provides the boolean trigger for when a download link should be made available without actually having to download the binary first just to check whether or not it exists.

Is there a way to do the same thing in Linq to SQL?

Thank you!

---

Based on mutual feedback, here's the most straightforward solution:

Add a new computed column to the database and use the formula: (CONVERT([bit],case when [Binary] IS NULL then (0) else (1) end,(0))). Set it to "Is Persisted: Yes" for efficiency during querying. Set the datatype of the column to bit not null so that it maps to a bool in the ViewModel. Then, you can use that column to gain information about the bigger lazy loaded binary data column.