CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
Matthew
Matthew

Reputation: 9949

Merging Collections of Many to Many linked Entity

I have a many to many relationship between two objects:

[Table("AEntities")]
public abstract class AEntity { 
 public int AEntityID { get; set; } 
 public string Description { get; set; }
 public virtual ICollection<Tag> Tags { get; set; }
}

And Tag:

public class Tag {
 public int TagID { get; set; }
 public int AEntityID { get; set; }
 public string TagValue { get; set; }
}

Mapping Table:

public class AEntityTags {
    public int AEntityTagID { get; set; }
    public int TagID { get; set; }
    public int AEntityID { get; set; }
}

Fluent Code to define the mapping:

        modelBuilder.Entity<AEntity>() 
            .HasMany(t => t.Tags)
            .WithMany(p=>p.AEntity) 
            .Map(t => t.MapLeftKey("AEntityID") 
                .MapRightKey("TagID") 
                .ToTable("AEntityTags"));

Next I am trying to get a list of distinct tags for a given set of AEntities. So if I have three AEntity objects in the last I want a list of all the tags on any of these three Entities.

I can currently can accomplish this with the following query:

    public IEnumerable<Tag> getTagsOnAEntities(IEnumerable<AEntities> aEntities) {
        IEnumerable<Tag> results = _context.Tags
            .AsEnumerable()
            .Where(p => p.AEntities.Any(o=>aEntities.Contains(o)));
        return results;
    }

When these are used we are forcing an additional lookup to pull the count of times used on projects (i.e. I am printing the t.TagValue and t.AEntities.Count() when used).

But as might be expected is incredibly slow (~10 seconds) as the number of AEntities (several thousand) and Tags(10's of thousand mappings) grows. I understand where the inefficiency is happening (thousands of DB calls), and am looking for advice on approach as I am having a difficult time determining what the best approach should be. I am having the following difficulties:

  1. If I am after overall counts of times a tag is used in the DB I have attempted separately querying the AEntityTags table for a count of the TagID (faster as it skips loading anything, but still slow) - is there a better way than this staying within EF?

  2. Is there an efficient way to determine the number of times a Tag is used in some subset of AEntities?

Upvotes: 0

Views: 95

Answers (1)

Manos Dilaverakis
Manos Dilaverakis

Reputation: 5869

I don't have something similar to your database handy to test but you might want to try this for performance over your query: 

IEnumerable<Tag> results = aEntities.SelectMany(e=>e.Tags).Distinct();

Upvotes: 1

Related Questions