Hi,

I'm using following versions:

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll : 2.6.9.601 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll : 2.6.9.511 SD.LLBLGen.Pro.DQE.Oracle10g.NET20.dll : 2.6.9.203 SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll : 2.6.8.1114

I've read about correlated queries in the documentation. I'm trying to implement some sort of correlated queries with linq to llbl and oracle db but I'm getting some errors in generated sql statement and so I'm not sure if this kind of linq query is supported.

For reproducing this issue I'm showing a simple sample : getting the category name of products in northwind database. Yes I know it's possible to get the same result using related entities or a join but this is only for demonstration and simplicity.

using the following linq query against SQL Server Northwind DB:

DataAccessAdapter adapter = new DataAccessAdapter(@"data source=.\;initial catalog=Northwind;Trusted_Connection=yes;");
            LinqMetaData metaData = new LinqMetaData();
            metaData.AdapterToUse = adapter;

            var q = from p in metaData.Products select new ProductsEntity() 
            {
                ProductId = p.ProductId,
                ProductName = p.ProductName,
                CategoryName = (from c in metaData.Categories where c.CategoryId == p.CategoryId select c).First().CategoryName
            };
            var list = q.ToList();

produces the following SQL query which runs without any problem:

SELECT [LPLA_1].[ProductID] AS [ProductId], [LPLA_1].[ProductName], (SELECT TOP 1 [LPLA_2].[CategoryName] FROM [Northwind2000].[dbo].[Categories] [LPLA_2]  WHERE ( ( ( ( [LPLA_2].[CategoryID] = [LPLA_1].[CategoryID]))))) AS [CategoryName] FROM [Northwind2000].[dbo].[Products] [LPLA_1] 

but running the same linq query against Oracle produces the following SQL statement:

SELECT "LPLA_1"."PRODUCTID" AS "Productid", "LPLA_1"."PRODUCTNAME" AS "Productname", (SELECT * FROM (SELECT "LPLA_2"."CATEGORYNAME" AS "Categoryname" FROM "MYSCHEMA"."CATEGORIES" "LPLA_2" WHERE ( ( ( ( "LPLA_2"."CATEGORYID" = "LPLA_1"."CATEGORYID"))))) WHERE rownum <= 1) AS "CategoryName" FROM "MYSCHEMA"."PRODUCTS" "LPLA_1"

which throws exception when running : ORA-00904: "LPLA_1"."CATEGORYID": invalid identifier.

I guess the extra select * from statement is the cause.

There is another problem when using anonymous types:

var q = from p in metaData.Products
                    select new 
                    {
                        Productid = p.Productid,
                        Productname = p.Productname,
                        CategoryName = (from c in metaData.Categories where c.Categoryid == p.Categoryid select c).First().Categoryname
                    };

The generated SQL statement in Oracle:

SELECT "LPLA_1"."PRODUCTID" AS "Productid", "LPLA_1"."PRODUCTNAME" AS "Productname", (SELECT * FROM (SELECT "LPLA_4"."CATEGORYNAME" AS "Categoryname" FROM "MYSCHEMA"."CATEGORIES" "LPLA_4" WHERE ( ( ( ( "LPLA_5"."CATEGORYID" = "LPLA_1"."CATEGORYID"))))) WHERE rownum <= 1) AS "CategoryName" FROM "MYSCHEMA"."PRODUCTS" "LPLA_1"

There is no "LPLA_5" alias.

With best regards, Arash.

P.S: I found the script for generating Oracle version of northwind DB from the this url: http://jagchat.spaces.live.com/blog/cns!41050F68F010A662!758.entry

It uses the same code so it's a bit weird the aliases aren't correct on oracle but are correct on sqlserver. I'll look into it.

Looking at the first query:

