Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Why does this query fail.
 

Pages: 1
LLBLGen Pro Runtime Framework
Why does this query fail.
Page:1/1 

  Print all messages in this thread  
Poster Message
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 12-Nov-2008 21:57:16.  
I have a linq to LLBLGen query (shown below) that fails. The first two succeede however the third fails. Could you tell me why? (software version 2.6.08.0911).

CiscoAlert has 0:1 CiscoAlertTemplate and has 0:n CiscoAlertCVE.



Code:

//WORKS
            try
            {
                var test = (from c in base.LinqContext.CiscoAlert
                            where c.CiscoAlertTemplate.Name == "Vulnerability Alert"
                            select new
                            {
                                c.CiscoAlertId,
                            }).ToArray();
            }
            catch (Exception ex)
            {
                Console.WriteLine("1 Failed");
            }

// WORKS
            try
            {
                var test = (from c in base.LinqContext.CiscoAlert
                            
                            select new
                            {
                                c.CiscoAlertId,
                                CVEs = c.CiscoAlertCVE
                            }).ToArray();
            }
            catch (Exception ex)
            {
                Console.WriteLine("2 Failed");
            }

// THIS ONE FAILS
            try
            {
                var test = (from c in base.LinqContext.CiscoAlert
                            where c.CiscoAlertTemplate.Name == "Vulnerability Alert"
                            select new
                            {
                                c.CiscoAlertId,
                                CVEs = c.CiscoAlertCVE
                            }).ToArray();
            }
            catch (Exception ex)
            {
                Console.WriteLine("3 Failed");
            }




The third one generates this SQL

Code:

exec sp_executesql N'SELECT [LPA_L2].[CiscoAlertId], @LO11 AS [LPFA_2] FROM ( [VRRA].[Vulnerability].[CiscoAlertTemplate] [LPA_L1] INNER JOIN [VRRA].[Vulnerability].[CiscoAlert] [LPA_L2] ON [LPA_L1].[CiscoAlertTemplateId]=[LPA_L2].[CiscoAlertTemplateId]) WHERE ( ( ( ( ( [LPA_L1].[Name] = @Name2)))))',N'@LO11 int,@Name2 varchar(50)',@LO11=1,@Name2='Vulnerability Alert'


exec sp_executesql N'SELECT [LPLA_3].[CiscoAlertId], [LPLA_3].[CVENumber] FROM [VRRA].[Vulnerability].[CiscoAlertCVE] [LPLA_3] WHERE ( ( ( ( EXISTS (SELECT [LPLA_1].[CiscoAlertId] FROM [VRRA].[Vulnerability].[CiscoAlert] [LPLA_1] WHERE ( ( ( ( ( [LPLA_2].[Name] = @Name1)))) AND [LPLA_1].[CiscoAlertId] = [LPLA_3].[CiscoAlertId]))))))',N'@Name1 varchar(50)',@Name1='Vulnerability Alert'

Brandt  Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 12-Nov-2008 23:46:23.  
Searching through the forum i found this entry (http://llblgen.com/TinyForum/Messages.aspx?ThreadID=14157) where BringerOD said

Quote:


It starts to break when you set criteria for a joined object.



which seems to be happening here. For further information.


Otis -> question - are you using a sample database for your unit tests like northwind?


Brandt  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8150 posts
# Posted on: 13-Nov-2008 05:07:49.  
HI Brandt,

- Please update to the latest build. Some changes had been made on linq stuff.
- If the problem persists, post the exception details.

Quote:
Otis -> question - are you using a sample database for your unit tests like northwind?

While I'm not Otis Regular Smiley, at least the linq unit tests are written using Northwind and AdventureWorks databases.
David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 13-Nov-2008 07:17:13.  
daelmo wrote:
HI Brandt,

- Please update to the latest build. Some changes had been made on linq stuff.
- If the problem persists, post the exception details.



Thanks,
I updated to the newest version and its still happening. The query information above is almost exactly identical. The first query is formulated correctly. The second query isn't.


The exception message is:

An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_2.Name" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.


The stack trace is :

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteHierarchicalValueListProjection(QueryExpression toExecute, IRelationPredicateBucket additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
at VRRA.Data.Repositories.CiscoAlertRepository.GetCiscoAlerts() in C:\Users\hbb9745\Documents\Visual Studio 2008\Projects\VRRA\Source\VRRA.Data\Repositories\CiscoAlertRepository.cs:line 49


Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 13-Nov-2008 10:10:32.  
Will look into it. The queries look pretty simple.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 13-Nov-2008 12:11:08.  
Can't reproduce it:
Code:

[Test]
public void NestedQueryWithFilterOnRelatedEntity()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from p in metaData.Product
                where p.Category.CategoryName == "Beverages"
                select new
                {
                    p.ProductId,
                    OrderDetails = p.OrderDetails
                };

        int count = 0;
        int totalNumberOfOrderDetails = 0;
        foreach(var v in q)
        {
            count++;
            totalNumberOfOrderDetails += v.OrderDetails.Count;
        }
        Assert.AreEqual(11, count);
        Assert.AreEqual(316, totalNumberOfOrderDetails);
    }
}

