具有隐藏链接表的实体SQL外连接 [英] Entity SQL outer join with hidden linking table

问题描述

可能最好只是通过示例解释：

说有3个数据库表：User，UserType和UserXUserType。
用户存储每个用户的信息，每个用户都有一个唯一的用户ID。
UserType包含所有不同的UserType，例如管理员，高级用户等。具有唯一ID：userTypeID。
最后UserXUserType将用户链接到UserTypes。 UserXUserType只有2列，userID和userTypeID。用户可以在此表中显示0次，一次或多次，这意味着他们可以没有用户类型，只有一种用户类型或多种用户类型。

在实体框架中具有此实体模型图简单模式， UserXUserType 没有实际实体，因为它只是一个没有信息的链接表。但是，每个用户都有该用户的userTypes的关系列表属性，同样对于具有该用户类型的所有用户的列表属性的UserType。

现在我真正的问题是我想要获得包含所有类型的所有用户的列表。
获取此属性的常规SQL如下：

Probably best to just explain by example:

Say there's 3 database tables: User, UserType, and UserXUserType.
User stores information on each user, each user has a unique userID.
UserType contains all the different UserTypes such as administrator, power user, etc. Has a unique ID: userTypeID.
Finally UserXUserType links Users to UserTypes. UserXUserType has only 2 columns, userID and userTypeID. A user can appear 0, once, or many times in this table, meaning they can have no user type, just one user type, or multiple user types.

In the entity framework with an entity model diagram of this simple schema, there is no actual entity for UserXUserType since it's just a linking table with no information. However each user has a relationship list property of the userTypes for that user, and likewise for UserType with a list property of all the users with that user type.

Now my real problem is that I'm trying to get a list of all users with all their types.
The regular SQL to get this would be like:

选择 *

from 用户 u

左 外部 join UserXUserType x x.userID = u.userID

left 外部 < font style ="color：rgb（128,128,128）"> join UserType t on t.userTypeID = x.userTypeID

select *

from User u

left outer join UserXUserType x on x.userID = u.userID

left outer join UserType t on t.userTypeID = x.userTypeID

推荐答案

我有类似的问题。你找到了解决办法吗？

Hi, I have a similar problem. Did you find any solution?

这篇关于具有隐藏链接表的实体SQL外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！