LINQ联盟vs SQL联盟 [英] LINQ Union vs SQL Union

问题描述

我在 Users 和 UsersProjects 之间有关系。 UserProjects.UserId 引用 Users.UserId

I am having relationship between Users and UsersProjects. UserProjects.UserId references to Users.UserId

我想要找到 UserProjects 和 UserProject 中的用户具有 UsreProjects.ProjectId == 4 。然后将其余的用户联合到这个表中。

I want to find User which is in UserProjects and UserProject has UsreProjects.ProjectId == 4. And then union the rest of Users to this table.

其实我想改变一些用户的一些特权，其中 UserProject = 4 。

In fact I want to change some propierties of User which UserProject = 4.

在MSSQL中，只是为了测试我有以下查询

In MSSQL, just to test I have below query

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

我没有更改任何属性。无论如何，结果它向我显示整个用户，因为它应该。

I am not changing any properties. Anyway as the result it shows me whole users as it should.

但是现在使用与更改属性相同的查询会导致更多的用户超过我。

But now using the same query with changing properties result more users than I ave.

 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;

当UNION不允许重复时，怎么办？

How does it go when UNION doesn't allow duplicates?

感谢adivce！

推荐答案

LINQ联盟vs SQL联盟

LINQ Union vs SQL Union

它们是等价的。

但现在使用与更改属性相同的查询会产生比我大的用户。

But now using the same query with changing properties result more users than I ave.

这是不一样的查询。在第一个（SQL）查询中，您选择（仅包含）一个字段（ UserId ），而在第二个（LINQ）中，您包括几个字段，其中一个是可靠的的不同即可。由于 Union 使用所有包含的字段作为条件，无论项目是否唯一，则第二个查询返回更多项目是正常的。

It's not the same query. In the first (SQL) query you select (include) only one field (UserId) while in the second (LINQ) you include a couple fields, one of them being for sure different. Since the Union uses all the included fields as criteria whether the item is unique, it's normal the second query to return more items.

据说，让我们看看如何解决具体的问题。看起来你根本不需要 Union 。通常你应该有一个导航属性，所以这样一个简单的查询应该做这个工作（假设导航属性被调用 Projects ）：

With that being said, let see how to solve the concrete issue. Looks like you don't need Union at all. Normally you should have a navigation properties, so a simple query like this should do the job (assuming the navigation property is called Projects):

var query =
    from user in context.Users
    select new ProjectUsersDTO
    {
        UserName = user.Name,
        Rate = user.RatePerHour,
        UserId = user.UserId,
        alreadyInProject = user.Projects.Any(userProj => userProj.ProjectId == projectId)
    };

var result = query.ToList();

这篇关于LINQ联盟vs SQL联盟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！