CODEWITHSUNDEEP
sqllinqlinq-to-entities
Mehrshad
Mehrshad

Reputation: 55

Subtraction in LINQ

I have two tables: "Personnel" with ID,Name,... columns and "User" with PersonnelID (FK of ID column in "Personnel"),Username,Password columns

The question is how can I query the personnel entities which their IDs are not used in "User" table, I mean entities from "Personnel" with no record in "User" table?

At first I thought creating a view containing each "Personnel" entity with record in "User" table and subtracting this view from all Personnel entities, is the solution, but I don't know how to subtract two views in Linq

I used this query for the view:

from p in ObjectContext.personnels
join u in ObjectContext.users on p.ID equals u.PersonnelID 
select p;

Is this the solution? If yes how can I subtract two views?

Or is there a better way?

PS!!: Sorry for my bad english :D

Upvotes: 1

Views: 2009

Answers (1)

Alex Aza
Alex Aza

Reputation: 78457

The question is how can I query the personnel entities which their IDs are not used in "User" table

Answer:

from p in ObjectContext.personnels
where !ObjectContext.users.Any(u => u.PersonnelID == p.ID)
select p;

Although, you might want to limit the columns that you pull:

from p in ObjectContext.personnels
where !ObjectContext.users.Any(u => u.PersonnelID == p.ID)
select new { p.Id, p.Name, etc };

Upvotes: 3

Related Questions