How to make Linq to SQL translate to a derived column?

Question

I have a table with a 'Wav' column that is of type 'VARBINARY(max)' (storing a wav file) and would like to be able to check if there is a wav from Linq to SQL.

My first approach was to do the following in Linq:

var result = from row in dc.Table
             select new { NoWav = row.Wav != null };

The problem with the code above is it will retreive all the binary content to RAM, and this isn't good (slow and memory hungry).

Any idea how to have Linq query to translate into something like bellow in SQL?

SELECT (CASE WHEN Wav IS NULL THEN 1 ELSE 0 END) As NoWav FROM [Update]

Answer 1

Are you sure that this code will retrieve the data to RAM?

I did some testing using LINQPad and the generated SQL was optimized as you suggest:

from c in Categories
select new
{
    Description = c.Description != null
}

SELECT 
    (CASE 
        WHEN [t0].[description] IS NOT NULL THEN 1
        ELSE 0
     END) AS [Description]
FROM [Category] AS [t0]

Answer 2

What about this query:

var result = from row in dc.Table where row.Wav == null
 select row.PrimaryKey

for a list of keys where your value is null. For listing of null/not null you could do this:

var result = from row in db.Table 
             select new 
             { Key = row.Key, NoWav = (row.Wav == null ? true : false) };

That will generate SQL code similar to this:

SELECT [t0].[WavID] AS [Key], 
    (CASE 
        WHEN [t0].[Wav] IS NULL THEN 1
        ELSE 0
     END) AS [NoWav]
FROM [tblWave] AS [t0]

Answer 3

I'm not clear here, your SQL code is going to return a list of 1s and 0s from your database. Is that what you are looking for? If you have an ID for your record then you could just retrieve that single record with the a condition on the Wav field, null return would indicate no wav, i.e.

var result = from row in dc.Table
             where (row.ID == id) && (row.Wav != null)
             select new { row.Wav };

Answer 4

Thanks for all the replies. They all make sense. Indeed, Linq should translate the != null correctly, but it didn't seem to effectively do it: running my code was very slow, so somehow my only explaination is that it got the binary data transfered over to the RAM.... but maybe I'm wrong.

I think I found a work around anyway somewhere else on stackoverflow: http://stackoverflow.com/questions/441869/create-a-computed-column-on-a-datetime

I ran the following against my table: ALTER TABLE [Table]

Add WavIsNull As (CASE WHEN [Wav] Is Null Then (1) Else (0) End)

Now I'll update my DBML to reflect that computed column and see how it goes.