title: Convert SQL query/subquery to LINQ (VB)
body: <p>I'm stuck, someone give me a push please.</p>
  
  <p>Here is my SQL query..</p>
  
  <pre><code>SELECT EqID, MakeID, Model, Description,
     (SELECT MAX(EvDateEnd) AS MaxOfDateEnd
       FROM tblEvent WHERE (EqID = tblEquip.EqID) AND (Event = 'REG')) AS RegExpire,
     (SELECT Entity FROM tblEvent AS tblEvent_3 WHERE (EqID = tblEquip.EqID) AND(Event = N'EAE') AND (EvDateEnd IS NULL)) AS IssuedTo
  FROM tblEquip
  </code></pre>
  
  <p>Why I'm doing it this way, If there is a better way, I'm all ears.</p>
  
  <p>tblEquip lists rows of Equipment, EqID is the PK
  tblEvent lists many Events that relate to the equipment, EqID is the FK</p>
  
  <p>For example, I want to get the Max EvDateEnd from the Events table for each record in tblEquip, but not all records in tblEquip have the "REG" event, while others have multiple REG events.</p>
  
  <p>Some events (like EAE) I leave the tblEvent.EvDateEnd field blank to show the event is open and ongoing, such as issuing a vehicle to an employee(entity). when the vehicle is returned I enter the date in EvDateEnd essentially closing the event.</p>