I have following entities -
BaseEntity for keys:
/// <summary>
/// Base entity
/// </summary>
[Index("Uid", IsUnique = true)]
public abstract class BaseEntity
{
[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public virtual int Id { get; set; }
[Required]
public virtual Guid Uid { get; set; } = Guid.NewGuid();
}
Post:
public class Post : BaseEntity
{
[MaxLength(100), MinLength(5)]
public required string Title { get; set; }
[MaxLength(3000), MinLength(50)]
public required string Text { get; set; }
public required DateTime Date { get; set; } = DateTime.Now;
public required bool IsAnonymous { get; set; } = false;
public Guid UserId { get; set; }
public virtual User User { get; set; }
public PostCount PostCount { get; set; } = null!;
public virtual ICollection<Comment>? Comments { get; set; }
public virtual ICollection<Topic> Topics { get; set; }
public virtual ICollection<Like> Likes { get; set; }
}
Topic:
public class Topic : BaseEntity
{
public string Type { get; set; } = string.Empty;
public virtual ICollection<User> Users { get; set; }
public virtual ICollection<Post> Posts { get; set; }
}
Their configuration:
public static void ConfigureTopics(this ModelBuilder modelBuilder)
{
modelBuilder.Entity<Topic>().ToTable("Topics");
modelBuilder.Entity<Topic>().Property(x => x.Type).IsRequired();
modelBuilder.Entity<Topic>().HasMany(x => x.Posts)
.WithMany(x => x.Topics)
.UsingEntity(t => t.ToTable("PostsTopics"));
}
Here is the method to create a post that works fine:
public async Task<PostModel> Create(CreatePostModel model, Guid userId)
{
try
{
await _createValidator.CheckAsync(model);
await using var context = await _dbContextFactory.CreateDbContextAsync();
// Create a new Post entity
var post = _mapper.Map<Post>(model);
if (post.Date.Kind == DateTimeKind.Local)
post.Date = post.Date.ToUniversalTime();
// Create a new PostCount entity
var postCount = new PostCount
{
Post = post
};
// Associate the Post and PostCount entities
post.PostCount = postCount;
post.UserId = userId;
// Fetch existing topics by Uid
post.Topics = await context.Topics
.Where(t => model.Topics.Contains(t.Uid))
.ToListAsync();
context.AttachRange(post.Topics);
// Add the entities to the context
await context.Posts.AddAsync(post);
await context.PostCounts.AddAsync(postCount);
// Save changes to the database
await context.SaveChangesAsync();
post.User = await context.Users.FirstAsync(u => u.Id == userId);
var createdPost = _mapper.Map<PostModel>(post);
return createdPost;
}
catch (Exception e)
{
_logger.Error($"Error creating post. Error message: {e.Message}");
throw;
}
}
PostsTopics table in the database:
-- auto-generated definition
create table "PostsTopics"
(
"PostsId" integer not null
constraint "FK_PostsTopics_Posts_PostsId"
references "Posts"
on delete cascade,
"TopicsId" integer not null
constraint "FK_PostsTopics_Topics_TopicsId"
references "Topics"
on delete cascade,
constraint "PK_PostsTopics"
primary key ("PostsId", "TopicsId")
);
alter table "PostsTopics"
owner to postgres;
create index "IX_PostsTopics_TopicsId"
on "PostsTopics" ("TopicsId");
Here is the update method. The problem here is EF Core for some reason is not removing topics from PostTopics table, and that is why I am getting an error
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
welisten_api-1 | ---> Npgsql.PostgresException (0x80004005): 23505: duplicate key value violates unique constraint "PK_PostsTopics"
When I try to pass empty list of topics, it updates every other property, and saves them, but the topics remain as before:
public async Task Update(Guid id, Guid userId, UpdatePostModel model)
{
try
{
await _updateValidator.CheckAsync(model);
await using var context = await _dbContextFactory.CreateDbContextAsync();
var post = await context.Posts
.Include(x => x.User)
.Include(x => x.PostCount)
.Include(x => x.Topics)
.FirstOrDefaultAsync(x => x.Uid == id);
if (post == null)
throw new ProcessException($"Post with ID: {id} not found");
if (post.UserId != userId)
throw new AuthenticationException("Authentication failed");
context.Entry(post).State = EntityState.Modified;
// Clear existing topics
post.Topics.Clear();
// Update other properties of the post
post.Title = model.Title;
post.Text = model.Text;
post.IsAnonymous = model.IsAnonymous;
// Update topics
post.Topics = await context.Topics
.Where(t => model.Topics.Contains(t.Uid))
.ToListAsync();
context.AttachRange(post.Topics);
// Save changes again to update the many-to-many relationship
await context.SaveChangesAsync();
}
catch (Exception e)
{
_logger.Error($"Error updating post with ID: {id}. Error message: {e.Message}");
throw;
}
}
I tried to first clear the topics with Post.Topics.Clear(), save them. Then assign new topics and save the changes once more, but it did not help either
So, after trial and error I made it working. The problem was in attaching post.Topics context.AttachRange(post.Topics); before changing them. However, the EF started complaining that it cannot track two entities at the same time, when I tried to update the values in post.Topics with
// Update topics
post.Topics = await context.Topics
.Where(t => model.Topics.Contains(t.Uid))
.ToListAsync()
I had to manually check which topics should be removed and whic ones to be added.
Here is final working solution for my problem:
public async Task Update(Guid id, Guid userId, UpdatePostModel model)
{
try
{
await _updateValidator.CheckAsync(model);
await using var context = await _dbContextFactory.CreateDbContextAsync();
var post = await context.Posts
.Include(x => x.User)
.Include(x => x.PostCount)
.Include(x => x.Topics)
.FirstOrDefaultAsync(x => x.Uid == id);
if (post == null)
throw new ProcessException($"Post with ID: {id} not found");
if (post.UserId != userId)
throw new AuthenticationException("Authentication failed");
context.Entry(post).State = EntityState.Modified;
context.AttachRange(post.Topics);
// Update other properties of the post
post.Title = model.Title;
post.Text = model.Text;
post.IsAnonymous = model.IsAnonymous;
// Update topics
var existingTopicIds = post.Topics.Select(t => t.Uid).ToList();
var newTopicIds = model.Topics;
// Remove topics that are not in the new list
foreach (var topic in post.Topics.ToList())
{
if (!newTopicIds.Contains(topic.Uid))
{
post.Topics.Remove(topic);
}
}
// Add new topics
foreach (var topicId in newTopicIds)
{
if (!existingTopicIds.Contains(topicId))
{
var topic = await context.Topics.FirstOrDefaultAsync(t => t.Uid == topicId);
if (topic != null)
{
post.Topics.Add(topic);
}
}
}
// Save changes to update the post entity
await context.SaveChangesAsync();
}
catch (Exception e)
{
_logger.Error($"Error updating post with ID: {id}. Error message: {e.Message}");
throw;
}
}