title: SQL query to linq with right and left joins and nested query
body: <p>My sql query is </p>
  
  <pre><code>SELECT DISTINCT 
    tblProjects.RevID, tblProjects.CEQRNum, tblProjects.ProjectName, GEOCODE.BBL,     tblProjects.BoroID, GEOCODE.BLOCK, GEOCODE.LOT, 
                       tblMilestoneType.MilestoneName, tblMilestone.MilestoneDate
  FROM            tblMilestoneType INNER JOIN
                       tblMilestone ON tblMilestoneType.MilestoneID = tblMilestone.MilestoneTypeID RIGHT OUTER JOIN
                       tblProjects ON tblMilestone.RevID = tblProjects.RevID LEFT OUTER JOIN
                       GEOCODE ON tblProjects.RevID = GEOCODE.RevID
  WHERE        (
              tblMilestone.MilestoneDate IN (SELECT MAX(tblMilestone.MilestoneDate) AS MilestoneDate
                                            FROM tblMilestone INNER JOIN tblMilestoneType ON tblMilestone.MilestoneTypeID = tblMilestoneType.MilestoneID
                                            GROUP BY tblMilestone.RevID)
           )
  ORDER BY tblProjects.RevID
  </code></pre>
  
  <p>I am trying to convert this to linq.</p>
  
  <p>So i started with </p>
  
  <pre><code>projectList = db.tblMilestoneTypes
                                  .Join(db.tblMilestones,
                                      mileType =&gt; mileType.MilestoneID,
                                      mile =&gt; mile.MilestoneTypeID,
                                      (mileType, mile) =&gt; new
                                      {
                                          tblMilestoneType = mileType,
                                          tblMilestone = mile
                                      })
                                  .Join(db.tblProjects,                                        
                                      project =&gt; project.        // does not work)
  </code></pre>
  
  <p>Also tried  </p>
  
  <pre><code>projectList = from MilestoneTypeTable in db.tblMilestoneTypes 
                            join MilestoneTable in db.tblMilestones  on MilestoneTypeTable.MilestoneID equals MilestoneTable.MilestoneTypeID
                            rig   // no right or left join.
  </code></pre>
  
  <p>How do i do this 
  Neha</p>