Error: ORA-00904: invalid identifier

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
developmentalmadness
Posts: 4
Joined: Thu 19 Mar 2009 22:10

Error: ORA-00904: invalid identifier

Post by developmentalmadness » Tue 14 Apr 2009 22:36

Here is my linq query:

Code: Select all

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?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 15 Apr 2009 14:35

Could you please send us (support@devart.com, 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.

Post Reply