CODEWITHSUNDEEP
.netsql-serverlinqentity-framework-4linq-to-entities
Diver Dan
Diver Dan

Reputation: 9963

What is the correct way to join 3 tables using EF

It might be because its a little early in the morning and the brain is not functioning correctly but I have a question regarding the entity framework and SQL

Lets say I have 2 tables

Consumer ConsumerID Name MembershipNumberID

MembershipEntry MembershipEntryID EntryDate MembershipNumberID

A member can have a MembershipNumberID before having a consumerID. Think along the lines of a person collects a membership number and uses the number for the 1st time before associates it with the consumerID.

I some how want to join the tables so I can create a query that returns all of the membership entries even if they don't yet have a consumer assoicated with them.

I have tried to add a FK to the MembershipEntry table however it fails as you would expect due missing id's in the membership table.

Do I just create a 3rd table called MembershipEntries and have 2 FK fields?

I can create a basic join using something like

from item in ctx.MembershipEntry
     join c in ctx.Consumer on item.MembershipNumberID  equals c.MembershipNumberID
     select new MembershipViewModel()
     {
     .....................
     }

How can I ceate a left outter join using the EF4 to include all entries even if they dont have a membership number yet assoicated with them?

Upvotes: 1

Views: 267

Answers (2)

Alex Aza
Alex Aza

Reputation: 78447

In LINQ to SQL this would look like this.

from item in ctx.MembershipEntry
join c in ctx.Consumer on 
    item.MembershipNumberID equals c.MembershipNumberID into customers
from c in customers.DefaultIfEmtpy()
select new MembershipViewModel()
{
    ...
}

I hope it will work for EF too.

Upvotes: 2

D.R.
D.R.

Reputation: 1219

You do not need a third table to join these two tables of data together (that's why it's a relational database!! WOOHOO!). The whole point is to make it "easy" to relate and extract the data. All you need is a simple SQL statement!

Such as this:

SELECT * 
FROM MembershipEntry 
LEFT OUTER JOIN Consumer on Consumer.MembershipNumberID = MembershipEntry.MembershipNumberID

LEFT OUTER JOIN says "Give me everything from MembershipEntry table even if there is nothing to match it in the Consumer table"

See http://www.w3schools.com/sql/sql_join_left.asp for more details and examples!

NOTE: Some databases may only use LEFT JOIN but SQL uses LEFT OUTER JOIN

Upvotes: 1

Related Questions