Problem with a many-to-many relation about detached-mapper HOT 9 CLOSED

Hey, many-to-many is a very complicated case... it took more than 5 years to EF team to deliver that feature.

Here some opinionated opinions:

I recommend to keep your class model simple and as close as possible to what you need in your program.
Use the new many to many and do not map foreing keys if you can avoid it, as they are pure SQL concepts that pollute your programming model, and the ORM should take care of that transparently.

Adding as many DTOs as needed is a good option too. The library should take care of the mapping. For example, GraphQL recommends to have an unique Input per call.

When adding aggregations, (i.e.: a dropdown), instead of sending an Id, I send an object containing the id, I usually call it Reference (Reference { Id }). Detached handles it and maps it to the right entity (only for [Agregations] of course).

This:

ParentDTO
   Id
   List<Reference> Documents

Can be mapped to the original entity:

Parent
   Id
   [Aggregation]
   List<Document> Documents

Detached will map Reference to Document, only the Id field, as it is the only field they have in common and the only thing it is needed to link to an existing entity.

from detached-mapper.

Hi @Jogai , thanks for the feedback...

I see that you have a many-to-many relationship between Parent and Document.
Given the attributes I defined, is ok to think that it is an [Aggregation] because linking a Document to a Parent does not modify the Document.
But in your case.. the many-to-many intermediate table was modelled as LinkedDocument.. and you actually want LinkedDocuments to be deleted or created along with Parent, so that it turns to be a [Composition].
Actually, changing it to [Composition] makes the exception disappear: Fiddle

Another option would be to configure new EF mapping to take care of the intermediate table by changing Parent.Document type from List to List and adding the corresponding mapping configuration in the OnModelCreating method:

modelBuilder.Entity<Parent>()
	.HasMany(p => p.Documents)
	.WithMany(p => p.Parents)
	.UsingEntity<LinkedDocument>(
		pd => pd.HasOne(d => d.Document).WithMany().HasForeignKey(pd => pd.DocumentId),
		pd => pd.HasOne(p => p.Parent).WithMany().HasForeignKey(pd => pd.ParentId)
	)
	.HasKey(pd => pd.Id);

then you can create document links like a regular aggregation relationship:

var p = context.Map<Parent>(new Parent
{
  Id = 1,
  Name = "MyParent",
  Documents = new List<Document> {
	  new Document { Id = 1 },
	  new Document { Id = 2 }
  }
});

Fiddle here.

Hope it helps, let me know how it goes...

from detached-mapper.

Thank you for following up so quick and showing me an improved way to build many-to-many relations. Glad I already learned something ;)

For now I left the example mostly like it was, but supplied the parent with one document and used the composition. Then I have a problem adding an extra document to it, see: https://dotnetfiddle.net/HZJvMo (instead of having 2 documents, the parent has 3 now where one is a duplicate).

Also, in my real code it throws an exception but that might be the difference between SQL local db and sqlite. SqlLite allowing the duplicate entry as shown in the fiddle makes sense because on inspection of the queries I see:

	INSERT INTO [LinkedDocument] ([Id], [DocumentId], [ParentId])
	VALUES (@p10, @p11, @p12); -- p10 is an Id that already exists in the LinkdedDocument table...
	INSERT INTO [LinkedDocument] ([DocumentId], [ParentId])
	VALUES (@p13, @p14);
	SELECT [Id]
	FROM [LinkedDocument]
	WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

Which errors out because it tries to insert a LinkedDocument with an existing Id

 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'DefaultDocument' when IDENTITY_INSERT is set to OFF.

from detached-mapper.

When deciding to add, keep or remove a LinkedDocument, the library compares by Key. And, since LinkedDocument.Id is not related in any way with Parent or Document, there are no way to ensure that no duplicates have been added.
You may need to:

• Send the correct Id of the LinkedDocument you want to update, instead of Zero (0 means new for the mapper).
  OR better,
• Remove that Id field and set ParentId + DocumentId as the primary key.

Sadly, EF doesn't let you create a composited key by using [Key] attribute, so you need to use the fluent mapping.

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
		modelBuilder.Entity<LinkedDocument>()
					.HasKey(d => new { d.DocumentId, d.ParentId });
    }

Sadliest, I didn't add any sync between EF and Detached keys, so until I do that, the workaround is to do it manually:

optionsBuilder.UseSqlite(_connection).UseDetached(options =>
{
	options.Configure<LinkedDocument>()
		.Member(l => l.ParentId).IsKey()
		.Member(l => l.DocumentId).IsKey();
});

The fiddler: here

from detached-mapper.

I meant to send one with Id 0 because that one is newly added (the link is new, the document is pre-existing). The existing one is preserved the way I expected, but the new one is added twice (which is apparently okay for sqlite).

But thanks for your explanation. I'll hope I can make this work with the more complicated cases that are coming up.

from detached-mapper.

Hopefully you dont mind taking another look, because I'm still struggling with this. I thought it might have something to do with my api setup, so I started building out that stuff, but as it turns out that was not even the problem.

I forked this project and added my experiment. Here i'm expecting to see one linkedDocument added to the database, but now it doesnt do anything anymore:
https://github.com/Jogai/Detached-Mapper/blob/2a1011d850406531b30eb9123ff1b23bd876d1ae/sample/Detached.Samples.NSwagApi/Startup.cs#L99

(By stepping trough the mapping code I hoped to solve it myself, hence the fork. I did learn a thing or two, but still couldn't solve my own problem).

from detached-mapper.

You've found a kind of bug...
There is a Convention that marks classes as "Entities" when the existing DbSet<> exists.
It seems that it stops working when you use fluent configuration.

Please add IsEntity to your fluent until I push a fix for that, and for multiple keys.

      cfg.Configure<Parent>().IsEntity()
          .Member(f => f.Documents).Composition();

from detached-mapper.

Fixed in 5.0.22

from detached-mapper.

Thank you so much! I was really pulling my hair out over this one.

from detached-mapper.