CODEWITHSUNDEEP
c#sqllinqvarbinary
bmay2
bmay2

Reputation: 382

Can't insert NULL into varbinary column using LINQ

I have code that updates a record via LINQ to SQL:

dc.ClCxDemographicFileAttachments.InsertOnSubmit(new MasterDataContext.ClCxDemographicFileAttachment()
{
    ...
    FileData = characteristicDataFileAttachment.FileData,
});

The FileData column in the ClCxDemographicFileAttachment table is VARBINARY(MAX) and nullable in the SQL table and the column/property in the DataContext is System.Data.Linq.Binary.

The problem is that if I set the FileData property to null, LINQ generates an empty byte[] for that column (0x) instead of NULL. I want the latter. How do I fix this?

Upvotes: 1

Views: 1684

Answers (3)

DavidG
DavidG

Reputation: 119156

You should just use a data type of byte[] instead of System.Data.Linq.Binary otherwise some implicit conversion will take place and you will not get a null entry in your database.

Upvotes: 2

bmay2
bmay2

Reputation: 382

DavidG, not sure why you deleted your comment, but you were right. I simply needed to change the FileData column in the DataContext from System.Data.Linq.Binary to System.Byte[]. Not sure why it was the former in the first place.

Upvotes: 1

Bcpouli
Bcpouli

Reputation: 225

You can try writing an inline if that returns a DBNull.Value if the result is an empty byte array (pseudocode):

FileData = characteristicDataFileAttachment.FileData == emptyByteArray ? DBNull.Value : characteristicDataFileAttachment.FileData, ...

Alternatively you could store this in a variable if you do not like inline ifs.

Upvotes: 0

Related Questions