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):
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
from (
select ID as GroupID,
Description
from Group
) G
left outer join (
select GroupID, PersonID
from PersonGroup
) P
on G.GroupID = P.GroupID
where P.PersonID = 1 or P.PersonID is null
order by G.Description
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 ?
Nicolas V
Member
111 Points
49 Posts
LINQ: how do you handle null based calculation in an outer join query?
Feb 22, 2012 09:25 PM|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.
adamturner34
Contributor
3964 Points
999 Posts
Re: LINQ: how do you handle null based calculation in an outer join query?
Feb 23, 2012 02:43 AM|LINK
Why write that in LINQ? Create a "view" in SQL Server and use that.
Nicolas V
Member
111 Points
49 Posts
Re: LINQ: how do you handle null based calculation in an outer join query?
Feb 23, 2012 03:37 PM|LINK
Two reasons:
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.
Thanks!
texx
Contributor
2412 Points
415 Posts
Re: LINQ: how do you handle null based calculation in an outer join query?
Feb 23, 2012 03:46 PM|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 ?
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: LINQ: how do you handle null based calculation in an outer join query?
Feb 24, 2012 12:46 AM|LINK
Hello NicolasV：）
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：
1）For linq-to-sql：http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
2）For linq-to-ef：http://msdn.microsoft.com/en-us/library/bb896279.aspx