Many to many joins in Entity Framework Core 2.0

Question

I understand that EF 2.0 does not yet support many to many joins, so I have followed the helpful details in Fluent API, many-to-many in Entity Framework Core 2.0 to query my database.

I have a table of Insights, and Topics, and the relevant join tables. An insight can have multiple topics and a topic can have multiple insights:

public class Insight  
{
    public int InsightId { get; set; }     

    [Required]
    public string Heading { get; set; }

    public virtual ICollection<InsightTopicJoin> InsightTopicJoins {get; set;}

}

public class InsightTopicJoin
{
    public int InsightId { get; set; }     
    public Insight Insight {get; set;}
    public int InsightTopicId { get; set; }     
    public InsightTopic InsightTopic {get; set;}

}

public class InsightTopic  
{
    public int InsightTopicId { get; set; }     

    [Required]
    public string Name { get; set; }

    public virtual ICollection<InsightTopicJoin> InsightTopicJoins {get; set;}

}

My question is, how do I map the topic name as a subproperty to a DTO when querying for insights?

Currently when querying for insights by topic I use the following:

// InsightList is a DTO which I use to hide other properties (not shown here) when querying for data

public class InsightList
{
    public string Heading { get; set; }   
}

// This is my repository code which maps an Insight to an Insight DTO
if (TopicId == 0)
{
    return await _context.Insights
    .Select(p => new InsightList
    {
        Heading = p.Heading,
    })
    .ToListAsync();
 }
 else 
 {
     return await _context.InsightTopics
     .Where(p => p.InsightTopicId == TopicId)
         .SelectMany(p=>p.InsightTopicJoins)
         .Select(pc => pc.Insight)
         .Select(p => new InsightList
         {
             Heading = p.Heading,
         })
         .ToListAsync();
 }

I'd like to add the following property to InsightList:

public List<string> Topics { get; set;}

How do I change my query to get this data?

Update:

To be clear, currently my json result from my repository code looks like this:

{
    "heading": "Test Heading",
},

I would like it to look like this:

{
    "heading": "Test Heading",
    "topics": [
        "Topic 1",
        "Topic 2"
    ]
},

you are avoiding to do it in two linq operations (first - fetch data, second - group it)? — Roman Pokrovskij, Jan 07 '18 at 00:21
I'm happy to do it in one operation, just don't know how to! — Evonet, Jan 07 '18 at 00:28
`Topics = p.InsightTopicsJoins.Select(itj => itj.InsightTopic.Name).ToList()`. I would also rename the class `InsightTopic` to `Topic` because right now it reads like it represents the join table since it has both `Insight` and `Topic` in its name. Then you will end up with: `Insight`, `Topic` and `InsightTopic` (no join needed) or call it `InsightToTopic`. Just my 2 cents. — CodingYoshi, Jan 07 '18 at 00:35
That's great @CodingYoshi, worked perfectly, can you move this to an answer so I can accept it? And you're right about the table names! — Evonet, Jan 07 '18 at 00:39

score 1 · Accepted Answer · answered Jan 07 '18 at 00:46

You can do it like this:

.Select(p => new InsightList
{
    Heading = p.Heading,
    Topics = p.InsightTopicsJoins.Select(itj => itj.InsightTopic.Name).ToList()
}

Few Suggestions

The class InsightTopic reads like a join table (bridge table etc) right now because it has both the words Insight and Topic in the name. I would name them like following:

Insight
Topic
InsightTopic // Or InsightToTopic (no need to mention Join in the name)

I would also rename the class InsightList to InsightCollection because the .NET framework follows this convention. If you have List in the name, it leaks the inner implementation of your class; Collection on the other hand could be any implementation such as an array, list, hashset etc.

Many to many joins in Entity Framework Core 2.0

1 Answers1