How can I make a LINQ Select that includes a child object like a SQL outer join?

Question

I have two SQL tables in my database. Here they are represented as C# classes:

public class Topic
{
    public Topic()
    {
        this.SubTopics = new HashSet<SubTopic>();
    }

    public int TopicId { get; set; }
    public string Name { get; set; }
    public int Number { get; set; }
    public virtual ICollection<SubTopic> SubTopics { get; set; }
}

public class SubTopic
{
    public int SubTopicId { get; set; }
    public int TopicId { get; set; }
    public string Name { get; set; }
    public virtual Topic Topic { get; set; }
}

I created this code to get a simple list:

var result = db.SubTopics
            .Select(s => new
            {
                TopicId = s.TopicId,
                SubTopicId = s.SubTopicId,
                TopicName = s.Topic.Name,
                SubTopicName = s.Name
            })
            .ToListAsync();

Result here but it only works if I have a SubTopic for every Topic:

Topic1  SubTopic1  abc  def
Topic1  SubTopic2  ghi  jkl
Topic2  SubTopic3  mno  pqr
Topic3  SubTopic4  stu  vwx
Topic3  SubTopic5  xxx  yyy

I would like to get a result looking like this for the cases where I don't have a Subtopic for every Topic:

Topic1  SubTopic1  abc  def
Topic1  SubTopic2  ghi  jkl
Topic2  null       mno  null
Topic3  SubTopic4  stu  vwx
Topic3  SubTopic5  xxx  yyy

or a result like this if there were no SubTopics at all:

Topic1  null       abc  null
Topic2  null       mno  null
Topic3  null       stu  null

Can someone give me some advice how I could get this using LINQ. I assume I would need to first target like this:

var result = db.Topics

But I am not sure how I can add in the SubTopics and have it work for the case where there are sometimes no matching SubTopics.

Possible duplicate of [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — BenR, May 11 '16 at 17:31

Ivan Stoev · Accepted Answer · 2016-05-11T17:42:08.630

You have to start from Topics and use DefaultIfEmpty for SubTopics. In your case it's quite easy because you have a proper navigation properties:

var result = (from t in db.Topics
              from s in t.SubTopics.DefaultIfEmpty()
              select new
              {
                  TopicId = t.TopicId,
                  SubTopicId = (int?)s.SubTopicId,
                  TopicName = t.Name,
                  SubTopicName = s.Name
              })
            .ToListAsync();

ocuenca · Answer 2 · 2016-05-11T17:35:27.950

2

Well you can do an outer join :

var query=from t in db.Topics
          join st in db.SubTopics on t.TopicId equals st.TopicId into g
          from s in g.DefaultIfEmtpy
          select new {
                       TopicId = t.TopicId,
                       SubTopicId = s.SubTopicId,
                       TopicName = t.Name,
                       SubTopicName =  s.Name
                     };

edited May 11 '16 at 17:35

answered May 11 '16 at 17:32

ocuenca

38,548
11
89
102

1

You don't need to check "s" for null by the way. – Evk May 11 '16 at 17:33

score 0 · Answer 3 · answered May 11 '16 at 20:18

This is not an answer to your question, but perhaps it's an answer your your problem. Quite often this is really what you want:

var result=db.Topics
  .Include(t=>t.SubTopics)
  .ToListAsync();

With this, you can iterate through your topics, and the child topics like this:

foreach(var topic in result)
{
  Console.WriteLine("Topic:"+topic.Name);
  foreach(var subtopic in topic.SubTopics)
  {
    Console.WriteLine("  SubTopic:"+subtopic.Name");
  }
}

Which would give the following output:

Topic: Topic1
  SubTopic: SubTopic1
  SubTopic: SubTopic2
Topic: Topic2
Topic: Topic3
  SubTopic: SubTopic4
  SubTopic: SubTopic5

This quite useful if you are building a tree structure like for navigation/menu system, or need to do sub totals, etc.

How can I make a LINQ Select that includes a child object like a SQL outer join?

3 Answers3