LINQ Union vs SQL Union
<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>