We help IT Professionals succeed at work.

Need help with double join in LINQ query

dgerler used Ask the Experts™
Can someone help me with a double left join in LINQ?
Or perhaps suggest a better way. The goal is to get a list of the stores an appUsers has the authority to access. An appuser record's [Stores] column may contain a store number or it may contain a groupname. If a groupname, the storegroups table contains a list of stores in the group. Then I want to return the store number and the name of the store.
Give these tables:
>> Truncated for clarity <<
CREATE TABLE [dbo].[StoreGroups](
	[GroupName] [nvarchar](30) NOT NULL,
	[Store] [int] NOT NULL,
	[Client] [smallint] NOT NULL CONSTRAINT [DF_StoreGroups_Client]  DEFAULT ((1)),

CREATE TABLE [dbo].[Stores](
	[Client] [smallint] NOT NULL CONSTRAINT [DF_Stores_Client]  DEFAULT ((1)),
	[Store] [int] NOT NULL,

CREATE TABLE [dbo].[AppUsers](
	[UserID] [nvarchar](20) NOT NULL,
	[Password] [nvarchar](50) NOT NULL,
	[AuthorityLevel] [smallint] NOT NULL,
	[Stores] [varchar](30) NULL,
	[Area] [varchar](50) NULL,

Open in new window

I have this SQL query:
Select * 
from appusers au left join storegroups sg on au.stores = sg.groupname 
left join stores s on (isnumeric(au.stores) = 1 and au.stores = s.store) or sg.store = s.store

Open in new window

Here is how I tried it:
  storeString = (from au in xm.AppUsers
                           join sg in xm.StoreGroups
                           on au.Stores equals sg.GroupName into JoinedGroups
                           from jg in JoinedGroups.DefaultIfEmpty().....

Open in new window

but I got stuck with the second left join.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2008
I haven't done multiple left joins yet, but I would think that you need to chain the LEFT OUTER JOIN conditions.  The let operator is usually good for that, as it labels one set as the input for another.

Unrelated example using the let operator:

Watch out for let operator in linq to entities


I eneded up making changes in my code to get around the need for the multiple Left Joins. This wasn't a complete answer with an example of how to use it.