MVC4 Code First Entity Framework Upload large files to a SQL Server database

Question

I get the following error when I try to save a file to my SQL Server database: No mapping exists from object type System.Data.Linq.Binary to a known managed provider native type.

Here is my Database table:

CREATE TABLE [dbo].[FilesData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FileName] [nvarchar](50) NULL,
    [FileData] [varbinary](max) NULL,
    [FileExtension] [nvarchar](50) NULL,

Here is my stored procedure:

ALTER PROCEDURE [dbo].[sp_InsertFileData] 
    @FileName nvarchar(50),
    @FileData varbinary(max),
    @FileExtension nvarchar(50),
    @result nvarchar(1) OUTPUT  
AS
    SET @result = '1'
    INSERT INTO FilesData(FileName, FileData, FileExtension)
    VALUES(@FileName,convert(varbinary(Max),@FileData),@FileExtension) 

    SELECT @result

Here is my CodeFirst class:

public class DataFile
{       
    [Key]
    public virtual int Id { get; set; }
    public virtual string FileName { get; set; }       
    public virtual byte[] FileData { get; set; }        
    public virtual string FileExtension { get; set; }
}

Here is my dbContect:

public class FileStreamDbContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        Configure(modelBuilder.Entity<DataFile>());
    }
    private void Configure(EntityTypeConfiguration<DataFile> datafiles)
    {
        datafiles.ToTable("DataFiles");
        datafiles.Property(a => a.Id).HasColumnName("Id");
        datafiles.Property(a => a.FileName).HasColumnName("FileName").IsOptional();
        datafiles.Property(a => a.FileData).HasColumnName("FileData").IsOptional();
        datafiles.Property(a => a.FileExtension).HasColumnName("FileExtension").IsOptional();
    }


    public DbSet<DataFile> datafiles { get; set; }
}

here is my code:

byte[] fileData = new byte[file.InputStream.Length];
//add file input stream into byte array           
file.InputStream.Read(fileData, 0, Convert.ToInt32(file.InputStream.Length));
//var newFileData = new Binary(fileData);
//create system.data.linq object using byte array
System.Data.Linq.Binary binaryFile = new System.Data.Linq.Binary(fileData);
using (var dataContext = new FileStreamDbContext())
{
    //DataFile df = null;
    //string query = "dbo.sp_InsertFileData,@FileName,@FileData, @FileExtension";
    string fileName = Path.GetFileName(file.FileName);
    string fileExtension = Path.GetExtension(file.FileName);
    var fileNameParameter = new SqlParameter 
    { 
        SqlDbType = System.Data.SqlDbType.VarChar,
        //DbType = DbType.String,
        ParameterName = "FileName",
        Value = fileName 
    };
    var fileDataParameter = new SqlParameter 
    {
        SqlDbType = System.Data.SqlDbType.VarBinary,
        //DbType = DbType.Binary,
        ParameterName = "FileData",
        Value = binaryFile 
    };
    var fileExtensionParameter = new SqlParameter 
    {
        SqlDbType = System.Data.SqlDbType.VarChar,
        //DbType = DbType.String,
        ParameterName = "FileExtension",
        Value = fileExtension 
    };
    //var result = dataContext.Database.SqlQuery("EXEC dbo.sp_InsertFileData @FileName, @FileData, @FileExtension",
    //    fileName, newFileData, fileExtension).SingleOrDefault();
    //var results = dataContext.Database.SqlQuery<DataFile>(query, fileNameParameter, fileDataParameter, fileExtensionParameter);
    var results = dataContext.Database.ExecuteSqlCommand("exec dbo.sp_InsertFileData @FileName, @FileData, @FileExtension",
        fileName, binaryFile, fileExtension);

Any and all help is appreciated.

Why are using a stored procedure to insert data into a table that is already mapped to EF? — Maess
– Maess, Commented Dec 16, 2013 at 19:35
Do you know of a way to do this without a stored procedure? I would be happy to do this without a stored procedrue if that is possible and simpler — Brono The Vibrator
– Brono The Vibrator, Commented Dec 16, 2013 at 20:00

danludwig · Accepted Answer · 2013-12-17 14:00:58Z

1

Your problem is here:

System.Data.Linq.Binary binaryFile = new System.Data.Linq.Binary(fileData);

Just store the raw byte array in your entity property, and it should work.

byte[] fileData = new byte[file.InputStream.Length];
file.InputStream.Read(fileData, 0, Convert.ToInt32(file.InputStream.Length));
using (var dataContext = new FileStreamDbContext())
{
    var entity = new DataFile
    {
        FileName = Path.GetFileName(file.FileName),
        FileExtension = Path.GetExtension(file.FileName),
        FileData = fileData,
    };
    dataContext.Create(entity);
    dataContext.SaveChanges();
}

edited Dec 17, 2013 at 14:00

answered Dec 16, 2013 at 20:28

danludwig

47.3k27 gold badges164 silver badges240 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Brono The Vibrator Over a year ago

When I do that I get a new error: Must declare the scalar variable "@FileName". Any ideas?

danludwig Over a year ago

You need to unmap the creation of this entity from a stored procedure. Just let EF handle it without sprocs.

Brono The Vibrator Over a year ago

If I don't use the ExecuteSqlCommand method, which method would you suggest?

Brono The Vibrator Over a year ago

Now I get a new compile time error on this line: dataContext.Entry(entity).State = EntityState.Added; The error states: Cannot implicitly convert type 'System.Data.EntityState' to 'System.Data.Entity.EntityState'. An explicit conversion exists (are you missing a cast?)

Brono The Vibrator Over a year ago

I assume you mean dataContext and not dbContext. I tried dataContext.Create(entity) but Create is not a part of the dataContxt object.

|

Collectives™ on Stack Overflow

MVC4 Code First Entity Framework Upload large files to a SQL Server database

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related