实体框架多对多查询：导航属性未生成 [英] Entity Framework many-to-many query: navigation property not generated

问题描述

我有两个表用户和 UserRole 使用链接表 UserInRole

I have two tables User and UserRole which are they connected using a link table UserInRole

当我生成实体模型时，由于某种原因，实体 UserInRole 没有生成。从图中可以看出，实体框架了解到， User 和 UserRole ：

When I generate the entity model, for some reason, the entity UserInRole not getting generated. And as you can see from the picture, Entity Framework understood that there is many-to-many relationship between User and UserRole:

我需要实现这样的查询

select ur.Name from [User] as u
inner join UserInRole uir on uir.UserId = u.Id
inner join UserRole ur on ur.Id = uir.UserRoleId
where u.Username = 'magename'

我正在使用通用存储库，如果导航属性将存在，​​查询将如下所示：

I am using generic repository, if navigation property would exists the query would be looking like this:

from u in repository.AsQueryable<User>()
join uir in repository.AsQueryable<UserInRole>() on u.Id equals uir.UserId
join ur in repository.AsQueryable<UserRole>() on uir.UserId equals ur.Id
where u.Username == userName
select ur.Name

但Enti ty框架不会生成 UserInRoles 导航属性和相关的 UserInRole 实体，所以问题该怎么办？应该删除 UserInRole 和 UserRole 之间的链接，以获取实体 UserInRole 在模型中生成，或者有任何方式可以在上面描述的查询而没有数据库中的任何更改？

But Entity Framework does not generate UserInRoles navigation property and related UserInRole entity, so the question what should I do in that situation? Should I remove link between UserInRole and UserRole to get entity UserInRole generated in model or there any way to have the query I described above without any changes in database?

更新

所以我需要做这样的事情

so looks i need to do something like that

stirng[] roles = (from u in repository.AsQueryable<User>()
         where u.Username == userName
         select ur.UserRoles.Select(x => x.Name)).ToArray<string>();

得到错误不能隐式转换类型'System.Collections.Generic.List< ; System.Collections.Generic.IEnumerable< string>>'to'string []'任何想法？

推荐答案

您的EF模型中不需要链接表这是EF的美丽！

检查您的实体用户 - 它有一个 UserRoles 导航属性 - 这是该用户所在角色的集合。

Check your entity User - it has a UserRoles navigation property - that's the collection of all roles this user is in.

查看实体 UserRole ：它具有导航属性用户，其所有用户都处于此角色。

Check out entity UserRole: it has a navigation property Users which has all users that are in this role.

使用这些两个多值导航属性，您可以表达您可能需要的任何查询：

With these two multi-valued navigation properties, you can express any of the queries you might want:

• 查找给定角色的所有用户（找到 UserRole 实体并枚举其 .Users 属性）


• 查找所有角色给定的用户（找到 User 并列举其 .UserRoles ）

• find all users for a given role (find the UserRole entity and enumerate its .Users property)
• find all roles for a given user (find the User and enumerate its .UserRoles)

EF和EDM类似的隐藏您的链接表 - 当您在概念模型中表达您的意图时，您并不真的需要它;这些链接表仅仅是关系数据库中的必要的邪恶，因为这些数据库不能以任何其他方式建立m：n关系。

EF and the EDM sort of "hide" that link table from you - you don't really need it when you're expressing your intent in the conceptual model; those link tables are just a "necessary evil" in relational databases since those databases cannot model m:n relationships in any other way.

更新：，所以你似乎想找到一个特定用户所在的所有用户角色（他们的名字）的列表 - 你可以这样表达：

Update: so you seem to want to find a list of all the user roles (their names) that a particular user is in - you can express this something like this:

// find the user in your "dbContext" (or via your repository)
var user = _dbContext.Users.FirstOrDefault(u => u.Name == "magename");

// from the list of UserRoles on that user, select the names only
var userRolesNames = user.UserRoles.Select(ur => ur.Name).ToList();

这篇关于实体框架多对多查询：导航属性未生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！