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

  • .NET, the SqlConnection object, and multi-threading
  • TSQL Union in sub query
  • Sql server rtrim not working for me, suggestions?
  • Query runs fast in Query Analyzer but slow in C# application
  • How to install fulltext on sql server 2008?
  • Select with Many Discrete Values Possible in Where Clause
  • Consumer


    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?

  • How can I check if a SQL Server constraint exists?
  • How do I subtract inside of an IN clause in SQL?
  • Generate database creation scripts
  • TSQL creating a dynamic report from two tables, one table is holds the headers, other one, data
  • GROUP BY to combine/concat a column
  • Unexpected #temp table performance
  • 2 Solutions collect form web for “What is the correct way to join 3 tables using EF”

    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

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.