Stored procedure MVC3 Entity Framework - Varbinary

Question

I have am Emp table that looks like this:

Employee .

I have created a stored procedure which looks like this

ALTER PROCEDURE [dbo].[GetAllEmployees]
AS
BEGIN
OPEN SYMMETRIC KEY TestTableKey DECRYPTION
BY CERTIFICATE EncryptTestCert
SELECT  
        EMPId,
        Firstname,
        Lastname,
        AddessId,
        JobId,
        DateofBirth,
        CONVERT(NVARCHAR(50),DECRYPTBYKEY(EncryptFirstname)) AS [EncryptFirstname],
        CONVERT(NVARCHAR(50),DECRYPTBYKEY(EncryptLastname)) AS [EncryptLastname]
FROM 
        EMPloyee
END

The reason I have created a stored procedure is to convert some of the varbinary columns in the table to strings and use it with EF in MVC3.

The way I have mapped the procedure in the model is as below.

FunctionImport1 into Model(EF)

FunctionImport2 into Model(EF)

In my EmployeeViewModel this is the way I am mapped the properties to fields

[Description("EmployeeDetails")]
public class EmployeeViewModel: IEmployeeModel
{
   public Guid EmpId { get; set; }
   [Display(Name = "EncryptLastName")]
   public byte[] EncryptLastName { get; set; }

   [Display(Name = "EncryptFirstName")]
   public byte[] EncryptFirstName { get; set; }

   [Display(Name = "LastName")]
   public string LastName { get; set; }
}

In access the stored procedure in my service method, this is the way I am accessing it.

public List<EmployeeViewModel> GetEmpList()
{
   var ent = new EncryptionEntities();
   List<Employee> allEmp = new List<Employee>();
   allEmp = ent.GetEmployees().ToList();
   ConvertViewModelObject cvmo = new ConvertViewModelObject();
   List<EmployeeViewModel> empVM = new List<EmployeeViewModel>();

   foreach (var item in allEmp)
   {
      empVM.Add(cvmo.ConvertFromEmployee(item));
   }

   return empVM.ToList();
}

ERROR in this method:

The 'EncryptFirstname' property on 'Employee' could not be set to a 'String' value. You must set this property to a non-null value of type 'Byte[]'.

In my stored procedure if I am simply display the EncryptFirstname as it islike, with out converting to string, no error occurs, but the value I will be getting is system.byte[].

But it needs to be in string format so that I can understand what value it is of?

Please advise, what should I do to display it correctly.

This is the way I am converting the entity to entityViewmodel.

public EmployeeViewModel ConvertFromEmployee(Employee emp)
{
   if (emp == null)
      return null;

   var evm = new EmployeeViewModel();

   evm.LastName = emp.Lastname;
   evm.EncryptFirstName = emp.EncryptLastName;

   return evm;
}

This is EXACTLY the same problem I am having as well. I know this is an older post but did you ever find a solution to this? I have been trying to figure this out for a good part of the day. — TheAmazingJason

TheAmazingJason TheAmazingJason · Accepted Answer · 2013-02-14T23:32:26

I found this article to be very helpful and I found the answer in the section titled "Importing Stored Procedures that Return Types Other than Entities". So the solution is in your last screen shot above where you are in the dialog box "Add Function Import" instead of selecting the Entities radio button, first click on the "Get Column Information", then click on the "Create New Complex Type" button. Now you can go back up and select the "Complex" radio button and select this new type from the drop down. Now you can just set your view to use this model and you will be all set.

Stored procedure MVC3 Entity Framework - Varbinary

1 Answers