title: Linq Select with Max/Min, Left Join
body: <p>I am trying to convert my program from ADO to Entity Framework and Linq. Can someone show me how to write this using Linq?</p>
  
  <pre><code>SELECT a.[Category], [WorkOrderVersion], min(a.[CategoryStartDate]) AS CategoryStartDate, 
         max(a.[CategoryEndDate]) AS CategoryEndDate, c.[HexColor] AS CustomBackgroundColor
  FROM [Job].[EstimateTbl] as a
  Left JOIN [Employee].[EmployeeColorPrefsTbl] c 
       ON c.[Item] = a.[Category] AND c.[EmployeeID] = @employeeID
  Where [ID_Job] = @ID_Job 
  group by [Category], [WorkOrderVersion], c.[HexColor] 
  ORDER BY [Category]
  </code></pre>
  
  <p>I am close, but I'm not getting the grouping and Max/Min part...</p>
  
  <pre><code>var query = from a in db.EstimateTbls
              where a.ID_Job == objActiveJobClass.ID
              join c in db.EmployeeColorPrefsTbls on a.Category equals c.Item into ps
              from c in ps.DefaultIfEmpty()
              orderby a.Category ascending
              select new
              {
               a.Category,
               a.WorkOrderDate,
               CategoryStartDate = a.CategoryStartDate,
               CategoryEndDate = a.CategoryEndDate,
               c.HexColor,
              };
  </code></pre>