SQL syntax error in because of a bad alias

Oct 6, 2011 at 9:34 PM

I am running additional tests against the Northwind database with the test mappings in Northwind.cs

When I run multiple projections over a one to many mapping, i get a syntax error in my SQL because of a bad alias.  Here's what I'm running:

TestQuery(db.Customers.Select(x => new
{
    id = x.CustomerID,
    name = x.CompanyName,
    order = x.Orders.Select(y => new { y.OrderID, OrderDetail = new { id = y.Employee.EmployeeID } }),
}));


Here's the test response:

Query failed to execute:
SELECT t1.[CustomerID], t1.[OrderID]
FROM [Customers] AS t0
LEFT OUTER JOIN [Orders] AS t1
  ON (t1.[CustomerID] = t0.[CustomerID])

SELECT t0.[EmployeeID]
FROM [Employees] AS t0
WHERE (t0.[EmployeeID] = A35366557?.[EmployeeID])

SELECT t0.[CustomerID], t0.[CompanyName]
FROM [Customers] AS t0

Test 19: TestE - FAILED
Reason: Incorrect syntax near '?'.

Feb 13, 2012 at 2:00 PM
Edited Feb 14, 2012 at 1:43 PM

I'm also having problems with bad aliases.

It seems that when I refer to a child object's field in the Where part of the query the SubqueryRemover does things it shouldn't. This is my query

 

var data = from delivery in core.MpNetworkLinks
           where delivery.MeteringPoint.Code == code
           select delivery;

I have set the policy to include the referred member. The problem is that CrossApplyRewriter creates a query like this

 

SELECT t0.LID, ... t1.LOBJID, ...
FROM G_NETWORK_LINK AS t0
LEFT OUTER JOIN (
  SELECT t2.LOBJID
  FROM G_METERING_POINT AS t2
  ) AS t1
  ON (t1.LOBJID = t0.LMETERINGPOINTID)
WHERE ((
  SELECT t2.SCODE
  FROM G_METERING_POINT AS t2
  WHERE (t2.LOBJID = t0.LMETERINGPOINTID)) = ?Parameter?())

 

and then SubqueryRemover replaces the sub query named t1 with the table G_METERING_POINT but it doesn't replace the all aliases in the column declarations that refer to t1, only some. Any ideas how to solve this in a clean fashion?

Feb 28, 2012 at 9:01 AM

Managed to solve my problem. The file ExecutionBuilder.cs has class Scope and it has method TryGetValue. It should check the available scopes for the column starting from the inner most. But there's a flaw:

if (column.Alias == s.Alias && this.nameMap.TryGetValue(column.Name, out ordinal))

should be 

if (column.Alias == s.Alias && s.nameMap.TryGetValue(column.Name, out ordinal))

so instead of staying in the same scope, we move to the nameMap of the outer scope when necessary.