SELECT  "LPLA_1"."PRODUCTID" AS "Productid", 
        "LPLA_1"."PRODUCTNAME" AS "Productname", 
        (
            SELECT * 
            FROM 
            (   
                SELECT  "LPLA_2"."CATEGORYNAME" AS "Categoryname" 
                FROM    "MYSCHEMA"."CATEGORIES" "LPLA_2" 
                WHERE (((( "LPLA_2"."CATEGORYID" = "LPLA_1"."CATEGORYID"))))
            ) 
            WHERE rownum <= 1
        ) AS "CategoryName" 
FROM    "MYSCHEMA"."PRODUCTS" "LPLA_1"

the scalar subquery needs to result in a single value, which is done through the rownum limiter however indeed the wrapping breaks the correlation between scalar query and the outer query, as the LPLA_1 alias should be known inside the scalar query as it's an alias in the outer query.

I'll look into writing this differently but I'm not sure if it's possible to write this differently in Oracle.

The code of the second query is a bit odd: you made a typo in the casing there (Categoryname). Did you copy the code over? Is it the exact query you ran because then the first one shouldn't compile and the second one should also fail on sqlserver, as the alias is embedded in the correlation predicate by the linq provider so way before it arrives at the db specific DQE which simply converts elements into SQL. So please look into that second query and provide the exact material. I'll look into the second query as well if I can reproduce that.

(edit) the second query fails on sqlserver too with the same error (LPLA_5 not found). Will look into this separately from the first error which is oracle only it seems.

Query 1, it's not the aliasing: this works fine on 10g:

SELECT "LPLA_1".EMPNO,
        "LPLA_1".ENAME,
        (
            SELECT * 
            FROM 
            (
                SELECT D.DNAME
                FROM DEPT D
                WHERE D.DEPTNO = "LPLA_1".DEPTNO 
            )
            WHERE rownum <= 1
        ) AS DName
FROM EMP "LPLA_1"

(handwritten, mimics your query).

So it has to be the 'CATEGORYID' field. I'll see if I can reproduce this here on 10g.

(edit). Looking at the creation script you linked to:

CREATE TABLE Products 
( 
  ProductID  NUMBER NOT NULL, 
  ProductName  VARCHAR2(40) NOT NULL, 
  SupplierID  NUMBER, 
  CategoryID  NUMBER, 
  QuantityPerUnit  VARCHAR2(20), 
  UnitPrice  NUMBER, 
  UnitsInStock  NUMBER, 
  UnitsOnOrder  NUMBER, 
  ReorderLevel  NUMBER, 
  Discontinued  NUMBER(1) NOT NULL, 
CONSTRAINT PK_Products 
  PRIMARY KEY (ProductID), 
CONSTRAINT CK_Products_UnitPrice   CHECK ((UnitPrice >= 0)), 
CONSTRAINT CK_ReorderLevel   CHECK ((ReorderLevel >= 0)), 
CONSTRAINT CK_UnitsInStock   CHECK ((UnitsInStock >= 0)), 
CONSTRAINT CK_UnitsOnOrder   CHECK ((UnitsOnOrder >= 0)), 
CONSTRAINT FK_Products_Categories FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID), 
CONSTRAINT FK_Products_Suppliers FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
) 
/ 

the field names are case sensitive, however your query has the names all in caps. I'm not sure if your schema's collation is case sensitive or not. Did you create it using the project converter or something?

And where does 'categoryname' come from in the first query? did you add that property yourself?

When I add 'DName' to Emp in my scott project, I can run the following query without a problem:

var q = from e in metaData.Emp
        select new EmpEntity()
        {
            Empno = e.Empno,
            Ename = e.Ename,
            DName = (from d in metaData.Dept where d.Deptno == e.Deptno select d).First().Dname
        };

query:

SELECT  "LPLA_1"."EMPNO" AS "Empno", 
        "LPLA_1"."ENAME" AS "Ename", 
        (
            SELECT * FROM (
                SELECT "LPLA_2"."DNAME" AS "Dname" 
                FROM "SCOTT"."DEPT" "LPLA_2" 
                WHERE ( ( ( ( "LPLA_2"."DEPTNO" = "LPLA_1"."DEPTNO"))))
            ) WHERE rownum <= 1
        ) AS "DName" 
