Trouble in converting SQL to LINQ RRS feed

  • Question

  • Here is my SQL that works ok:

    	'Authorised' AS [Permissions]
    	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	
    	[user].Id = 44800
    	AND [adt].Name = 'Gallery'
    --Select gallerys don't require premissions
    	'Not Needed' AS [Permissions]
    	[dbo].[tblGallery] [gallery]
    	LEFT JOIN [dbo].[tblDataItemAppDataPublicUserGroupMatrix] [matrix]
    		ON [gallery].Id = [matrix].DataItemId
    	[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


All replies

  • From gallery In db.Gallery into g_

    From matrix in g.DefaultIfEmpty()


    Friday, February 22, 2013 4:14 PM
  • Davearia,

    Plz download this tool:


    If this still cannot help u, it seems that you have to use SQL (ADO.NET) or just make it into a SP (Stored Procdure) + EF by importing it. Because not every complicated sql can be converted to LINQ.


    • Marked as answer by Davearia Monday, February 25, 2013 11:26 AM
    Saturday, February 23, 2013 3:16 AM
  • Hi,

    I ended up with:

    From gallery In db.Gallery _
    Where _
        CLng(gallery.WebsiteId) = Website.Current.Id _
        And Not (From matrix In db.tblDataItemAppDataPublicUserGroupMatrix _
                Select matrix.DataItemId).Contains(gallery.Id)
    Select gallery

    I did use Linqer but dropped the Left join as I couldn't get anything to work with. I tried Michael's suggestion amongst my attempts and anything Linquer suggested so in the end I added an extra where clause

    • Proposed as answer by ThankfulHeart Tuesday, February 26, 2013 3:55 AM
    Monday, February 25, 2013 11:26 AM