Works as expected. This is with the latest internal build. I'll attach this build to this post. We have 1 issue to go before this release can be rolled into the next build on the website, so use the attached dll for testing.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 13-Nov-2008 15:09:46.  
Otis wrote:
Can't reproduce it:

****

Works as expected. This is with the latest internal build. I'll attach this build to this post. We have 1 issue to go before this release can be rolled into the next build on the website, so use the attached dll for testing.


It still doesn't work. I even tried it on different tables with the same type of relationships. Therefore, I will double check and tripple check everything. If your tests are passing then the cause should be on my side.

Thanks,
Brandt
Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 13-Nov-2008 15:56:01.  
Brandt wrote:
Otis wrote:
Can't reproduce it:

****

Works as expected. This is with the latest internal build. I'll attach this build to this post. We have 1 issue to go before this release can be rolled into the next build on the website, so use the attached dll for testing.


It still doesn't work. I even tried it on different tables with the same type of relationships. Therefore, I will double check and tripple check everything. If your tests are passing then the cause should be on my side.

A join is missing in the second query. I think it might be that the ORMSupportClasses is outdated. I'll attach to this post the latest internal build of that as well, as it might be that the cause of the missing join is inside that dll.

(edit) attached.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 13-Nov-2008 18:11:53.  
Ok, I created a new project based off of the AdventureWorks 2008 Refresh 1 database, Generated the code and created a test project. I referenced the new dlls.

ORMSupportClasses.NET20.dll 2.6.08.1107
LinqSupportClasses.NET35.dll    2.6.08.1113

Then created a test based off the PersonEntity. I still get an exception. T

Test method AdventureWorks.Tests.UnitTest1.NestedQueryWithFilterOnRelatedEntity threw exception: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_2.CurrentFlag" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: The multi-part identifier "LPLA_2.CurrentFlag" could not be bound..


Quote:

        [TestMethod]
        public void NestedQueryWithFilterOnRelatedEntity()
        {
            AdventureWorks.DbModel.Linq.LinqMetaData context = new AdventureWorks.DbModel.Linq.LinqMetaData(_adapter);

            var qry = from a in context.Person
                     where a.Employee.CurrentFlag == true
                     select new
                     {
                         a.FirstName,
                         CreditCards = a.PersonCreditCard
                     };
            var l = qry.ToList();
            Assert.IsNotNull(l);
        }



Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 14-Nov-2008 13:34:00.  
Our unit-tests are on sqlserver 2005/2000 so I'll go for Adventureworks normal. I've reworked the query so it's likely also in your db.

After re-generating the code, latest builds of everything, the following query failed:
Code:

[Test]
public void NestedQueryWithWhereFilter()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from soh in metaData.SalesOrderHeader
                where soh.Contact.NameStyle == false
                select new
                {
                    soh.SalesOrderNumber,
                    SalesOrderDetails = soh.SalesOrderDetailCollection
                };

        foreach(var v in q)
        {
        }
    }
}


but the earlier query I posted still works... Confused

I did find another issue (not related) which caused nested queries to be executed even if there were 0 parent rows. This has been corrected. I'll now look into why this query fails but the northwind query works.

(edit) I see what's causing one query to work and the other to fail: the earlier test query didn't use a subquery approach but a parameterized subquery.

when I use:
Code:

[Test]
public void NestedQueryWithFilterOnRelatedEntity()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        adapter.ParameterisedPrefetchPathThreshold = 5;
        var q = from p in metaData.Product
                where p.Category.CategoryName == "Beverages"
                select new
                {
                    p.ProductName,
                    OrderDetails = p.OrderDetails
                };

        int count = 0;
        int totalNumberOfOrderDetails = 0;
        foreach(var v in q)
        {
            count++;
            totalNumberOfOrderDetails += v.OrderDetails.Count;
        }
        Assert.AreEqual(11, count);
        Assert.AreEqual(316, totalNumberOfOrderDetails);
    }
}

It also fails.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 14-Nov-2008 13:46:19.  
Must have been monday morning 8 o clock sharp when I wrote this Embarrassed
Code:

if(!currentLevelRelations.IsEmpty())
{
    usableCurrentLevelRelations.AddRange(usableCurrentLevelRelations);
}


Fix is attached.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Brandt
User



Location:

Joined on:
04-Apr-2007 17:43:03
Posted:
142 posts
# Posted on: 15-Nov-2008 18:44:32.  
Otis wrote:
Must have been monday morning 8 o clock sharp when I wrote this Embarrassed
Code:

