CODEWITHSUNDEEP
c#sqllinq
jrn
jrn

Reputation: 2800

LINQ sql expression to exclude data that is present in other table

I am trying to write a LINQ query to return a list of username which are not yet assigned. My Usernames table looks like this:

Id | Username  
1  | user.1
2  | user.2
3  | user.3

The Assignments table holds just the username, next to some other data:

Username | SomeOtherData
user.1   | foo
user.3   | bar

The LINQ query that I am trying to write should return user.2 in the above example.

My query, unfortunately, returns too many users and I am not sure how to tweak my LINQ expression:

from u in Usernames
    join a in Assignments on u.Username equals a.Username
    where u.Username != a.Username
    select u

Adding the Distinct() method filters down the result set but it still returns usernames which are assigned:

(from u in Usernames
    join a in Assignments on u.Username equals a.Username
    where u.Username != a.Username
    select new { u }).Distinct()

What would be the best way to tweak these LINQ expressions, to return only those usernames which are not in the assignments table?

Upvotes: 0

Views: 1491

Answers (2)

Amay
Amay

Reputation: 108

You need to do a left join using DefaultIfEmpty. Something like as follows:

from u in Usernames 
join a in Assignments on u.Username equals a.Username into userAssignments
from ua in userAssignments.DefaultIfEmpty()
    where ua == null
    select u

Upvotes: 1

Harsh
Harsh

Reputation: 3751

You are joining the two tables on username, that is not going to return you the usernames that are not common in both the table. You need to select the usernames which are not present in Assignment table.

Context.Usernames.Where(x=> !Assignments.Select(y => y.Username).Contains(X.Username));

Another way of writing the same query :

Context.Usernames.Where(x=> Assignments.All(y => y.Username ! = x.UserName));

Upvotes: 4

Related Questions