CODEWITHSUNDEEP
c#sqllinqlambda
Alsan
Alsan

Reputation: 325

lambda linq not join c#

I have this query that returns me the names of the users whose userid is in access table and users, what I want is to give me back those userid of users who are not in access.:

 var query = db.Users
      .Join(db.Access, c => c.UserId, o => o.UserId,
       (c, o) => new { c.UserId, c.Name });

In sql it´d be something like this:

SELECT  Users.Name
FROM     Access INNER JOIN
               Users ON Access.UserId <> Users.UserId

What is the lambda expression equivalent to the sql script?

Upvotes: 2

Views: 1183

Answers (3)

Alsan
Alsan

Reputation: 325

var exclude = db.Access.Select(p => p.UserId);


var us = db.Users.Where(q => !exclude.Contains(q.UserId)).ToList();


ViewBag.UsersId = new SelectList(us, "UserId", "Name");

Finally that is the right answer, the only difference is in the first line.

Thank you all!

Upvotes: 0

Kaf
Kaf

Reputation: 33809

You can also use ! Contains() like below:

//Get Access user ids into an array
var AccessUserIds = db.Access.Select(a => new {a.UserId}).ToArray();

//Users who are not in Access user id array
var Results = db.Users.Where(u => !AccessUserIds.Contains(u.UserId));

Upvotes: 0

Muhammad Hani
Muhammad Hani

Reputation: 8664

Use Except

var res = db.Access.Except(query);

Upvotes: 6

Related Questions