title: Converting an SQL Containing inner and Outer Joins into Linq
body: <p>I need to convert an SQL query to Linq/Lambda expression, I am trying doing the same but not getting the desired results.</p>
  
  <p>SQL:</p>
  
  <pre><code> SELECT b.*, n.notes 
   FROM Goal_Allocation_Branch as b 
   INNER JOIN Goal_Allocation_Product as g 
        on b.Product = g.ProductID 
       and b.Year = g.Year 
   left join Goal_Allocation_Branch_Notes as n 
        on b.branchnumber = n.branch 
       and n.year = ddlYear
   WHERE b.Year = ddlYear 
     and g.Show = 1 
     and branchnumber = ddlBranch
  </code></pre>
  
  <p>I am new to Linq , I am getting error on Join Clause , and X is not containing any data from first Join</p>
  
  <pre><code>var result = (from br in _DB_Branches.Goal_Allocation_Branches
                            join pr in _DB_Product.Goal_Allocation_Products on  new { br.Product, br.Year } equals new {Product= pr.ProductID, Year= pr.Year }
                            join n in _DB_Notes.Goal_Allocation_Branch_Notes.Where(n =&gt; n.Year == ddlYear) on br.BranchNumber equals n.Branch into Notes
                            from x in Notes.DefaultIfEmpty()
                            select new BranchNotesViewModel
                            {
                                Year = x.Year,
                                BranchNumber = x.Branch,
                                ProductID = x.ProdID
                            }
                            ).ToList();
  </code></pre>
  
  <p><strong>Update:</strong> My First Join clause initially giving error "The type of one of the expression in Join Clause is incorrect " is resolved, when I Changed On Clause
  from </p>
  
  <p>"on  new { br.Product, br.Year } equals new {pr.ProductID, pr.Year}"
  "on  new { br.Product, br.Year } equals new {Product=pr.ProductID,Year= pr.Year}"</p>
  
  <p>still not getting desired results as expected from above SQL query. Please advise..</p>