Selecting Related Entities When No Association Exists

Question

EF4 is great for pulling in associated data but what do you do when the association is not explicit? An example illustrates my situation:

MasterTable has a child1Id and child2Id column.

There are two tables Child1 and Child2 with corresponding primary key child1Id and child2Id. There are Master, Child1 and Child2 entities.

There is no foreign key or entity framework association between Master and Child1 / Child2 tables or entities.

How can I select the master records and corresponding child records from the two child tables when all I have are the matching child Ids in the master?

I can't retrofit a relationship or association.

Richard

score 0 · Accepted Answer · edited May 23 '17 at 10:33

0

You must select them manually by linq to entities. Here is how to do left join between two tables:

var query = from m in context.Masters
            where m.Id == 1
            join c in context.Childs on m.Child.Id equals c.Id into leftJoin
            from x in leftJoin.DefaultIfEmpty()
            select new
                {
                    Id = x.Id,
                    Name = x.Name,
                    Child = x.Childs
                };

Btw. if your entities have a property which contains a value of PK from other entity you can create relation in EF designer. In such case you will be able to use navigation properties.

edited May 23 '17 at 10:33

Community

1
1

answered Apr 12 '11 at 19:17

Ladislav Mrnka

360,892
59
660
670

I have a similar situation.. Parent & Child share no explicit association in EF/DB. So I added a Child property in a Parent.partial.cs class file. Is there a way to use the query selector - but 'select m' so EF materializes master objects - and then somehow populate the .Child property all in-line, such that EF will actually generate a join that returns the children AND materializes them simultaneously? (essentially, association on the fly) – JoeBrockhaus May 01 '13 at 21:50
.. currently I have to do something like select new { Parent = parents, Children = children } .. but that means I have to spend more cycles stuffing that into Parent: var results = q.Select(x => { var p = x.Parent; p.Children = x.Children; return p; }); (I have this in a S/O question http://j.mp/10tivJy) – JoeBrockhaus May 01 '13 at 21:51

Selecting Related Entities When No Association Exists

1 Answers1