Entity Framework Code First Text Store as Image in SQL

Question

I have a legacy SQL database that has a linked table to store specifications for order lines. The application designers use an image field in SQL to store the text as binary data. I mocked up an example below:

public class OrderLine
{
    public int ID { get; set; }
    public int OrderID { get; set; }
    public int LineNo { get; set; }
    public string Product { get; set; }
    public decimal Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public string Status { get; set; }
}

public class OrderLineSpecifications
{
    public int ID { get; set; }
    public int OrderID { get; set; }
    public int OrderLineNo { get; set; }
    public Image Bits { get; set; }   // <--- This Line
    public int Bit_Length { get; set; }
}

SQL Table Definition

    [ID] [int] IDENTITY(1,1) NOT NULL,
    [OrderID] [varchar](15) NOT NULL,
    [OrderLineNo] [smallint] NOT NULL,
    [Bits] [image] NULL,
    [Bit_Length] [int] NOT NULL

Currently I have to use SQL with

cast(cast(Bits as varbinary(max)) as varchar(max))

to extract the text and then perform the reverse to return it to the database. Is it possible to have the conversion done in EF? Perhaps at the property level { get; set;} ?

score 0 · Accepted Answer · answered Aug 17 '12 at 19:43

The solution was easier than I thought. I changed what was identified as an Image in SQL to a byte array (byte[]) and created a property (Specs) that processed the BITS values. Entity Framework was happy and it works in both directions. Surprisingly easy.

public virtual byte[] BITS { get; set; }
public virtual int BITS_LENGTH { get; set; }
[NotMapped]
public virtual string Specs
{
    get
    {
        UTF8Encoding enc = new UTF8Encoding();
        string str = enc.GetString(BITS);
        return str;
    }
    set
    {
        UTF8Encoding encoding = new UTF8Encoding();
        BITS = encoding.GetBytes(value);
     }

}

Entity Framework Code First Text Store as Image in SQL

1 Answers1