none
Trouble in converting SQL to LINQ RRS feed

  • Question

  • Here is my SQL that works ok:

    SELECT 
    	[gallery].*,
    	'Authorised' AS [Permissions]
    FROM
    	tblPublicUserWebsite [user]
    	INNER JOIN [dbo].[tblPublicUserWebsiteToPublicUserGroup] [userToGroup]
    		ON [user].Id = [userToGroup].PublicUserWebsiteId
    	INNER JOIN [dbo].[tblPublicUserGroup] [pug]
    		ON [pug].Id = [userToGroup].PublicUserGroupId
    	INNER JOIN [dbo].[tblDataItemAppDataPublicUserGroupMatrix] [matrix]
    		ON [matrix].PublicUserGroupId = [pug].Id
    	INNER JOIN [dbo].[tblAppDataType] [adt]
    		ON [adt].Id = [matrix].AppDataTypeId
    	INNER JOIN [dbo].[tblGallery] [gallery]
    		ON [gallery].WebsiteId = [user].WebsiteId AND [matrix].DataItemId = [gallery].Id	
    WHERE
    	[user].Id = 44800
    	AND [adt].Name = 'Gallery'
    	
    UNION
    
    --Select gallerys don't require premissions
    SELECT 
    	[gallery].*,
    	'Not Needed' AS [Permissions]
    FROM
    	[dbo].[tblGallery] [gallery]
    	LEFT JOIN [dbo].[tblDataItemAppDataPublicUserGroupMatrix] [matrix]
    		ON [gallery].Id = [matrix].DataItemId
    WHERE
    	[gallery].WebsiteId = 39
    	AND matrix.AppDataTypeId IS NULL

    Here is my LINQ up to now:

    Dim query = (
                    From user In db.PublicUserWebsite Join userToGroup In db.tblPublicUserWebsiteToPublicUserGroup On user.WebsiteId Equals userToGroup.PublicUserWebsiteId _
                    Join pug In db.PublicUserGroup On pug.Id Equals userToGroup.PublicUserGroupId _
                    Join matrix In db.tblDataItemAppDataPublicUserGroupMatrix On matrix.PublicUserGroupId Equals userToGroup.PublicUserGroupId _
                    Join adt In db.AppDataType On adt.Id Equals matrix.AppDataTypeId _
                    Join gallery In db.Gallery On gallery._WebsiteId Equals user.WebsiteId And matrix.AppDataTypeId Equals gallery._Id _
                    Where user.Id = 44800 And adt.Name = "Gallery" _
                    Select gallery, Permission = "Authorise"
                ).Union(From gallery In db.Gallery _
                        Where gallery._WebsiteId = 39 _                               
                        Select gallery, Permission = "Not Needed"
                        )

    I've managed the first part of the UNION (although I have not any testing as of yet). The 2nd bit should be easier but I am struggling with the left join. Makes it harder as I need to do this VB.NET and I am more used to C#.

    Any help with this would be appreciated. Thanks, Dave.


    • Edited by Davearia Friday, February 22, 2013 3:59 PM
    Friday, February 22, 2013 3:58 PM

Answers

All replies