Store files in database using Entity Framework Core

Question

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!

score 19 · Accepted Answer · answered Feb 17 '18 at 20:03

19

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

answered Feb 17 '18 at 20:03

Roman Bartke

326
3
6

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://docs.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

score 3 · Answer 2 · answered Feb 19 '18 at 09:51

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.

farid salehi · Answer 3 · 2019-09-13T08:52:07.730

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

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.

Store files in database using Entity Framework Core

3 Answers3

Linked