CODEWITHSUNDEEP
c#sqllinq
will
will

Reputation: 871

How to write linq query comparing 2 tables?

Okay so I have a database that has a Projects, Users and ProjectMembers table, the Projects table has an ID column and ProjectOwner column which is a foreign key from the Users table, the Users table has ID, username columns and the ProjectMembers table has an ID, ProjectID and UserID column with the later 2 columns being foreign keys from the Projects table and Users table.

Basically the project is something that manages projects, a user can create a project which makes them the owner and then other users can be assigned to that project which adds a record to the ProjectMembers table.

I'm trying to write a linq query that selects all records from the Projects table where a specific user isn't the ProjectOwner but they are a ProjectMember in the ProjectMembers table?

I've made various attempts but so far haven't been able to get the query right.

Upvotes: 0

Views: 1628

Answers (3)

mozgow
mozgow

Reputation: 197

Something like this should work:

from p in Projects
    join m in ProjectMembers on m.ProjectID equals p.ID
    where m.UserID == userID && p.ProjectOwner != userID
    group p by p.ID
    select p

Upvotes: 2

Igor Ševo
Igor Ševo

Reputation: 5495

Try this:

from x in Users
where ProjectMembers.Any(y => y.UserID==x.ID) &&
!ProjectOwners.Any(z => z.ProjectOwner==x.ID)
select x;

Upvotes: 0

Bierpfurz
Bierpfurz

Reputation: 1

You can make several from clauses:

from e1 in table1
from e2 in table2
where e1.key == e2.id
select e1.name

Upvotes: 0

Related Questions