How to Enable File Upload via Post in .NET Core 6 Web Api as IFormFile to Store in SQL VarBinary

I have never done anything like this before and i'm struggling to find any answers to my question (also not knowing what question to ask makes it more difficult).

I have a .NET Core 6 Web API, this is hooked up to a SQL Express DB (i'm using EF and AutoMapper).

I have a model

public partial class ConcessionDocument
{
    public long Id { get; set; }
    public long RequestId { get; set; }
    public int LineNumber { get; set; }
    public IFormFile ConcessionDoc { get; set; } = null!;

    public virtual RequestLine RequestLine { get; set; } = null!;
}

And my DB Context

        modelBuilder.Entity<ConcessionDocument>(entity =>
        {
            entity.HasKey(e => new { e.RequestId, e.LineNumber })
                .HasName("PK_RMR_CONCESSIONDOCUMENT");

            entity.ToTable("ConcessionDocument", "rmr");

            entity.Property(e => e.Id);

            entity.Property(e => e.ConcessionDoc);

            entity.HasOne(d => d.RequestLine)
                .WithOne(p => p.ConcessionDocument)
                .HasForeignKey<RequestLine>(d => new { d.RequestId, d.LineNumber })
                .HasConstraintName("FK_RMR_REQUESTLINE_CONCESSIONDOCUMENT");
        });

and my Database table

CREATE TABLE [rmr].[ConcessionDocument]
(
    [Id] BIGINT NOT NULL IDENTITY(1,1), 
    [RequestId] BIGINT NOT NULL, 
    [LineNumber] INT NOT NULL, 
    [ConcessionDoc] VARBINARY(MAX) NOT NULL

    CONSTRAINT PK_RMR_CONCESSIONDOCUMENT
        --PRIMARY KEY ([RequestId], [LineNumber]),
        PRIMARY KEY ([Id])

    CONSTRAINT FK_RMR_REQUESTLINE_CONCESSIONDOCUMENT
        FOREIGN KEY (RequestId, LineNumber)
        REFERENCES rmr.RequestLine(RequestId, LineNumber),
)

Now I am working on the POST method in my controller. I have used AutoMapper for this and everything else i've needed to do has been fine, i've understood and been able to get it working. I now need to be able to upload small files and this is where i'm struggling. My understanding is files <2mb are ok to go in the database and this is the approach I want to take, as there will not be many.

From what I have read so far, I need to convert the IFormFile ConcessionDoc property to a Memory Stream, to be able to save it in my Database as a varbinary.

   [HttpPost]
    public async Task<ActionResult<ConcessionDocumentCreateDTO>> PostConcessionDoc(ConcessionDocumentCreateDTO concessionDocDto)
    {
        var conDoc = _mapper.Map<ConcessionDocument>(concessionDocDto);

        Byte[]? bytes = null;

        if (_context.ConcessionDocument == null)
        {
            return Problem("Entity set 'RawcliffeDatastoreContext.ConcessionDocument'  is null.");
        }
        await _context.ConcessionDocument.AddAsync(conDoc);

        try
        {
            using (MemoryStream ms = new MemoryStream())
            {
                if(conDoc.ConcessionDoc != null)
                {
                    conDoc.ConcessionDoc.OpenReadStream().CopyTo(ms);
                }
                bytes = ms.ToArray();
            }

            await _context.SaveChangesAsync();
        }
        catch (DbUpdateException)
        {
            if (ConcessionDocExists(conDoc.Id))
            {
                return Conflict();
            }
            else
            {
                throw;
            }
        }
        return CreatedAtAction(nameof(GetConcessionDoc), new { id = conDoc.Id }, conDoc);
    }

The above is what I have so far - This follows the same format as my other controllers, with the addition of setting up the Memory Stream (as the bytes variable).

I am at a loss as to where to go from here.

I believe I need to somehow override what AutoMapper is doing, to map bytes to the ConcessionDoc property.

Does anybody know how I would do this? Also if this is completely incorrect, i'm going down the wrong path etc please let me know. Any and all advice is welcome.

CodePudding user response：

You're doing fine so far, you just need to make a few tweaks.

IFormFile is an interface, so you don't really want it on an EF entity, therefore since it's a varbinary, you should have it as a byte[].

I believe I need to somehow override what AutoMapper is doing, to map bytes to the ConcessionDoc property.

Yes, you can update your conDoc variable after doing the _mapper.Map.

Instead of doing bytes = ms.ToArray();, you can do conDoc.ConcessionDoc = ms.ToArray();