bounav bounav - 2 months ago 14
SQL Question

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

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

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: Create a computed column on a datetime

I ran the following query 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.

Comments