title: Trying to get results from 3 different tables using sql or linq query
body: <p>I'm very new to SQL/LINQ and could really use your help.</p>
  <p>I have 3 tables:</p>
  <p><strong>Invitation</strong> table</p>
  <pre><code>| InviteId | Name     | Email        | SalesAgentId |   |
  |----------|----------|--------------|--------------|---|
  | 9        | John Doe | john@doe.com | 1            |   |
  </code></pre>
  <p><strong>SalesAgent</strong> table</p>
  <pre><code>| SalesAgentId | UserId | Active       |
  |--------------|--------|--------------|
  | 1            | 2      | true         |
  </code></pre>
  <p><strong>User</strong> table</p>
  <pre><code>| UserId | Name                                 | Email                             |
  |--------|--------------------------------------|-----------------------------------|
  | 2      | Sales Guy                            | sales@agent.com                   |
  </code></pre>
  <p>Trying to generate a list of a given SalesAgent that should contain that SalesAgent's info along with info of the user that has been invited by the sales agent (from the <strong>Invitation</strong> table)</p>
  <p><strong>Goal</strong>:
  Trying to Get a list of users (both from the <strong>Invitation</strong> table and <strong>User</strong> table that are tied to the SalesAgent Id (either the user is the sales agent himself, or the user was invited by the sales agent).</p>
  <p>So in this scenario, the goal is to get the result table (result) as:</p>
  <p><strong>ResultId</strong>: If there is a record in <strong>User</strong> table, then use the <strong>SalesAgentId</strong>, but if the record is coming from <strong>Invitation</strong> table, then use the <strong>InviteId</strong> for this column.</p>
  <p><strong>Name/Email</strong>: Same logic; if record is in <strong>User</strong> table, use the Name/Email from <strong>User</strong> table, if not, then use the info from <strong>Invitation</strong> table.</p>
  <p><strong>InvitedUser</strong>: If record is in <strong>User</strong> table, then this should be <strong>false</strong>, but if its coming from <strong>Invitation</strong> table, then this should be true.</p>
  <pre><code>| ResultId | Name      | Email           | InvitedUser |
  |----------|-----------|-----------------|-------------|
  | 1        | Sales Guy | sales@agent.com | false       |
  | 9        | John Doe  | john@doe.com    | true        |
  </code></pre>
  <p>I'm not sure what kind of join would help me in this scenario, more or less I need to combine results from 2 queries (I think). Ultimate goal is to get a linq query version of this select, but I think LINQ/EfCore doesn't support Concat when using different data stores.</p>
  <p>I'd really appreciate any type of help, or a pointer in the right direction; many thanks in advance.</p>