FROM "SCOTT"."EMP" "LPLA_1"

I at least fixed the second problem. The fixed provider is attached. Please answer my questions in my previous post so we can look further into the first problem. Thanks

Otis wrote:

the field names are case sensitive, however your query has the names all in caps. I'm not sure if your schema's collation is case sensitive or not. Did you create it using the project converter or something?

The provided sql script has all table names in Pascal Case. I ran the scripts using Navicat For Oracle and in our server all tables names are created in capital. The provided linq query for Oracle has no compilation error so I guess there is no problem in table names.

And where does 'categoryname' come from in the first query? did you add that property yourself?

CategoryName is a custom property and not mapped in to any field in DB. I added this property in partial class definition of ProductsEntity class generated by LLBL.

Thank you for your support, Arash.

arash wrote:

Otis wrote:

the field names are case sensitive, however your query has the names all in caps. I'm not sure if your schema's collation is case sensitive or not. Did you create it using the project converter or something?

The provided sql script has all table names in Pascal Case. I ran the scripts using Navicat For Oracle and in our server all tables names are created in capital. The provided linq query for Oracle has no compilation error so I guess there is no problem in table names.

I don't know what's the cause in your situation as here I can't reproduce it with a different schema (see above). I'll try with the schema you provided but I don't see how that can be different. The error you got refers to an alias and a column name. The alias is properly defined, so it must be the column name however I have no idea why this fails as in my earlier example with another schema, it works OK.

And where does 'categoryname' come from in the first query? did you add that property yourself?

CategoryName is a custom property and not mapped in to any field in DB. I added this property in partial class definition of ProductsEntity class generated by LLBL.

I understand, you used it twice, however once as CategoryName and once as Categoryname, with a different casing on the 'n', hence my question if the queries were real or that you typed them in again here, which could mean you made a mistake perhaps (e.g. not copying everything over) which might have been important.

I'll try with the schema you provided today, however I don't expect a different outcome.

I created the northwind schema and ran your query:

static void Main(string[] args)
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from p in metaData.Product
                select new ProductEntity()
                {
                    Productid = p.Productid,
                    Productname = p.Productname,
                    CategoryName = (from c in metaData.Category where c.Categoryid == p.Categoryid select c).First().Categoryname
                };
        foreach(var v in q)
        {
            
        }
    }
}

SQL code (tables created in HR schema, which was empty in our testdb)

SELECT  "LPLA_1"."PRODUCTID" AS "Productid", 
        "LPLA_1"."PRODUCTNAME" AS "Productname", 
        (
            SELECT * FROM (
                SELECT  "LPLA_2"."CATEGORYNAME" AS "Categoryname" 
                FROM    "HR"."CATEGORIES" "LPLA_2" 
                WHERE (((("LPLA_2"."CATEGORYID" = "LPLA_1"."CATEGORYID"))))
            ) 
            WHERE rownum <= 1
        ) AS "CategoryName" 
FROM    "HR"."PRODUCTS" "LPLA_1"

Ran as expected: ok.

So my question to you is: when you run the query generated using toad on the schema (or other sql tool), do you get the same error? And if so, can you determine what the error is? As we can't reproduce the error (the second error you reported is fixed with the assembly attachted to a post earlier in this thread).

Let me explain more about namings, For the first issue I only provided SQL Server linq query sample, because The linq query for the Oracle is the same. The difference you mentioned is just because of different casing of letters between my SQL Server sample and Oracle sample. In SQL server the field name is "CategoryName" so when imported in LLBL the property name is "CategoryName", but in Oracle the field name is "CATEGORYNAME" so when imported in LLBL the property is "Categoryname". The situation is the same for other properties. For the second issue I just posted the linq query for oracle.

