score:7
I've not done this before but my guess is that you should do it like this:
//Get the roles
var roles = (from q in context.Roles
where context.UserHasRole.Any(o => o.UserId == userId && o.RoleId == q.Id)
select q).ToList();
return roles;
score:2
Try:
var roles = (from q in context.Roles
context.UserHasRole.Any(o =>
o.UserId == userId
&& o.RoleId == q.Id)
select q).ToList();
score:2
You're asking for the roles where "there is a relation with a specific user in the UserHasRole table" and "there is a relation with the role in the UserHasRole table". The first condition will have the same result for every role, because it does not depend on the roles.
You want the roles where "there is a relation between the user and the role".
var roles = (from q in context.Roles
where context.UserHasRole.Any(o => o.UserId == userId && o.RoleId == q.Id)
select q).ToList();
return roles;
score:2
Break down the query and see what you area actually doing:
from q in context.Roles
So for every Role...
where context.UserHasRole.Any(o => o.UserId == userId)
Where there exists any entry in UserHasRole with the given userId...
&& context.UserHasRole.Any(p => p.RoleId == q.Id)
And there exists an entry in UserHasRole with a roleId matching the current Role...
select q
Select that role.
I have a feeling this logic is not what you desired, as it is independently checking to see if there is a UserHasRole
entry with the userId and a UserHasRole
entry with the role Id. But not that there is a UserHasRole
entry with both the userId and the role Id, which is probably the desired effect:
//Get the roles
var roles = (from q in context.Roles
where context.UserHasRole.Any(o => o.UserId == userId
&& o.RoleId == q.Id)
select q).ToList();
return roles;
score:2
I can't say for sure without looking at your data, but I would expect your query to give you many more roles back than you are looking for.
Here is pseduo-code for your existing query:
Get all roles
From those roles, accept only those where these are true:
Any mapping exists that matches the user ID
Any mapping exists that matches the role ID
The problem is that you don't ensure that those Any
clauses boil down to the same mapping. The user doesn't have to have that specific role - the user just has to have some role, and the role has to be mapped to some user.
Here's what you really want your query to do:
Get all roles
From those roles, accept only those where this is true:
Any mapping exists that matches *both* the user ID and the role ID
To implement this query:
var roles = (from r in context.Roles
where context.UserHasRole.Any(uhr => uhr.UserId == userId
&& uhr.RoleId == r.Id)
select r)
.ToList();
Source: stackoverflow.com
Related Articles
- Lambda expression incorrect
- Lambda Expression for Many to Many realtionship in C# EF 5 Code First
- How to write the same code using Lambda Expression
- How to write following code in lambda expression or linq?
- ForEach loop with Lambda expression in Razor code MVC 5 For List<T>
- VS Code Coverage won't recognize only possible Expression Lambda Path
- Linq Expression Building Incorrect number of parameters supplied for lambda declaration
- Lambda expression with statement body error in previously working code
- How to insert a record in lambda expression and possible way to shorten the length of code
- Retrieving Property name from lambda expression
- "A lambda expression with a statement body cannot be converted to an expression tree"
- An expression tree lambda may not contain a null propagating operator
- Cannot convert lambda expression to type 'string' because it is not a delegate type
- C# Pass Lambda Expression as Method Parameter
- "Or" equivalent in Linq Where() lambda expression
- Cannot use a lambda expression as an argument to a dynamically dispatched operation without first casting it to a delegate or expression tree type
- How to write a VB.Net Lambda expression
- Pass a lambda expression in place of IComparer or IEqualityComparer or any single-method interface?
- LINQ: Passing lambda expression as parameter to be executed and returned by method
- Cannot convert lambda expression to type 'object' because it is not a delegate type
- Can you create a simple 'EqualityComparer<T>' using a lambda expression
- Like in Lambda Expression and LINQ
- What is the Efficiency and Performance of LINQ and Lambda Expression in .Net?
- lambda expression for exists within list
- Can I use Linq's Except() with a lambda expression comparer?
- LINQ: Get all selected values of a CheckBoxList using a Lambda expression
- Expression cannot contain lambda expressions
- Difference between expression lambda and statement lambda
- Lambda expression for multiple parameters
- Simple Examples of joining 2 and 3 table using lambda expression
- Get all Shots (table) related to a SessionId (table) where a third table named Results connects them through Result Id. using Linq
- How to convert a Dynamics Advanced Find query to LINQ?
- How to filter LINQ with date
- Required Column not showing as required from UI
- Add a if statement condition to a Linq statement
- How can I get the average of a property in a list using LINQ?
- How can I do a group by with totals using LINQ and Entity Framework?
- Binding and Updating LINQ object in Gridview
- How to pivot a list in C#
- Assign 0 to Id to Null value
- How to select Distinct / Group By child property in both LINQ method and syntax?
- How to select highest common value across groups
- DbCommand with Linq?
- What's the correct way to fill a DropDownList using LINQ?
- C# Point3DCollection select point by min or max coordinate
- LINQ JOIN in .NET Core 3.1
- Using LINQ - Concat duplicate columns
- Convert SQL Query into C#
- If statement in LINQ (If no value do something...)
- Fastest way to query data by a specific date and data by next older date from oracle table?