Add custom field to linq to sql auto generated entity

Question

I have a table with a varbinary(max) column for an image. I have dropped the table on the LinqToSql designer and have set "Delay load" to true, since I don't want to load the actual image data. Is it possible to just know if the column is null or not, without getting the actual data and still only doing one query from the database? I would also like to use the automated entity created by Linq. Something like a new bool HasImage {get;} property would be just what I'm looking for.

Answer 1

don't know the actual answer to your Q, but in case you don't get an answer: how about doing the change yourself in the DB. (that is of course if you have control over the DB design).

and put the HasImage (or HasContent) column straight in the table, with a default "false" and when you add the image you make it "true" and than you can consult that column to see if you have an image or not.

Answer 2

Create a partial class

public partial class MyTableObject
{
    public bool HasImage { get { return MyColumn.HasValue; } }
}

this will probably trigger a database hit, though

I would suggest adding a new column to the database "HasImage" bit that you set when an image is uploaded or deleted

Answer 3

The only way for Linq to SQL to "automatically" know whether or not the column has a value is to actually ask the database for it. You can extend the partial class with fields/properties, but that's not going to eliminate the lookup.

One of the things you could do is created a computed column (assuming SQL 2005+ here, otherwise you'll have to try to adapt this to your DBMS). If your table looks like this, for example:

CREATE TABLE Foo
(
    FooID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    FooName varchar(50) NOT NULL,
    FooImage varbinary(max) NULL
)

You would add the computed column this way:

ALTER TABLE Foo
ADD FooHasImage AS CASE
    WHEN FooImage IS NULL THEN 0
    ELSE 1
END

Then you can add the FooHasImage column to your Linq to SQL entity, don't delay load it, and check that property instead of explicitly checking the FooImage property.

Also, I feel obligated to point out that storing images in a database this way is sub-optimal. It may be necessary, I don't know much about your environment, but if you're using SQL Server 2008 then consider using FILESTREAM instead, as it will use the file system for cheap "offline" BLOB storage instead of stuffing the entire thing in the database.