The Oracle linq query for the first issue is:

DataAccessAdapter adapter = new DataAccessAdapter(@"data source=mytnsname;user id=MYSCHEMA;password=MYPASSWORD;persist security info=false;");
            LinqMetaData metaData = new LinqMetaData();
            metaData.AdapterToUse = adapter;

            var q = from p in metaData.Products
                    select new ProductsEntity()
                    {
                        Productid = p.Productid,
                        Productname = p.Productname,
                        CategoryName = (from c in metaData.Categories where c.Categoryid ==  p.Categoryid select c).First().Categoryname
                    };
            var list = q.ToList();

This is a real query and I didn't just type it here, I only changed the connection string info.

Otis wrote:

I created the northwind schema and ran your query:

static void Main(string[] args)
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from p in metaData.Product
                select new ProductEntity()
                {
                    Productid = p.Productid,
                    Productname = p.Productname,
                    CategoryName = (from c in metaData.Category where c.Categoryid == p.Categoryid select c).First().Categoryname
                };
        foreach(var v in q)
        {
            
        }
    }
}

SQL code (tables created in HR schema, which was empty in our testdb)

SELECT  "LPLA_1"."PRODUCTID" AS "Productid", 
        "LPLA_1"."PRODUCTNAME" AS "Productname", 
        (
            SELECT * FROM (
                SELECT  "LPLA_2"."CATEGORYNAME" AS "Categoryname" 
                FROM    "HR"."CATEGORIES" "LPLA_2" 
                WHERE (((("LPLA_2"."CATEGORYID" = "LPLA_1"."CATEGORYID"))))
            ) 
            WHERE rownum <= 1
        ) AS "CategoryName" 
FROM    "HR"."PRODUCTS" "LPLA_1"

Ran as expected: ok.

So my question to you is: when you run the query generated using toad on the schema (or other sql tool), do you get the same error? And if so, can you determine what the error is? As we can't reproduce the error (the second error you reported is fixed with the assembly attachted to a post earlier in this thread).

It's very odd

Are you sure the query ran fine? I ran your SQL query in two different oracle tools and I've the same error : ORA-00904: "LPLA_1"."CATEGORYID": invalid identifier. How is that possible? I checked the CATEGORIES table again and it indeed has a field named CATEGORYID.

When I changed your query to the following query, It ran as expected:

SELECT   "LPLA_1"."PRODUCTID" AS "Productid",
        "LPLA_1"."PRODUCTNAME" AS "Productname",
        (
                SELECT   "LPLA_2"."CATEGORYNAME" AS "Categoryname"
                FROM     "HR"."CATEGORIES" "LPLA_2"
                WHERE (((("LPLA_2"."CATEGORYID" = "LPLA_1"."CATEGORYID")))) and rownum <= 1
            
        ) AS "CategoryName"
FROM     "HR"."PRODUCTS" "LPLA_1"

Just to mention that I removed "rownum <= 1" to inner query and deleted the wrapping query. So I guess that the wrapping query is the cause of problem, but I don't know why it runs on your oracle instance correctly. I'm using 11g and unfortunately I don't have access to a 10g instance. but it should not be related to the Oracle version.

Thank you.

If it's the wrapping SELECT *, it's a bit odd indeed. We use a default 10g install, no special settings whatsoever. Could it be it's a specific setting that this goes wrong? Does your DBA know, perhaps?

