Why does this query fail.
Joined: 04-Apr-2007
Joined: 04-Apr-2007
HI Brandt,
- Please update to the latest build. Some changes had been made on linq stuff.
- If the problem persists, post the exception details.
Otis -> question - are you using a sample database for your unit tests like northwind?
While I'm not Otis 
, at least the linq unit tests are written using Northwind and AdventureWorks databases.
Joined: 04-Apr-2007
Joined: 04-Apr-2007
Joined: 04-Apr-2007
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:
[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... 
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:
[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.
Must have been monday morning 8 o clock sharp when I wrote this 
if(!currentLevelRelations.IsEmpty())
{
usableCurrentLevelRelations.AddRange(usableCurrentLevelRelations);
}
Fix is attached.
Joined: 04-Apr-2007
Otis wrote:
Must have been monday morning 8 o clock sharp when I wrote this
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.
/// <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..
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.