问题描述
我有两个表用户
和 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();
这篇关于实体框架多对多查询:导航属性未生成的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!