CODEWITHSUNDEEP
c#linqentity-frameworksyntax
SeToY
SeToY

Reputation: 5895

Querying two SQL-Tables with LINQ

I got a table Peoples and a table PeopleRequirements.

PeopleRequirements.PeopleId is assigned with a FK Constraint to Peoples.Id and also contains a bit (boolean) field PeopleRequirements.IsActive.

Now I want to query all people to which a row in PeopleRequirements exists (where a row exists that equals PeopleId == People.Id) and the PeopleRequirements.IsActive field is true.

How can I achieve this with EF4 and LINQ?

I've already tried using the NavigationProperties:

e.QueryableSource = _dataContext.Peoples.Where(a => a.EMail != string.Empty && a.EMail != null && a.PeopleRequirements.Count > 0);

Upvotes: 0

Views: 124

Answers (1)

undefined
undefined

Reputation: 34238

This is normally not how you would do things in EF, normally you would use a Navigation Property in your model to link the two entities. Having said that heres the linq you would use if they are not linked in a normal way.

from pplReq in PeopleRequirements
from person in People
where pplReq.PeopleId == person.Id
where pplReq.IsActive
select pplReq;

EDIT: Based on your update around navigation properties you can use 

from pr in _dataContext.PeopleRequirements
where pr.People != null
where pr.IsActive
select pr

this will find all PeopleRequirements which are active and are linked to an actual person

EDIT: heres the converse case, people with attached active requirements

from person in _dataContext.Peoples
from req in person.PeopleRequirements
where req.IsActive
select distinct person

im not too sure if you need the distinct or not.

Upvotes: 1

Related Questions