I'll look into changing the SQL, as adding the rownum predicate to the real query is OK. it's done the way it's now because that's easier (e.g. when the 'where' clause isn't the last line in the query).

I can not contact our DBA for a few days, but I'll ask him as soon as he comes back from vacation.

Thanks.

No worries, we'll try to change the DQE so the rownum filter is inside the scalar so it will work regardless of what setting is used. I hope to have a fixed DQE for you today or tomorrow.

There's a problem:

SELECT DISTINCT COUNT(*) AS "LPAV_" FROM (SELECT "LPLA_1"."EMPNO" AS "Empno" FROM "SCOTT"."EMP" "LPLA_1" WHERE ( ( ( "LPLA_1"."JOB" = :Job1)))) "LPA_L1" WHERE rownum <= 1

:Job1 is 'MANAGER'

this gives '1', but when the where clause is removed (on the rownum) it's '3', the correct answer.

The query above is generated from:

[Test]
public void CountAggregateOnEmployee()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var amount = (from e in metaData.Emp
                      where e.Job == "MANAGER"
                      select e.Empno).Count();

        Assert.AreEqual(3, amount);
    }
}

which behind the scenes limits the results to 1. This results in the rownum filter which should be applied AFTER the count has been ran, but is now applied to the data the count is applied on, which is not what is intended.

The limit is placed in the scalar query even though the aggregate is present, because it doesn't know the aggregate is present, it simply knows it's a scalar query, so it needs to be 1 value, hence the limit.

I.o.w.: the intended fix to move the rownum into the where of the query to limit isn't going to work in all cases, and it's unknown which cases those are, so it's also not testable.

We'll setup 11g dev in a VM tomorrow to see if we can reproduce the behavior you're seeing. I still find it very odd your oracle install doesn't recognize aliases defined in the outer query however it might be an option (not sure) how deep outer aliases are known (e.g. 1 level deep) which makes it unknown to nested queries more than 1 level deep.

From the 10g manual about Using subqueries: (emphasis mine)

Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery. The parent statement can be a SELECT, UPDATE, or DELETE statement in which the subquery is nested. A correlated subquery is evaluated once for each row processed by the parent statement. Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.

I did some googling and it seems some of the people have the same problem even with 10.2g release, but found no solution. It seems it might be related to some DB settings.

http://forums.oracle.com/forums/thread.jspa?threadID=378604 http://forums.oracle.com/forums/thread.jspa?messageID=3403285&#3403285

arash wrote:

I did some googling and it seems some of the people have the same problem even with 10.2g release, but found no solution. It seems it might be related to some DB settings.

http://forums.oracle.com/forums/thread.jspa?threadID=378604 http://forums.oracle.com/forums/thread.jspa?messageID=3403285&#3403285

Thanks for looking into this. We're running 10.2 here (default install, no settings changed) so at least it's not something that's simply not supported by oracle, however it also illustrates that it indeed must be something setting related.

I thought of a workaround for you btw, which uses a join instead of a scalar subquery, so it should be faster also. The trick is to traverse the m:1 relationship between product and category:

var q = from p in metaData.Products select new ProductsEntity()
        {
            ProductId = p.ProductId,
            ProductName = p.ProductName,
            CategoryName = p.Category.CategoryName
        };
var list = q.ToList();

This should work without a problem on your db as well. I still would like to know if your DBA knows what the problem could be, which setting it might be, or that it might be a bug in Oracle.

Yes, in this sample it's possible with joins. My problem is that I intended to run a more sophisticated sub query but after getting this error I simplified the query to find out the reason for this problem. But it seems the problem is related to Oracle. After you mentioned about 10g documents I looked into 11g documents about correlated queries. In 11g documents is also commented that more than 2 levels of sub query is supported. So I'll try to figure this out in Oracle and I'll let you know as soon as I can find a solution.

Thanks.

Ok, please let us know what you find. If it helps: the rownum wrapping is caused by 'First()' as you might have guessed. If you can rework your query so it doesn't do a row limit, it might overcome the problem you ran into as well. I also think that in many cases it is possible to write the query as a query using joins and not a scalar query. But perhaps not in your case.

I'll mark the thread as done for now. You can simply post into this thread again and it will re-open automatically.

Hi, Long time from the last post, but we still have the nesting limit problem on correlated queries. Sometimes we really need to use correlated queries instead of joins and we have found no solution for the error on Oracle 11g. Please read the following on Oracle site:

