17

How to store files in a SQL Server database using Entity Framework Core (Code-First) in an ASP.NET Core app?

I have tried using Filestream but unfortunately I get this error:

Cannot call Property for the property 'Avatar' on entity type 'UserProfile' because it is configured as a navigation property. Property can only be used to configure scalar properties

Here's the code:

public class UserProfile : BaseEntity {
    public FileStream Avatar { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
    public Sex Sex { get; set; }
    public string Address { get; set; }
    public string PhoneNumber { get; set; }

    public virtual IEnumerable<Email> Emails { get; set; }
    public virtual User User { get; set; }
    public int UserID { get; set; }
}

And mapping:

public class UserProfileMap {
    public UserProfileMap(EntityTypeBuilder<UserProfile> entityBuilder) {
        entityBuilder.HasKey(e => e.ID);
        entityBuilder.Property(e => e.Avatar);
        entityBuilder.Property(e => e.FirstName);
        entityBuilder.Property(e => e.LastName);
        entityBuilder.Property(e => e.DateOfBirth);
        entityBuilder.Property(e => e.Sex);
        entityBuilder.Property(e => e.Address);
        entityBuilder.Property(e => e.PhoneNumber);
        entityBuilder.HasMany(e => e.Emails).WithOne(u => u.UserProfile).HasForeignKey(x => x.UserProfileID);
    }
}

What do I do? Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
stroibot
  • 808
  • 1
  • 11
  • 25

4 Answers4

20

You can convert the file bytes to a byte array.

public byte[] Avatar { get; set; }

Examine the accepted answer in the analogous approach for EF6: Save and retrieve image (binary) from SQL Server using Entity Framework 6

Roman Bartke
  • 336
  • 3
  • 6
  • 1
    But is there a way to store them using Filestream? – stroibot Feb 18 '18 at 07:10
  • 1
    The entity classes are simple POCO classes (plain old CLR objects). Supported data types are the elementary .NET data types (String, DateTime, Boolean, Byte, Byte[], Int16, Int32, Int64, Single, Double, Decimal and System.Guid). By convention, the database provider selects a data type based on the CLR type of the property. It also takes into account other metadata, such as the configured Maximum Length, whether the property is part of a primary key, etc. (cf. https://learn.microsoft.com/en-us/ef/core/modeling/relational/data-types). IMO we cannot use FileStream as property type. – Roman Bartke Feb 18 '18 at 11:55
3

I am assuming that you are trying to use the windows filestream for sql server, which is not yet supported by .NET Core. You have to store the file as a byte array as already said (which will convert to varbinary(max) in sql server) and copy the file content over when uploading using a memory-stream for instance.

alsami
  • 8,996
  • 3
  • 25
  • 36
2

By Ef core, you can't store file in your database until now, so you can:

  1. store the result of reading files as byte[] like this :
public byte[] Avatar { get; set; }

var avatar =  File.ReadAllBytes(filePath);

2.use your machine as a file server and store the path of your file in database :

public string Avatar { get; set; }

In my Opinion the second way is better and i always use this pattern but it depends on your machine's H.D.D and the amount of files you want to store.

farid salehi
  • 79
  • 7
0

There is a much better way of storing files with EntityFrameworkCore, this library make it much easier https://www.nuget.org/packages/Files.EntityFrameworkCore.Extensions

Its stores files in small chunks so you don't have to worry about using too much memory during file upload or downloading.

Examples are also available on Github repopsitory

public class UserImage : IFileEntity
{
    public Guid Id { get; set; }
    public Guid FileId { get; set; }
    public string Name { get; set; }
    public string MimeType { get; set; }
    public DateTimeOffset TimeStamp { get; set; }
    public Guid? NextId { get; set; }
    public int ChunkBytesLength { get; set; }
    public long TotalBytesLength { get; set; }
    public byte[] Data { get; set; }
}

[HttpPost]
public async Task<ActionResult<FilesExtensionsResponse>> UploadFile([FromForm] IFormFile file)
{
     if (file.Length > 0)
     {
         var fileDetails = await _context.SaveFileAsync<UserImage>(file.OpenReadStream(), file.FileName, file.ContentType);
         return Ok(fileDetails);
     }
     else
     {
         return BadRequest("File is required.");
     }
}
[HttpGet("{id}/download")]
public async Task<IActionResult> DownLoadFile(Guid id)
{
    var fileDetails = await _context.GetFileInfoAsync<UserImage>(id);
    var stream = new MemoryStream();
    await _context.DownloadFileToStreamAsync<UserImage>(id, stream);
    return File(stream, fileDetails.MimeType, fileDetails.Name);
}
// And more methods are available
sitholewb
  • 309
  • 2
  • 6