CODEWITHSUNDEEP
c#arrayssql-serverentity-frameworkvarbinarymax
doktorg
doktorg

Reputation: 197

Entity Framework returning different value for varbinary type in database

I'm a newbie to C# and EF so apologies if I get some terminology incorrect as I'm currently fixing some code from a developer who wrote our Web APIs but is not available.

In summary we have a JAVA front end that uploads PDF files and stores them in our SQL Server database as varbinary(max). When the JAVA web app downloads the PDF it calls a database function which returns the varbinary data into a byte[] variable and then the JAVA web app is able to file stream it and save as a PDF no problems.

The database value for the file data, as well as what it returned by the function that returns the varbinary(max) column as well as what is stored in the byte[] that is used in JAVA and then converted to a string, are all the same and looks like :

"0x255044462D312E360D25E2E3CFD30D0A34372030206F626A0D3C3C2F414442455F46696C6C5369676E496E666F3C3C2F56657273696F6E203130302F666F726D4..."

which from what I can tell is the format for varbinary data.

In the Web API which uses EF, the same function is modelled in the EF Model and it appears as a Complex Type, and the Filedata column is of type Binary which according to MS docs this is correct:

(https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings?redirectedfrom=MSDN)

In the C# model, the Filedata is defined as :

public byte[] FileData { get; set; }

There is a Controller that returns the data from the function as follows:

[HttpGet]
public IHttpActionResult FileAttachment_Get(string FileAttachmentID)
{
   using (TSTDBContext dbContext = new TSTDBContext())
   {
       Guid FileAttachmentGUID = new Guid(FileAttachmentID);
       var entity = dbContext.FileAttachment_Get(FileAttachmentGUID).SingleOrDefault();
       if (entity != null)
       {
            /*var str = System.Text.Encoding.Default.GetString(entity.FileData);*/
            return Ok(new { results = entity });
       }
       else
       {
           return NotFound();
       }
   }
}

The entity variable contains the Filedata value which we can then return in our Web API to be used by our mobile apps to display PDFs. The returned value is different to what is in the database and looks like: 

"JVBERi0xLjYNJeLjz9MNCjQ3IDAgb2JqDTw8L0FEQkVfRmlsbFNpZ25JbmZvPDwvVmVyc2lvbiAxMDAvZm9ybUlEKDY0OTFiNDYzLTA2MGEtNGU1Yy05NmYyLTljMThkMTZlNDlhZik+Pi9NZXRhZGF0YSA1NSAwIFIvT0NQcm9wZXJ0aWVzPDwvRDw8L09OWzU2IDAgUl0vT3JkZXJbXS9SQkdyb3Vwc1tdPj4vT0NHc1s1NiAwI..."

When we use this for the PDF file the PDF file is corrupted. There is other data that is returned by the function and the data is correct, but none of the data is of type varbinary. The varbinary data is the only field we are having issues with and is returned different to what is stored in the database.

The commented code above that converts it to a string also displays the different data.

My question is why is EF bringing back different data to what is in the database for the varbinary(max) column?

I hope I have explained enough but if you need more details please let me know.

Thanks.

Upvotes: 1

Views: 2694

Answers (2)

salli
salli

Reputation: 797

As others have mentioned that you are returning an entity and MVC controller is returning base 64 in the response. If you want to get the file you can try the following code, slightly adjusted from your sample. 

[HttpGet, Route("api/files/{FileAttachmentID}")]
    public IHttpActionResult FileAttachment_Get(string FileAttachmentID)
    {
        using (TSTDBContext dbContext = new TSTDBContext())
        {
            Guid FileAttachmentGUID = new Guid(FileAttachmentID);
            var entity = dbContext.FileAttachment_Get(FileAttachmentGUID).SingleOrDefault();
            if (entity != null)
            {
                using (var stream = new MemoryStream())
                {
                    var result = new HttpResponseMessage(HttpStatusCode.OK)
                    {
                        Content = new ByteArrayContent(entity.Content)//this is your varbinary value
                    };
                    result.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment")
                    {
                        FileName = entity.Name
                    };
                    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
                    var response = ResponseMessage(result);
                    return response;
                }
            }
            else
            {
                return NotFound();
            }
        }

Upvotes: 2

user12954110
user12954110

Reputation: 1

The MVC Controller Response is probably base64 Encoded, so you will need to use base64 Decoding in your client before creating the PDF.

Upvotes: 0

Related Questions