How can i cancel the asp.net core field plus "s" behavior when I execute complex query

[LZzccc222]

this is my table including "role","user","roleUser"



I use dbfirst method to generate my model entity,I already predict that the generated models don't include skip navigation,,for example,my "User.cs" model doesn't include the Roles Navigation but RoleUsers Navigation,I guess the reason is that I create table "roleUser" add column "id",it doesn't matter for me.

The key is that after I add Roles Navigations mannunaly,I want to get the user related role data by roles navagation directyly,the sql generated by efcore seems that always plus "s" when executed correlated query,but my database field doesn't plus 's',which results the sql execution error.it bothers me very much!!


this is my fluentApi settings about RoleUser.

thanks for resolving my problem,it's mergency!!

[ajcvickers]

Hard to say what is happening without a repro, but it could just be that you have a DbSet<> property named "RoleUsers", which is then used by convention as the table name. If this isn't it, then please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[LZzccc222]

As picture shows,I generate "user"，"role"，"roleUser"entities By my database and efcore's reverse engineering ,the entity "user" and "role"don't include mutual skiping navigation property like public virtual ICollection<Role> Roles { get; set; } = new List<RoleUser>(); and public virtual ICollection<User> Users { get; set; } = new List<User>();.
I guess that is caused by the efcore's reverse engineering behavior according to my table structure.But that doesn't matter.What I pay attention to is that I want to add skiping navigation in the '"user" and "Role" entity mannualy and keep the intermidate table navigation at the same time.

public partial class Role
{
    public int Id { get; set; }
    public virtual ICollection<RoleUser> RoleUsers { get; set; } = new List<RoleUser>();
}

public partial class RoleUser
{
    public int Id { get; set; }

    public int RoleId { get; set; }

    public int UserId { get; set; }

    public virtual Role Role { get; set; } = null!;

    public virtual User User { get; set; } = null!;
}

public partial class User
{
    public int Id { get; set; }

    public virtual ICollection<RoleUser> RoleUsers { get; set; } = new List<RoleUser>();

}

↓this is code adding skipping navigation mannualy by me.

public partial class Role
{
    public int Id { get; set; }
        public virtual ICollection<User> Users{ get; set; } = new List<User>();
    public virtual ICollection<RoleUser> RoleUsers { get; set; } = new List<RoleUser>();
}

public partial class RoleUser
{
    public int Id { get; set; }

    public int RoleId { get; set; }

    public int UserId { get; set; }

    public virtual Role Role { get; set; } = null!;

    public virtual User User { get; set; } = null!;
}

public partial class User
{
    public int Id { get; set; }
    public virtual ICollection<Role> Roles{ get; set; } = new List<Role>();
    public virtual ICollection<RoleUser> RoleUsers { get; set; } = new List<RoleUser>();

}

**the problem is that I use context in my service,when i get user's related role datas by include() method,I get error message as below describes ,the error message describes very thoroughly,it seems that the error is caused by auto-generated sql query,the query add "s" making "roleId" to be "rolesId" and "userId" to be "usersId," so how can I configure the code to cancel the sql query?

select * from role join roleUser on role.Id=roleUser.rolesId 
join user on roleUser.usersId=user.id;

↓I want auto-generated sql like below not above

select * from role join roleUser on role.Id=roleUser.roleId 
join user on roleUser.userId=user.id;

   public class UserService : IUserService
   {
       private CRMContext _context;
       public UserService(CRMContext context)
       {
           _context = context;
       }

       public User? GetUserInfo(int id)


       {
           var user = _context.Users.Include(u => u.Roles).ToList().SingleOrDefault();
           return user;
       }

   }

I've done my best to describe my problem,maybe only you can understand what happened and resolve it .Thanks for resolving my problem!!!

[LZzccc222]

!!!!!!!!!

[ajcvickers]

@LZzccc222 A couple of things. First, it's notoriously difficult to customize the ASP.NET Core Identity model. It uses a lot of quite obscure mappings. See Identity model customization in ASP.NET Core. I wrote the doc on it, and I still have to refer back to the doc if I need to do it. That being said, this may not be relevant if you're just trying to reverse engineer you own model.

Second, the documentation for mapping many-to-many relationships is here: Many-to-many. It looks like you're going to need Many-to-many with navigations and changed foreign keys.

[LZzccc222]

@ajcvickers I get what you mean.Thanks for your resolving,I have resolved this problem by referencing efcore doc.