So I have a schema I've developed that has a table that acts a junction table for multiple entities. This is for the purpose of tagging. I have my entities use GUIDs and then I'm able to centralize the tagging.
In this case in the diagram, I'm relating a TeamMember via TeamMemberGUID in a many to many setup via the ObjectTag junction table a 'la the ObjectGUID field. I could just as easily relate any other GUID keyed entity in the same way. Thus I have a central repo of tags t开发者_StackOverflow中文版hat can "tag" various entities. Pretty slick, or so I thought.
The trouble is I can't seem to find a way to bring this data back with LINQ to Entities. I'm using EF Code First and I want to bring this back in one query if possible. The closest I've gotten is using ugly outer join syntax:
var team = from tm in db.TeamMembers
join ot in db.ObjectTags on tm.TeamMemberGuid equals ot.ObjectGuid into objTags // outer join to ObjectTag
from ot2 in objTags.DefaultIfEmpty()
join tags in db.Tags on ot2.TagId equals tags.TagId into tmTags // join via ObjectTag to Tag
select new TeamMemberDTO
{
TeamMember = tm,
Tags = tmTags
};
But this gives me a flattened set (4 team members sharing 2 tags can equal up to 8 results) where what I'm looking for is a hierarchical TeamMember object with a Tags property of the associated tags.
I've tried normal relation properties but EF doesn't want to map more than one entity to a junction table so that's out. I've also tried a group by but haven't gotten far with that.
Is this out for LINQ to EF? Do I need to consider a view or something?
Really hoping an EF pro can help me on this one. I feel like I'm missing something or taking the wrong approach.
精彩评论