Devart Forums

Posted: **Tue 14 Apr 2009 22:36**

Here is my linq query:

var query = (from u in Repository.Users
				  where u.UserName == username
					 && u.Roles.Any(
						 r => r.SystemFunctions.Any(
							 f => f.FunctionPages.Any(
								 fp => fp.Page.PageName == currentPage
								 && fp.AccessType >= (int)AccessType.Read
							 )
						 )
					 )
				  select u);
									  
Console.WriteLine(query.ToTraceString());
GPM.Data.Models.User user = query.FirstOrDefault();

Here is the resulting SQL :

Code: Select all

SELECT 
"Extent1".USER_ID AS USER_ID, 
"Extent1".USER_NAME AS USER_NAME
FROM GPMMD.USERS "Extent1"
WHERE ("Extent1".USER_NAME = :p__linq__1) AND ( EXISTS (SELECT 
    1 AS C1
    FROM ( SELECT 
        "Extent2".ROLE_ID AS ROLE_ID
        FROM GPMMD.USER_ROLE "Extent2"
        WHERE "Extent1".USER_ID = "Extent2".USER_ID
    )  "Project1"
    WHERE  EXISTS (SELECT 
        1 AS C1
        FROM ( SELECT 
            "Extent3".SYSTEM_FUNCTION_ID AS SYSTEM_FUNCTION_ID
            FROM GPMMD.ROLE_FUNCTION "Extent3"
            WHERE "Project1".ROLE_ID = "Extent3".ROLE_ID
        )  "Project2"
        WHERE  EXISTS (SELECT 
            1 AS C1
            FROM  GPMMD.FUNCTION_PAGE "Extent4"
            INNER JOIN GPMMD.PAGE "Extent5" ON "Extent4".PAGE_ID = "Extent5".PAGE_ID
            WHERE (("Project2".SYSTEM_FUNCTION_ID = "Extent4".SYSTEM_FUNCTION_ID) AND ("Extent5".PAGE_NAME = :p__linq__2)) AND ("Extent4".ACCESS_TYPE >= 1)
        )
    )
))

I am getting an error for "Extent1".USER_ID

This linq query works fine:

Code: Select all

			 var query = from u in Repository.Users
							from r in u.Roles
								from f in r.SystemFunctions
									from fp in f.FunctionPages
									where fp.Page.PageName == currentPage
										&& fp.AccessType >= (int) AccessType.Read
						where u.UserName == username
						select u;
						
				Console.WriteLine(query.ToTraceString());
				GPM.Data.Models.User user = query.FirstOrDefault();

and generates this SQL:

Code: Select all

SELECT 
"Filter1".USER_ID1 AS USER_ID, 
"Filter1".USER_NAME AS USER_NAME
FROM   (SELECT "Extent1".USER_ID AS USER_ID1, "Extent1".USER_NAME AS USER_NAME, "Extent2".ROLE_ID AS ROLE_ID1, "Extent2".USER_ID AS USER_ID2, "Extent3".ROLE_ID AS ROLE_ID2, "Extent3".SYSTEM_FUNCTION_ID AS SYSTEM_FUNCTION_ID1, "Extent4".ACCESS_TYPE AS ACCESS_TYPE, "Extent4".PAGE_ID AS PAGE_ID, "Extent4".SYSTEM_FUNCTION_ID AS SYSTEM_FUNCTION_ID2
	FROM    GPMMD.USERS "Extent1"
	INNER JOIN GPMMD.USER_ROLE "Extent2" ON "Extent1".USER_ID = "Extent2".USER_ID
	INNER JOIN GPMMD.ROLE_FUNCTION "Extent3" ON "Extent2".ROLE_ID = "Extent3".ROLE_ID
	INNER JOIN GPMMD.FUNCTION_PAGE "Extent4" ON "Extent3".SYSTEM_FUNCTION_ID = "Extent4".SYSTEM_FUNCTION_ID
	WHERE "Extent4".ACCESS_TYPE >= 1 ) "Filter1"
INNER JOIN GPMMD.PAGE "Extent5" ON "Filter1".PAGE_ID = "Extent5".PAGE_ID
WHERE ("Extent5".PAGE_NAME = :p__linq__1) AND ("Filter1".USER_NAME = :p__linq__2);

but the problem is the Include method only seems to work with the first linq query. If I use Include() with the 2nd version the related data isn't included. I don't care which syntax I use, but I'd like to be able to use Include - any suggestions?

Posted: **Wed 15 Apr 2009 14:35**

Could you please send us ([email protected], subject "EF: Include error") a small test project illustrating the problem with Include()?
Please add the script of the DB objects you are using in this query.
Please also add the .edmx file in case you have made any changes to it.

Devart Forums

Error: ORA-00904: invalid identifier

Error: ORA-00904: invalid identifier