Home > Software design >  Entity Framework - How to update a table related to my object
Entity Framework - How to update a table related to my object

Time:10-02

First, a little context. I'm working on a full-stack project with Angular as the front end, so there are a decent amount of intermediary classes in my project such as DTO versions of many of my classes, and a repository that is between my DataContext class and my Controller classes. Anyway, I have an AppUser class that among other properties has a field of type ProfilePhoto:

public class AppUser
    {
        public int Id { get; set; }
        public string UserName { get; set; }
        public byte[] PasswordHash { get; set; }
        public byte[] PasswordSalt { get; set; }
        public string KnownAs { get; set; }
        public ProfilePhoto ProfilePhoto { get; set; }
        public string Gender { get; set; }
        public DateTime DateOfBirth { get; set; }
        public string Country { get; set; }
        public string About { get; set; }
        public string Influences { get; set; }
        public ICollection<Goal> Goals { get; set; }
        public DateTime Created { get; set; } = DateTime.Now;
        public DateTime LastActive { get; set; } = DateTime.Now;
        public ICollection<AuthoredTab> AuthoredTabs { get; set; }
        public ICollection<FavoritedTab> FavoriteTabs { get; set; }
        public ICollection<LikedTab> LikedTabs { get; set; }
        public ICollection<PracticeRoutineDto> PracticeRoutines { get; set; }
    }

Ignore the bad password storing principles, it will be changed to use Microsoft Identity later. Here is the ProfilePhoto class for reference:

public class ProfilePhoto
    {
        public int Id { get; set; }
        public string Url { get; set; }
        public string PublicId { get; set; }
        public AppUser AppUser { get; set; }
        public int AppUserId { get; set; }
    }

It is fully defined so that Entity Framework knows how my tables are related. In my UsersController class, I have an UpdateProfilePhoto() method which accepts a file from the user that will be uploaded through the UI:

[HttpPost("add-photo")]
        public async Task<ActionResult<ProfilePhotoDto>> UpdateProfilePhoto([FromForm]IFormFile file)
        {
            var user =  await _userRepository.GetUserByUsernameAsync(User.GetUsername());

            var result = await _photoService.AddPhotoAsync(file);

            if (result.Error != null) return BadRequest(result.Error.Message);

            var photo = new ProfilePhoto
            {
                Url = result.SecureUrl.AbsoluteUri,
                PublicId = result.PublicId
            };

            user.ProfilePhoto = photo;

            if (await _userRepository.SaveAllAsync())
            {
              return _mapper.Map<ProfilePhotoDto>(user.ProfilePhoto);
            }

            return BadRequest("Problem adding photo");
        }

The photo is being correctly uploaded to the cloud storage, and If I remove some lines of code, I am able to get my PhotoDto mapped correctly and returned to my request which I tested in Postman. The issue is definitely (99.9% sure?) with getting my database to update correctly.

My errors will be near the end, but here is some more info for further context/explanation. The GetUsername() comes from an extension of the ClaimsPrincipal class as below:

public static class ClaimsPrincipalExtensions
    {
        public static string GetUsername(this ClaimsPrincipal user)
        {
            return user.FindFirst(ClaimTypes.NameIdentifier)?.Value;
        }
    }

The GetUserByUsernameAync() comes from my UserRepository class:

public async Task<AppUser> GetUserByUsernameAsync(string username)
        {
            return await _context.Users
                .Include(x => x.PracticeRoutines)
                .Include(x => x.FavoriteTabs)
                .Include(x => x.LikedTabs)
                .SingleOrDefaultAsync(x => x.UserName == username);
        }

And the SaveAllAsync() also comes from my UserRepository class:

public async Task<bool> SaveAllAsync()
        {
            return await _context.SaveChangesAsync() > 0;
        }

Now back to my [HttpPost] UpdateProfilePhoto() method. No matter how I adjust my code, I'm getting one of two errors. In the current version, I'm getting back:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.ProfilePhoto' with unique index 'IX_ProfilePhoto_AppUserId'. The duplicate key value is (6). The statement has been terminated.

