开发者

Entity Framework code first outer join for interesting schema

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.

Entity Framework code first outer join for interesting schema

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