Need work around for case Insensitive Linq to Entity Framework query

Question

I have three tables Users, UserRoles, Roles.

columns of Usres Table are UserId(Pk varchar(20)), CashCenterId(Int)
columns of UserRoles are UserRoleID(pk Int), UserId(Fk to user table), RoleID(Fk to role Table)
columns of Roles are RoleId(Pk int), RoleName(varchar(30))

I am using LINQ to ENTITY Framework to query them.

Now I want a function to check if loggin user has 'FFFAdmin' role so I pass loggin user's UserId to this function and do the following in the function.

     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 this query is case insensitive so if I have two user say

1.'Ram' with role 'siteUser' 2. 'ram' with role 'FFFAdmin'

now when I pass 'Ram' this function should return false but It returns true because of case insensitive behavior of this query.

Note* I cannot change the collation type of my database to make query case sensitve. Please help I am new to entity and LInq.

score 1 · Accepted Answer · answered Feb 14 '13 at 20:41

Split the task in 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);

By AsEnumerable() the second part is done in the case sensitive C# environment, while the main filtering task is still a database job.

Thanks a lot @Gert Arnold It worked (I think the best solution will be to changes the collation type of my column) — Yagnesh.Dixit, Feb 15 '13 at 08:45

Need work around for case Insensitive Linq to Entity Framework query

1 Answers1