Which makes it seem like it's working correctly, but obviously it can't have a second ProfilePhoto in my ProfilePhotos database table associated with the same AppUser.

I also tried to adjust my code so that the existing AppUser simply has the fields of its associated ProfilePhoto object updated (instead of trying to add/update a new ProfilePhoto object). Here is the slightly adjusted method:

[HttpPost("add-photo")]
        public async Task<ActionResult<ProfilePhotoDto>> UpdateProfilePhoto([FromForm]IFormFile file)
        {
            var user =  await _userRepository.GetUserByUsernameAsync(User.GetUsername());

            var result = await _photoService.AddPhotoAsync(file);

            if (result.Error != null) return BadRequest(result.Error.Message);

            var photo = new ProfilePhoto
            {
                Url = result.SecureUrl.AbsoluteUri,
                PublicId = result.PublicId
            };

            user.ProfilePhoto.Url = photo.Url;
            user.ProfilePhoto.PublicId = photo.PublicId;

            if (await _userRepository.SaveAllAsync())
            {
              return _mapper.Map<ProfilePhotoDto>(photo);
            }

            return BadRequest("Problem adding photo");
        }

And this version gives me this error:

System.NullReferenceException: Object reference not set to an instance of an object. at ThirtyDaysOfShred.API.Controllers.UsersController.UpdateProfilePhoto(IFormFile file) in D:\MUSIC PRODUCTION BUSINESS\30 DAYS OF SHRED\30 Days of Shred App\ThirtyDaysOfShred.API\Controllers\UsersController.cs:line 70 at lambda_method15(Closure , Object ) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at ThirtyDaysOfShred.API.Middleware.ExceptionMiddleware.InvokeAsync(HttpContext context) in D:\MUSIC PRODUCTION BUSINESS\30 DAYS OF SHRED\30 Days of Shred App\ThirtyDaysOfShred.API\Middleware\ExceptionMiddleware.cs:line 24 The program '[78712] iisexpress.exe' has exited with code 4294967295 (0xffffffff).

Where the line 70 of code mentioned is "user.ProfilePhoto.Url = photo.Url;". When I step through the method using the debugger, I can see that "var user" is of type AppUser, and I see all of the fields correctly populated based on when it got it from the database. I've tried to troubleshoot this single issue for about 8 hours since I'm a student developer, and I feel like the issue is something easy I'm not seeing due to inexperience.

Does it have to do with the fact that my method is returning a "Task" of "AppUser" technically? Or is that canceled out by the fact that I used "await" on the method? Lastly, I'm using MS SQL Server, but I highly doubt that's an issue since I would hope all Microsoft things play nicely together. This experience is making my hate Entity Framework and wishing I just did prepared SQL queries by hand on my own so that I could customize it all perfectly... Any help is extremely appreciated! Thanks!

CodePudding user response:

As Poul pointed out, my user.ProfilePhoto was null. I was able to fix everything by manually creating an instance of a new ProfilePhoto{};, and Entity was smart enough to then do an update instead of insertion into my database. Here is the altered code:

[HttpPost("add-photo")] public async Task<ActionResult> UpdateProfilePhoto([FromForm]IFormFile file) { var user = await _userRepository.GetUserByUsernameAsync(User.GetUsername());

        var result = await _photoService.AddPhotoAsync(file);

        if (result.Error != null) return BadRequest(result.Error.Message);

        var photo = new ProfilePhoto
        {
            Id = user.Id,
            Url = result.SecureUrl.AbsoluteUri,
            AppUser = user,
            PublicId = result.PublicId,
            AppUserId = user.Id
        };

        user.ProfilePhoto = photo;

        if (await _userRepository.SaveAllAsync())
        {
        return _mapper.Map<ProfilePhotoDto>(user.ProfilePhoto);
        }

        return BadRequest("Problem adding photo");
    }

I'm sure there's a way to solve this problem in the DataContext.cs somehow, but maybe this will help someone else with a similar issue. Make sure Entity can create child objects associated with your classes.

  • Related