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

  • How to refresh a stored procedure and a table automatically every 30 mins?
  • What are the pitfalls of inserting millions of records into SQL Server from flat file?
  • How to specify the reply-to address using sp_send_dbmail in SQL Server
  • Inner Join and Group Multiple Tables into one row
  • Two column join between tables, one column with equal to and other columns with not equal to
  • Date format and validity in SQL server
  • 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?

  • Get Products By Minimum Duration Between StartDate and EndDate?
  • Search value in column which is pipe separated in sql
  • What's the SQLite 3 equivalent syntax of this T-SQL Update command
  • Duplication of data in SQL Query
  • SQL Subtract exactly a year
  • Delete Performance on Logging Table
  • 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.