Trouble in converting SQL to LINQ

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.

Answer 1

From gallery In db.Gallery into g_

From matrix in g.DefaultIfEmpty()

 http://msdn.microsoft.com/en-us/library/bb397895.aspx

Answer 2

Davearia,

Plz download this tool:

http://sqltolinq.com/

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.

---

帮助一起改进论坛质量？提交你的意见于此。
我的博客园
慈善点击，点击此处
和谐拯救危机，全集下载，净化人心

Answer 3

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