CODEWITHSUNDEEP
c#linqentity-frameworksyntax
SeToY
SeToY

Reputation: 5895

Query dynamic data with LINQ

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 }

Upvotes: 3

Views: 273

Answers (3)

Douglas
Douglas

Reputation: 54917

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
    };

Upvotes: 3

Joe
Joe

Reputation: 82654

Use a join with DefaultIfEmpty

var query = from house in _dataContext.Houses
            join assignment in _dataContext.PersonHouseAssignments
            on house.Id equals assignment.HouseId into results
            from r in results.DefaultIfEmpty()
            select new 
            {
               HouseNames = house.HouseName, 
               IsAssigned = r != null, 
               Description = r != null ? r.Description : string.Empty
            }

This will generate SQL with a left outer join to include Description.

Upvotes: 3

BrokenGlass
BrokenGlass

Reputation: 161012

You can use a GroupJoin for this:

var query = from h in _dataContext.Houses
            join a in _dataContext.PersonHouseAssignments 
            on h.Id equals a.HouseId into assignments
            select new 
            {
               HouseName = p.HouseName, 
               IsAssigned = assignments.Any(),
               //..
            }

Upvotes: 3

Related Questions