troubleshooting Question

Need help with double join in LINQ query

Avatar of David Gerler
David GerlerFlag for United States of America asked on
C#ASP.NET.NET Programming
2 Comments1 Solution613 ViewsLast Modified:
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,
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
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().....
but I got stuck with the second left join.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros