Entity Framework Query With More Than One Join VB

Question

I'm trying to use the Entity Framework to create a complex query and get stuck in the joins. After browsing some of the documentations, I cannot seem to find the correct solution, so maybe someone here can help.

I have a data structure where a family has several family members, each of which can have more than one entry in the income table (on different registration dates) and can have more than one entry in the healthconditions table.

What I would like to obtain is the result of the following query

select [some stuff] from FamilyMember fm
left join FamilyMemberIncome fmi on fm.id=fmi.familymemberid
left join Education e on e.id=fmi.educationid
left join FamilyMemberHealthCondition fmhc on fm.id=fmhc.familymemberid
left join HealthCondition hc on fmhc.healthconditionid=hc.id

This will obviously return multiple results for a single family member, but that's ok. My attempt in code started like this (but is incorrect: "id is not a member of anonymous type")

Dim familyMembers As IQueryable = database.FamilyMember _
            .Join( _
                database.FamilyMemberIncome, _
                Function(fm) fm.id, _
                Function(fmi) fmi.familymemberid, _
                Function(fm, fmi) New With { _
                    .fmEducation = fmi.Education, _
                    .fmIncome = fmi.averagemonthlyincome, _
                    .fmProfession = fmi.profession _
                }
            ) _
            .Join( _
                database.FamilyMemberHealthCondition, _
                Function(fm) fm.id, _
                Function(fmhc) fmhc.familymemberid, _
                Function(fm, fmhc) New With { _
                    .fmHealthCondition = fmhc.HealthCondition
                }
            )

Can someone show me (or point me to an article explaining) the right way of doing this?

Did you look at the syntax for left outer join at all? Frankly, your code is a mess. — Gert Arnold
– Gert Arnold, Commented May 13, 2012 at 18:59
Thanks for the comment. And no, I didn't. I'm very new to VB.net and am learning as I go along, so I am not familiar with all language (or in this case, framework) features yet. At the same time, I currently don't have access to many experienced team members, an existing code base to learn from or quality internet (Ethiopia), so it's kind of hard to get up to speed. The link is very interesting, so thanks for that! If you can suggest more places for good tutorials, I'd welcome them. — Niels Kuiper
– Niels Kuiper, Commented May 15, 2012 at 9:31

Gert Arnold · Accepted Answer · 2012-05-15 20:34:35Z

OK, since your lonely at the top :)... Maybe you figured it out yourself in the mean time, but the syntax would be something like:

From fm in FamilyMember
Join fmi In FamilyMemberIncome On fm.id Equals fmi.familymemberid
Join e In Education On fmi.educationid Equals e.id
Join fmhc In FamilyMemberHealthCondition On fm.id Equals fmhc.familymemberid
Join hc In HealthCondition On fmhc.healthconditionid Equals hc.id
Select New With { .Name = fm.Name, .Income = fmi.Income, ...more... }

(leaving out the database. part for brevity)

This is so called comprehensive syntax. Personally I prefer fluent or chained syntax (FamilyMember.Select(...)), except with joins. Especially a number of joins in a row make it very hard to handle the result selector functions and quickly makes code messy.

The best way to master linq, has been my experience, is doing it. When linq was introduced I looked into the MSDN documentation of IEnumerable and did some exercises with each and every extension method. This has proven very useful.

Understanding the difference between Join and GroupJoin is especially useful in Linq-to-Sql or EF. The former translates to an inner join, the latter to an outer join.

Collectives™ on Stack Overflow

Entity Framework Query With More Than One Join VB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related