'Entity framework left outer join with filtering

I'm using Entity Framework. I have two tables which are linked in my model. So, I'm using LINQ to Entities to query the data and navigation properties as the joining method in this instance.

My two table are:

  • Topic (contains a single row for every topic)
  • TopicRelationship (contains the relationships each topic has with another). A topic can have multiple relationships or no relationships.

Effectively: Topic (1) => TopicRelationship (0...many)

I'm trying to return the related topic rows that have a RelationshipTypeId of 1, but I also want to keep the full list of topics (perform a left outer join). The resulting information should look something like this:

Topic  => RelatedTopic

Topic1 => NULL
Topic2 => Topic5
Topic3 => NULL
Topic4 => Topic6
Topic5 => Topic7
...
Topic30 => Topic 20

My query to get the information looks like this:

public static IEnumerable<Topic> GetTopics()
{
    using (var uow = new UnitOfWork(Connection.CmsEntities))
    {
        var r = new Repository<Topic>(uow.Context);

        return r.Find() // Proprietary method returns IQueryable
            .Where(t =>
                t.TopicRelationships.Any(tr =>
                tr.RelationshipTypeId == TopicRelationshipType.ParentChild))
            .ToList();
    }
}

The Topic table has 30 rows, so this is what I expect to be returned. The TopicRelationship table has 8 matching rows, which is what is actually being returned.

If I change the Where statement as below, I get an error instead - "Only primitive types etc. allowed.

.Where(t =>
    t.TopicRelationships.Any(tr =>
    tr == null ||
    tr.RelationshipTypeId == TopicRelationshipType.ParentChild))

I need all 30 being returned. Help and advice appreciated.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source