Need work for case Insensitive Linq to Entity Framework query
I have three tables: Users, UserRoles, Roles.
- Usres table columns are UserId (Pk varchar (20)), CashCenterId (Int)
- the UserRoles columns are UserRoleID (pk Int), UserId (Fk table for user), RoleID (Fk table for roles)
- the Roles columns are RoleId (Pk int), RoleName (varchar (30))
I am using LINQ to ENTITY Framework to query them.
Now I want the function to check if the loggin user has the "FFFAdmin" role, so I pass the loggin user User to this function and perform the following functions.
public bool isUserFFFAdmin(string UserId){
return (from u in Db.Users
join ur in Db.UserRoles on u.UserID equals ur.UserID
join r in Db.Roles on ur.RoleID equals r.RoleID
where r.RoleName == "FFFAdmin"
&& u.UserID.Equals(UserId)
select '1').Any();
}
Now the problem is that this query is case insensitive, so if I have two users say
1.'Ram 'with the role' siteUser '2. "ram" with the role "FFFAdmin"
now when I pass "Ram" this function should return false, but returns true due to the case insensitivity of this request.
Note * I cannot change the collation type of my database to make a query based on the query. Please help, I am new to organization and LInq.
source to share
Divide the task into two parts
var users = from u in Db.Users
join ur in Db.UserRoles on u.UserID equals ur.UserID
join r in Db.Roles on ur.RoleID equals r.RoleID
where r.RoleName == "FFFAdmin"
&& u.UserID == UserId
select u.UserID;
return users.AsEnumerable().Any(s => s == UserId);
The AsEnumerable()
second part is done in a case-sensitive C # environment, while the main filtering task is still a database job.
source to share