if(!currentLevelRelations.IsEmpty())
{
    usableCurrentLevelRelations.AddRange(usableCurrentLevelRelations);
}


Fix is attached.



Perfect it worked for that case. Now I would like introduce a case where is again fails..    (sorry i haven't been able to install the older test databases so i can produce exact tests yet). This one involves projections. The first and the third work but the second fails. The exception returned is at the bottom.


Code:

        /// <summary>
        /// This one works - the filter is defined with in the first expression
        /// </summary>
        [TestMethod]
        public void NestedQueryWithFilterOnRelatedEntityWithProjection1()
        {
            AdventureWorks.DbModel.Linq.LinqMetaData context = new AdventureWorks.DbModel.Linq.LinqMetaData(_adapter);

            var qry = from a in context.Person
                     where a.Employee.CurrentFlag == true
                     select new
                     {
                         a.FirstName,
                         Employee = new { a.Employee.CurrentFlag, a.Employee.SalariedFlag },
                         CreditCards = a.PersonCreditCard.Select(c=> new { c.CreditCardID, c.BusinessEntityID })
                     };
            var l = qry.ToList();
            Assert.IsNotNull(l);
        }



        /// <summary>
        /// This one fails - The filter is added after the first linq expression
        /// </summary>
        [TestMethod]
        public void NestedQueryWithFilterOnRelatedEntityWithProjection2()
        {
            AdventureWorks.DbModel.Linq.LinqMetaData context = new AdventureWorks.DbModel.Linq.LinqMetaData(_adapter);

            var qry = from a in context.Person
                     select new
                     {
                         a.FirstName,
                         Employee = new { a.Employee.CurrentFlag, a.Employee.SalariedFlag },
                         CreditCards = a.PersonCreditCard.Select(c => new { c.CreditCardID, c.ModifiedDate })
                     };

            qry = qry.Where(a => a.Employee.CurrentFlag == true);
            var l = qry.ToList();
            Assert.IsNotNull(l);
        }


        /// <summary>
        /// This one works - The filter is added after the first expression however the CreditCards removed from the result.
        /// </summary>
        [TestMethod]
        public void NestedQueryWithFilterOnRelatedEntityWithProjection3()
        {
            AdventureWorks.DbModel.Linq.LinqMetaData context = new AdventureWorks.DbModel.Linq.LinqMetaData(_adapter);

            var qry = from a in context.Person
                     select new
                     {
                         a.FirstName,
                         Employee = new { a.Employee.CurrentFlag, a.Employee.SalariedFlag },
                     };

            qry = qry.Where(a => a.Employee.CurrentFlag == true);
            var l = qry.ToList();
            Assert.IsNotNull(l);
        }




Exception Details :

Test method AdventureWorks.Tests.UnitTest1.NestedQueryWithFilterOnRelatedEntityWithProjection2 threw exception: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_1.BusinessEntityID" could not be bound.
The multi-part identifier "LPLA_1.BusinessEntityID" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: The multi-part identifier "LPLA_1.BusinessEntityID" could not be bound.
The multi-part identifier "LPLA_1.BusinessEntityID" could not be bound..


Brandt  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 15-Nov-2008 20:54:48.  
(Haven't tried it, but I guess it's this -> )
This is indeed something which isn't obvious. The problem is that the second query has a 'nested' query (the creditcards), but the additional where clause wraps the whole query (so it becomes something like ('*' to make things easier for reading)
select * from ( ... the query) where ...

though the nested query then isn't executed / executable, as the nested query is done in hte outer projection, which is now wrapped...

It's not really fixable, as the nested queries are executed in batches, not 'on the fly' as in the MS frameworks. Batch execution (like with prefetch paths) is much faster and efficient, but has the downside that the query has to be executable in the first place, or better: the mechanism has to be usable.

With prefetch paths (the .WithPath routine) it's doable, because these are bubbled upwards by a special visitor. The nested queries in a projection however aren't, as that's not doable, because the projection with the nested query takes place INSIDE a wrapped query.

Again, I haven't tested it yet, but I guess this is the reason. If you switch on tracing, you'll likely see a wrapped select in the second case, correct?
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 17-Nov-2008 10:34:03.  
It's likely caused by the wrapping, but looking at the queries, it's the second query (the nested query for creditcards in your case) which fails, not the first one. The aliases are wrong.

Looking into it.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 17-Nov-2008 11:35:00.  
Got it. Was difficult to track down, as the fix was easy, but a subtle bug popped up (re-use of a cloned entity field in projection AND correlation filter made it go wrong, so alias changes in filters due to wrappings also changed projection fields. )

Anyway, the attached dll should fix the issue. Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.