Last post Feb 24, 2012 12:46 AM by Decker Dong - MSFT
Feb 22, 2012 09:25 PM|Nicolas V|LINK
I am trying to pull the list of all groups that a given person may be a member of. The example below is just mock T-SQL. PersonGroup would be a table between Group and Person.
IsSelected is a calculated column using the left join => null = false, not null = true technique.
How would I go about coding this in C# LINQ? Been scratching my head for a little while.
For instance, PersonID 1 is part of group 1, 3 and 5 and there are 5 groups, from 1 to 5, I would get this (and yes, IsSelected has to be cast as bool):
GroupID | IsSelected
1 | true
2 | false
3 | true
4 | false
5 | true
Thanks a lot for your help, been scratching my head on this for a bit.
select G.GroupID, G.Description,
IsSelected = case when P.PersonID is null then 0 else 1 end
select ID as GroupID,
left outer join (
select GroupID, PersonID
on G.GroupID = P.GroupID
where P.PersonID = 1 or P.PersonID is null
order by G.Description
Feb 23, 2012 02:43 AM|adamturner34|LINK
Why write that in LINQ? Create a "view" in SQL Server and use that.
Feb 23, 2012 03:37 PM|Nicolas V|LINK
1- the view cannot work as the result of the query is specific to the PersonID
2- I am looking to learn how to do this using a LINQ statement.
Feb 23, 2012 03:46 PM|texx|LINK
Quick question, are you using Linq to Objects, or Entity Framework ? if Entity framework, does the joining table have an Entity Set, or does each Person have a collection of Groups, and each Group a collection of Persons ? Or does each person have a collection
of persongroups ?
Feb 24, 2012 12:46 AM|decker dong - msft|LINK
I suggest you return a complete table model from either EF or LINQ-TO-SQL with the help of Stored procdure or Views（Just use Views or SP in EF）：
Sample torturial here：