"http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1853075500346799932"

and the same one on stackoverflow:

http://stackoverflow.com/questions/1233910/is-there-a-nesting-limit-for-correlated-subqueries-in-oracle

Thank you in advance. Arash.

Hmm. the nesting limit comes from the First() I think, but I don't see how to reduce that, as retrieving the first from a set takes a rownum using subquery.

If you do: var q = from p in metaData.Products select new ProductsEntity() let categoryName = (from c in metaData.Categories where c.CategoryId == p.CategoryId select c).First().CategoryName { ProductId = p.ProductId, ProductName = p.ProductName, CategoryName = categoryName }; var list = q.ToList();

it produces the same result?

The thing is that you need to obtain the first value in a set which is unordered, so IMHO not really helpful, as any value could be returned for CategoryName, unless the filter produces 1 value already, which is the case when you can use a join as well (the property navigation I mentioned earlier).

Could you give an example of a query which doesn't work and where you can't use a join? (please describe the relations used in the query)

Hi, Our model contains lots of entities and it requires lots of table to be created. So I I'll try to make an example with a more familiar table structure and I will post it here later so you can reproduce it more easily.

Meanwhile, when I was testing some nested queries, I encountered another problem regarding wrong alias names, so I reproduced it with Northwind table structure. For example I want to get the list of some orders including the number of discontinued and the number of not discontinued products for each order(It may seems silly but it reproduces the error). The linq query is:

var q = from o in metaData.Orders join cust in metaData.Customers on o.Customerid equals cust.Customerid
        where o.Orderid == 10261
        select new OrdersEntity()
        {
            Orderid = o.Orderid,
            Orderdate = o.Orderdate,
            CountOfDiscontinuedProducts = Convert.ToInt32(o.Orderdetails.Count(od => od.Products.Discontinued == 1)),
            CountOfNotDiscontinuedProducts = Convert.ToInt32(o.Orderdetails.Count(od => od.Products.Discontinued == 0)),
        };


        var list = q.ToList();

the generated SQL query (in Oracle) is:

SELECT "LPA_L1"."ORDERID" AS "Orderid", "LPA_L1"."ORDERDATE" AS "Orderdate", CAST((SELECT COUNT(*) AS "LPAV_" FROM ( "BIMEHAPP"."PRODUCTS" "LPA_L4"  INNER JOIN "BIMEHAPP"."ORDERDETAILS" "LPA_L5"  ON  "LPA_L4"."PRODUCTID"="LPA_L5"."PRODUCTID") WHERE ( ( "LPA_L1"."ORDERID" = "LPA_L5"."ORDERID") AND ( "LPA_L4"."DISCONTINUED" = :Discontinued1))) AS NUMBER(9, 0)) AS "CountOfDiscontinuedProducts", CAST((SELECT COUNT(*) AS "LPAV_" FROM ( "BIMEHAPP"."PRODUCTS" "LPA_L6"  INNER JOIN "BIMEHAPP"."ORDERDETAILS" "LPA_L7"  ON  "LPA_L6"."PRODUCTID"="LPA_L7"."PRODUCTID") WHERE ( ( "LPA_L1"."ORDERID" = "LPLA_6"."ORDERID") AND ( "LPA_L6"."DISCONTINUED" = :Discontinued2))) AS NUMBER(9, 0)) AS "F__3" FROM ( "BIMEHAPP"."ORDERS" "LPA_L1"  INNER JOIN "BIMEHAPP"."CUSTOMERS" "LPA_L2"  ON  "LPA_L1"."CUSTOMERID" = "LPA_L2"."CUSTOMERID") WHERE ( ( ( ( ( "LPA_L1"."ORDERID" = :Orderid3)))))

Parameter: :Discontinued1 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: :Discontinued2 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.
Parameter: :Orderid3 : Decimal. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 10261.

