title: LINQ Union vs SQL Union
body: <p>I am having relationship between <code>Users</code> and <code>UsersProjects</code>. <code>UserProjects.UserId</code> references to <code>Users.UserId</code></p>
  
  <p>I want to find User which is in <code>UserProjects</code> and <code>UserProject</code> has <code>UsreProjects.ProjectId == 4</code>. And then union the rest of Users to this table.</p>
  
  <p>In fact I want to change some propierties of User which <code>UserProject = 4</code>.</p>
  
  <p>In MSSQL, just to test I have below query</p>
  
  <pre><code>Select U.UserId 
  from Users U
  Join UserProjects UP
  On U.UserId = up.UserId
  Where up.ProjectId = 4
  Union
  Select U.UserId  From Users U
  </code></pre>
  
  <p>I am not changing any properties. Anyway as the result it shows me whole users as it should. </p>
  
  <p>But now using the same query with changing properties result more users than I ave. </p>
  
  <pre><code> var usrs = ((from users in context.Users
                               join userProj in context.UserProjects
                               on users.UserId equals userProj.UserId
                               where userProj.ProjectId == projectId
                               select new ProjectUsersDTO
                               {
                                   UserName = users.Name,
                                   Rate = users.RatePerHour,
                                   UserId = users.UserId,
                                   alreadyInProject = true
                               })
                               .Union(from users in context.Users
                                      select new ProjectUsersDTO
                                      {
                                          UserName = users.Name,
                                          Rate = users.RatePerHour,
                                          UserId = users.UserId,
                                          alreadyInProject = false
                                      }))
                                 .ToList();
                      return usrs;
  </code></pre>
  
  <p>How does it go when UNION doesn't allow duplicates?</p>
  
  <p>Thanks in adivce!</p>