C#•2w ago

✅ 2 Databases Identity.ef + my CustomDb FK INSERT issue

So i have webapp with webapi, I've made Controllers, Services etc... all looks correct I've debugged it and the data passed to models look correct to what i have in SSMS, like the userId. but somehow when i try to create new TodoList it fails with error:

An unhandled exception occurred while processing the request.
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TodoListUsers_ApplicationUser_UserId". The conflict occurred in database "TodoListDb", table "dbo.ApplicationUser", column 'Id'.
The statement has been terminated.

An unhandled exception occurred while processing the request.
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TodoListUsers_ApplicationUser_UserId". The conflict occurred in database "TodoListDb", table "dbo.ApplicationUser", column 'Id'.
The statement has been terminated.

using System.ComponentModel.DataAnnotations;

namespace TodoListApp.WebApp.ViewModels
{
    public class CreateNewTodoListViewModel
    {
        [Required]
        [StringLength(255, MinimumLength = 3)]
        public string Name { get; set; } = null!;

        [StringLength(1000)]
        public string? Description { get; set; }
    }
}

using System.ComponentModel.DataAnnotations;

namespace TodoListApp.WebApp.ViewModels
{
    public class CreateNewTodoListViewModel
    {
        [Required]
        [StringLength(255, MinimumLength = 3)]
        public string Name { get; set; } = null!;

        [StringLength(1000)]
        public string? Description { get; set; }
    }
}

13 Replies

Jimmacle•2w ago

what is the body of todoListUserService.CreateAsync?

WhiteboyOP•2w ago

        public async Task<TodoListUserDto> CreateAsync(TodoListUserDto dto)
        {
            var todoListUser = new TodoListUser
            {
                TodoListId = dto.TodoListId,
                UserId = dto.UserId,
                Role = TodoListRole.Owner
            };

            this.todoListDbContext.TodoListUsers.Add(todoListUser);
            await this.todoListDbContext.SaveChangesAsync();

            return new TodoListUserDto
            {
                TodoListId = todoListUser.TodoListId,
                UserId = todoListUser.UserId,
                UserName = todoListUser.User.UserName!,
                Role = todoListUser.Role
            };
        }

        public async Task<TodoListUserDto> CreateAsync(TodoListUserDto dto)
        {
            var todoListUser = new TodoListUser
            {
                TodoListId = dto.TodoListId,
                UserId = dto.UserId,
                Role = TodoListRole.Owner
            };

            this.todoListDbContext.TodoListUsers.Add(todoListUser);
            await this.todoListDbContext.SaveChangesAsync();

            return new TodoListUserDto
            {
                TodoListId = todoListUser.TodoListId,
                UserId = todoListUser.UserId,
                UserName = todoListUser.User.UserName!,
                Role = todoListUser.Role
            };
        }

So im checking this because in DTO i added extra field that tdoes nothing, i will remove it and see

Jimmacle•2w ago

the error is basically saying the user ID you're trying to use isn't a user in the database have you looked at the SQL being generated to confirm what EF is trying to do?

WhiteboyOP•2w ago

looks the same to me so i checked TodoLists table and looks fine but TodoListUsers table is all null So after further investigation i am even more confused why is there dbo.Applicationuser in my TodoListDb, am i not refferencing the UsersDb correctly somewhere?

Jimmacle•2w ago

have you defined a relationship between a user and a todo list item? you can't have relationships between 2 databases so both tables will be in the same database is there a reason you have 2 databases?

WhiteboyOP•2w ago

it was the requrement to use Identity.entityFramework to make register/login and then i made 2nd db for actual entities

Jimmacle•2w ago

you can use the same database for identity and your app specific tables you actually have to if you want to have relationships to identity related entities

WhiteboyOP•2w ago

Oh ye at first i was really confused to use 2 dbs so i just merge it all to one db and should be fine?

Jimmacle•2w ago

probably, i'm guessing the issue is you reference an identity user so a table is being created for them with no rows in your second db, which leads to the user id not existing where it expects it to

WhiteboyOP•2w ago

ye when i register the user data is only in UsersDb, and not in TodoListDb even tho it creates a table for user

Jimmacle•2w ago

yeah if you put everything in the same db it should work properly

WhiteboyOP•2w ago

okay thanks, will update here if all works So after fixing 91234812 (maybe more) other problems it works just fine with one db. My code for services was ok. Just had to make one DbContext

Gaming

Programming

✅ 2 Databases Identity.ef + my CustomDb FK INSERT issue

Did you find this page helpful?