and an exception is thrown with the following message:

ORA-00904: "LPLA_6"."ORDERID": invalid identifier

If I run the query in the following conditions it works fine:

1:without where condition:

var q = from o in metaData.Orders join cust in metaData.Customers on o.Customerid equals cust.Customerid
    select new OrdersEntity()
    {
        Orderid = o.Orderid,
        Orderdate = o.Orderdate,
        CountOfDiscontinuedProducts = Convert.ToInt32(o.Orderdetails.Count(od => od.Products.Discontinued == 1)),
        CountOfNotDiscontinuedProducts = Convert.ToInt32(o.Orderdetails.Count(od => od.Products.Discontinued == 0)),
    };

2:with where condition but without join:

var q = from o in metaData.Orders
    where o.Orderid == 10261
    select new OrdersEntity()
    {
        Orderid = o.Orderid,
        Orderdate = o.Orderdate,
        CountOfDiscontinuedProducts = Convert.ToInt32(o.Orderdetails.Count(od => od.Products.Discontinued == 1)),
        CountOfNotDiscontinuedProducts = Convert.ToInt32(o.Orderdetails.Count(od => od.Products.Discontinued == 0)),
    };

3:with where condition and join but without second nested query:

var q = from o in metaData.Orders join cust in metaData.Customers on o.Customerid equals cust.Customerid
    where o.Orderid == 10261
    select new OrdersEntity()
    {
        Orderid = o.Orderid,
        Orderdate = o.Orderdate,
        CountOfDiscontinuedProducts = Convert.ToInt32(o.Orderdetails.Count(od => od.Products.Discontinued == 1))
    };

So, would you please take a look at it. Thank you in advance.

PS: I'm using the latest DLLs

The "LPLA_6"."ORDERID" reference is indeed wrong (i.o.w. looks like a bug), as it should have been: LPA_L7.ORDERID. I'll see if I can repro this in other providers as well (as it looks like an alias error, which isn't dqe specific).

I.o.w.: if you run the query which fails, in toad (or other query tool) and replace "LPLA_6" with "LPA_L7" in the second cast subquery, it should work. (just for testing). I'll see if I can reproduce this alias issue. It does sound familiar though.

(edit) alias issue reproduced. Looking into it.

About the initial issue: is this only occuring when you are using 'First()' ? So i.o.w.: when a SELECT * (SELECT ... FROM ) where rownum etc. query is issued? (so a wrapped 1 element retrieval query?) If so, the only solution to your initial problem is the removal of the wrapping query. It could be possible, though I've to do some tests. It currently wraps with: SELECT * FROM ({0}) WHERE rownum <= {1}

It should emit the where clause earlier, inside the actual where clause, not as a wrapped query. (If that's the only cause of the problem). Paging wraps the query also, however paging inside another query isn't possible, so I think the wrapping of the limit query is the one you run into.

The alias error is caused by the multiple times the usage of the same related entity. It might sound weird, but that causes the problem. The root cause is that the linq provider assigns an alias to a set property of a type. The alias assigned to order.OrderDetails in the first nested query should be different from the second, however it isn't. As the linq query just contains types, the second nested query tries to obtain the alias of the occurence in the first nested query (as it's the same thing, according to the linq provider), and gets the alias of the aggregate set as it's out of scope (LPLA_6).

Fixing this at this point is not possible due to a deep architectural change we have to make for this in the linq provider. (other situations this issue pops up is when you use the same anonymous type in different parts of the query without meaning the same thing). We will fix this in v3's RTL and will look into backporting it to v2's linq provider if it's possible (it might be a small change, but at this point it looks like changes in multiple areas in the code).

Your example query unfortunately doesn't have an easy workaround for this, and we're sorry for this inconvenience. For these queries, please use our native query system with a projection instead, so you can avoid this alias issue. It's a bit of an edge case, so it's not likely you'll run into it often (the alias error).

