One to Many relationship does not retrieve all item from list that was saved-CodePudding

I have a one-to-many relationship with many ResourceTags per ResourceRequest in an MVC project.

Below is how I scaffolded the relationship with get and add methods. The add method successfully saves all the ResourceTags to the table.

However, upon retrieving the ResourceRequest as a list I only get one tag in the ICollection and it's usually a random tag i.e, not the latest one I added.

I have at least a dozen tags saved to the table for one row of ResourceRequest which I can see get updated on studio manager with the tag. However, only 1 tag gets retrieved using the GetResources() method for some reason. Any tips on why that is and how I can get it to update immediately and correctly with all the tags I added to the Resources tags collection?

public class ResourceTags
{

    [Key]
    public byte[] ResourceId { get; set; }

    public string Tag { get; set; }

    public ResourceRequest ResourceRequest { get; set; }
}



public class ResourceRequest
{
 
    [Key]
    public byte[] ID { get; set; }

    public ICollection<ResourceTags> ResourceTags { get; set; }

}



public DbSet<ResourceRequest>? Resources { get; set; }
public DbSet<ResourceTags>? ResourceTags { get; set; }

modelBuilder?.Entity<ResourceRequest>().ToTable("Resources");
modelBuilder?.Entity<ResourceTags>().ToTable("ResourceTags")
.HasOne<ResourceRequest>(rq => rq.ResourceRequest)
        .WithMany(rt => rt.ResourceTags)
        .HasForeignKey(id => id.ResourceId);


public void AddResourceTags(string id, string tagText)
{
  using var context = this.prismContext;

        byte[] convertedId = Convert.FromBase64String(id);
        var resource = context.Resources?.Find(convertedId);

        if (resource != null)
        {
            var tag = new ResourceTags { ResourceId = convertedId, Tag = tagText };
            context.ResourceTags?.Add(tag);
            context.SaveChanges();
        }

    }


  public IEnumerable<ResourceRequest> GetResources()
    {
        var context = this.prismContext;
        var result = context.Resources?.Include(t => t.ResourceTags).Take(3);
        return result ?? Enumerable.Empty<ResourceRequest>();
    }

CodePudding user response：

The issue is likely with how you have your Keys defined. I'm somewhat surprised that EF didn't raise an alarm about it.

public class ResourceTags
{

    [Key]
    public byte[] ResourceId { get; set; }

Here you say ResourceTag has a PK called ResourceId, yet...

 modelBuilder?.Entity<ResourceTags>().ToTable("ResourceTags")
    .HasOne<ResourceRequest>(rq => rq.ResourceRequest)
    .WithMany(rt => rt.ResourceTags)
    .HasForeignKey(id => id.ResourceId);

Here you are saying the ResourceTag will map to it's ResourceRequest using the ResourceId column.

This is not a One-to-Many, this becomes a One-to-One as both tables are sharing the same PK since the FK to ResourceRequest is marked as the PK of ResourceTag.

What you probably should have is:

public class ResourceTags
{

    [Key]
    public byte[] ResourceTagId { get; set; }

    public byte[] ResourceId { get; set; } // <- FK to ResourceRequest

You can manage your basic mappings via attributes to remove the need for the modelBuilder configuration for the relationship:

public class ResourceTags
{

    [Key]
    public byte[] ResourceTagId { get; set; }

    [ForeignKey(nameof(ResourceRequest))]
    public byte[] ResourceId { get; set; }

    public string Tag { get; set; }

    public ResourceRequest ResourceRequest { get; set; }
}

EF will auto-wire the Collection on the other end. (ResourceRequest.ResourceTags) Attributes can work for pretty much any general entity configuration which saves the bulk of messy configuration, where you would only have to add configuration statements for relationships that aren't clearly available via attributes.

CodePudding user response：

So I was missing a composite key declaration to begin with and also I needed to implement your solution as above and tell SQL the order of the columns.

    public class ResourceTags
{


    [ForeignKey(nameof(ResourceRequest))]
    [Key]
    [Column(Order = 0)]
    public byte[] ResourceId { get; set; }

    [Key]
    [Column(Order = 1)]
    public string Tag { get; set; }


    public ResourceRequest ResourceRequest { get; set; }
}

   modelBuilder?.Entity<ResourceTags>().ToTable("ResourceTags").HasKey(c => new { c.ResourceId, c.Tag });

        modelBuilder?.Entity<ResourceTags>().ToTable("ResourceTags")
        .HasOne(rq => rq.ResourceRequest)
        .WithMany(rt => rt.ResourceTags)
        .HasForeignKey(id => id.ResourceId);

    public IEnumerable<ResourceRequest> GetResources()
    {
        var result = this.prismContext.Resources?.Include(t => t.ResourceTags).Take(1).ToList();
        return result ?? Enumerable.Empty<ResourceRequest>();
    }