Hi,

I've just come across what seems to be a bug when trying to project results out of a query that uses a join on a filtered data source - when that join is on a column that has some specific naming conditions. Crikey

Let me try and explain:

I've got an almost vanilla instance of the Northwind database. The only changes I've made are to rename the [OrderId] fields in the [Orders] and [Order Details] tables to be [Order_Id].

I've then built a default LLBLGen project over the Northwind database - i.e. import the entire catalog and create an entity for each table.

If I then run the following query:

var list1 = (from o in metadata.Order where o.EmployeeId == 4 join od in metadata.OrderDetail on o.OrderId equals od.OrderId where od.ProductId == 57 select od).ToList();

it works fine and gives me 8 results. However, if I try and project the results to an anonymous type, I get an error. The projected query is:

var list2 = (from o in metadata.Order where o.EmployeeId == 4 join od in metadata.OrderDetail on o.OrderId equals od.OrderId where od.ProductId == 57 select new { od.ProductId }).ToList();

and the error is:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Invalid column name 'Order_ID'

Now, the trace log for that latter query shows:

Generated Sql query: 
    Query: SELECT [LPA_L2].[ProductID] AS [ProductId] FROM ( (SELECT [LPLA_1].[Order_ID] AS [OrderId], [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_1].[EmployeeID] AS [EmployeeId], [LPLA_1].[OrderDate], [LPLA_1].[RequiredDate], [LPLA_1].[ShippedDate], [LPLA_1].[ShipVia], [LPLA_1].[Freight], [LPLA_1].[ShipName], [LPLA_1].[ShipAddress], [LPLA_1].[ShipCity], [LPLA_1].[ShipRegion], [LPLA_1].[ShipPostalCode], [LPLA_1].[ShipCountry] FROM [Northwind].[dbo].[Orders] [LPLA_1]  WHERE ( ( [LPLA_1].[EmployeeID] = @EmployeeId1))) [LPA_L1]  INNER JOIN [Northwind].[dbo].[Order Details] [LPA_L2]  ON  [LPA_L1].[Order_ID] = [LPA_L2].[Order_ID]) WHERE ( ( ( ( ( [LPA_L2].[ProductID] = @ProductId2)))))
    Parameter: @EmployeeId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 4.
    Parameter: @ProductId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 57.

If you look closely, the problem is that [LPLA_1].[Order_ID] gets aliased to [OrderId] but the inner join is trying to join on [LPLA_1].[Order_ID] still...

My guess is that this will only happen if the column names within the entity model are mapped - i.e. order_id is mapped as property OrderId and so on.

Cheers Ian

P.S. I've not attached any sample project etc. as it's all based on Northwind and should be easy enough to replicate. I will do, though, if it helps. Also, for clarity I've only tested this on the 10th April build of the beta.

Will check it out

I've seen this issue before, there's some code to figure out the right names, it's not used in this scenario (derived table as a join side) so I'll make sure the code will properly figure out the name of the field to use.

(it's similar to: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=12824 only that routine was easy to fix but the elements in your query where it goes wrong aren't covered by this routine)

(edit). It's due to the fact that the entity fetch routine uses a newer piece of code to preprocess query elements which hasn't been refactored to the projection/dyn. list using routines yet. This is scheduled to be done today or tomorrow (so the code is already there, the current fetch routines just have to use the newer code). When that happens, they'll automatically use the proper aliases and not the field names as was the case when fetching an entity.

Ok, after throwing away a lot of code and using just 2 new routines (which are now used by all query creation routines in adapter and selfservicing) your query works now properly (the rest of the tests do too pfew)

So fixed in next build.

Thanks. Look forward to the next release!