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 Query
- 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
More Query from same tag
- Include Entity Framework Core (Loop of data)
- ANDing in JOIN Condition Using LINQ
- How to rewrite this LINQ using join with lambda expressions?
- Get all +ve number count in LINQ
- How to get files from a specific sub-directories using c#?
- How to repeat the array elements with specific sequence according to some number?
- convert the for loop with index using LINQ
- Passing an IComparer parameter to custom LINQ OrderBy extension method
- Creating a set of couples made of two rows in Linq
- C# Search filters- ignore specific diacritics
- How can I optmize a "select all" statement in linq that casts ~4000 rows to objects?
- LinqExpression: How do I get the value from comparison evaluation
- Calling Enumerable.Join on a IEnumerable collection causes segmentation fault
- Check null values in Linq query in Entity Framework
- LINQ to Entities does not recognize the method
- Is there an equivalent in LINQ for the SQL Server PARTITION OVER functionality?
- Sum values on list sorted by Distinct C#
- LINQ: How to Append List of Elements Into Another List
- Reading csv values into Dictionary with Where clause
- Converting Entity to DTO along with child entities
- How to Convert the value in DataTable into a string array in c#
- Linq performance: does it make sense to move out condition from query?
- How to safely call Count(Func) on an IEnumerable that can be changed by another thread?
- Linq Stored procedures, send parameter via System.Data.CommandType.StoredProcedure
- LINQ 2 SQL, Wrong SQL Generation
- Stumped in Dependency Property in VB
- Implication of using dynamic Expression<Func<T,X>> in NHibernate
- how do you loop through a List based on List contents
- Expression predicates with several parameters
- Performance of Skip (and similar functions, like Take)