I'll prepare a debug build of the Oracle DQE for you with a temporary fix for the wrapping of the limit query, which should not give you the errors you reported earlier so you can test it out to see if it indeed solves your problem.

Please use the attached Oracle DQE for testing if your First() query now works properly. If you look closely at the SQL, it now won't wrap the scalar query anymore, it will embed the rownum filter directly in the query.

(remember, this is a debug/test build, and not regression tested.)

About the initial issue: is this only occuring when you are using 'First()' ? So i.o.w.: when a SELECT * (SELECT ... FROM ) where rownum etc. query is issued? (so a wrapped 1 element retrieval query?) If so, the only solution to your initial problem is the removal of the wrapping query. It could be possible, though I've to do some tests. It currently wraps with: SELECT * FROM ({0}) WHERE rownum <= {1}

Yes, it occures when using First(), FirstOrDefault(), Single() and SingleOrDefault()

The alias error is caused by the multiple times the usage of the same related entity

So why it works when I omit the where condition? It still uses the related entity twice.

Please use the attached Oracle DQE for testing if your First() query now works properly. If you look closely at the SQL, it now won't wrap the scalar query anymore, it will embed the rownum filter directly in the query. (remember, this is a debug/test build, and not regression tested.)

Yes, It works now but I have not tested to see whether it breaks some where else or not.

arash wrote:

About the initial issue: is this only occuring when you are using 'First()' ? So i.o.w.: when a SELECT * (SELECT ... FROM ) where rownum etc. query is issued? (so a wrapped 1 element retrieval query?) If so, the only solution to your initial problem is the removal of the wrapping query. It could be possible, though I've to do some tests. It currently wraps with: SELECT * FROM ({0}) WHERE rownum <= {1}

Yes, it occures when using First(), FirstOrDefault(), Single() and SingleOrDefault()

then the fix I posted yesterday will work properly.

The alias error is caused by the multiple times the usage of the same related entity

So why it works when I omit the where condition? It still uses the related entity twice.

Because the 'order' in the subqueries is a property of an anonymous type Linq queries are sequenced calls to extension methods, where each method works on the result of the previous method. It's a little complicated to explain but in the case where there's no 'where' clause nor a 'join', the subqueries work on the same element, but that's the direct element from the meta-data. They'll likely assign the same alias to it in the subquery. The case where a 'where' or 'join' (or orderby or groupby etc.) is present, they work on a property of an anonymous type, which represents the REAL element they work on (i.e. the source of the method producing the anonymous type, e.g. the source of where or join). This causes the problem with the alias, as it has to create a correlation relation with the outer query which goes wrong.

What should be done is that member aliases aren't global, but are per scope. This is already implement to some extend, (it is aware of scopes, but instead of storing multiple aliases per member (one per scope the member is used in), it stores 1 alias and can determine if the alias is reachable or not: inside a subquery isn't reachable for outside elements, so it has to use the outer scope's alias. It gets ugly very quickly if you consider that when you use a 'join', you'll get a new type with two properties: one is the left side, the other is the right side. If you then use a 'where' on an element in the join, you actually refer to one of the properties on the type which was the result of the join. The linq provider then has to track back to the real source of that property as the where is in the same scope as the join. This linking is done through aliases, but can go wrong in some edge cases, like the one you ran into.

Same happens when you use the same reference to the same property in two let statements for example: let val1 = x.SomeCollection.First().FirstName let val2 = x.SomeCollection.First().LastName

this goes wrong for val2 with the same problem.

Please use the attached Oracle DQE for testing if your First() query now works properly. If you look closely at the SQL, it now won't wrap the scalar query anymore, it will embed the rownum filter directly in the query. (remember, this is a debug/test build, and not regression tested.)

Yes, It works now but I have not tested to see whether it breaks some where else or not.

Please post back in this thread if you can confirm that other queries which previously failed now work, so I can merge the fix into the main code base.