Query dynamic data with LINQ

Question

I got three tables: Houses, Person and PersonHouseAssignments

In my Houses-Table I got different columns like HouseName, HouseType and Description.

In PeopleHouseAssignments I got columns like PersonId and HouseId.

---

I now want to display this in my WPF DataGrid in the following way:

A column named HouseNames that contains ALL the available HouseNames from my Houses-Table.

I now got a checkbox in my datagrid that should assign the currently selected person to the house.

[ ] House1
[x] House2
[ ] House3
[x] House4

This Person is assigned to House2 and House4, because the table "PersonHouseAssignments" has two rows:

PersonId | HouseId
1        |    2
1        |    4

How should I create my LINQ Query?

I've already tried something like this, but this didn't work:

from p in _dataContext.Houses
from a in _dataContext.PersonHouseAssignments
select new {HouseNames = p.HouseName, IsAssigned = a.HouseId == p.Id, Description = a.Description }

Answer 1

I assume that your query would be specific to a particular person? Namely, the IsAssigned value of each item in your returned collection would be true if that particular person is assigned to the house named HouseName? In that case, you could use a nested subquery:

int personId = 1;

var query = 
    from h in _dataContext.Houses
    select new 
    {
        HouseName = h.HouseName, 
        IsAssigned = 
        (
           from a in _dataContext.PersonHouseAssignments
           where a.HouseId == h.Id && a.PersonId == personId
           select a
        ).Any()
    };

Edit: If you want to include the Description from PersonHouseAssignments, you could use:

int personId = 1;

var query =
    from h in _dataContext.Houses
    let a = 
    (                
        from pha in _dataContext.PersonHouseAssignments
        where pha.HouseId == h.Id && pha.PersonId == personId
        select pha
    ).FirstOrDefault()
    select new
    {
        HouseName = h.HouseName,
        Description = a != null ? a.Description : "",
        IsAssigned = a != null
    };