Ok. I think I reproduced your situation. I tested before but it was slightly different.
Here are my results (with AdventureWorks):
LLBLGen RTL
3.1.11.318
TypedList
(attached the .llblgenproj file)
Its a SalesOrderHeader related to SalesPerson (subtype of Employee). The relation is m:1 and SalesPersonId is Optional.
Test1 (indicate Left join in the Designer, ObeyWeakRelation=false (default) in code):
[TestMethod]
public void CheckSalesOrderTypedListWithNullSalesPerson()
{
// the SalesPersonId is null for this SalesOrder
IRelationPredicateBucket filter = new RelationPredicateBucket(SalesOrderHeaderFields.SalesOrderId == 43697);
// fetch the TypedList
SalesOrderHeaderAndSalesPersonTypedList ordersWithSalesPersonTpl = new SalesOrderHeaderAndSalesPersonTypedList();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(ordersWithSalesPersonTpl, filter.PredicateExpression);
}
// test: as a LEFT JOIN is emmited, we expect the order fetched
// FAILS
Assert.AreEqual(1, ordersWithSalesPersonTpl.Count);
// test: the SalesPersonId should be null
Assert.AreEqual(null, ordersWithSalesPersonTpl[0].SalesPersonId);
}
Generated SQL:
SELECT
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] AS [SalesOrderId],
[AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate],
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber],
[LPA_L1].[BirthDate],
[LPA_L2].[Bonus],
[LPA_L2].[SalesPersonID] AS [EmployeeId],
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID] AS [SalesPersonId]
FROM
(
( [AdventureWorks].[HumanResources].[Employee] [LPA_L1]
INNER JOIN [AdventureWorks].[Sales].[SalesPerson] [LPA_L2]
ON [LPA_L1].[EmployeeID]=[LPA_L2].[SalesPersonID]
)
RIGHT JOIN [AdventureWorks].[Sales].[SalesOrderHeader]
ON [LPA_L2].[SalesPersonID]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID]
)
WHERE
(
(
( [AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] = 43697)
)
AND
(
( [LPA_L2].[SalesPersonID] IS NOT NULL)
)
)
Test2 (indicate Inner join in the Designer, ObeyWeakRelation=true (default) in code):
The code is the same as above, the difference is I set_ ObeyWeakRelation=true_
Generated SQL:
SELECT
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] AS [SalesOrderId],
[AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate],
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber],
[LPA_L1].[BirthDate],
[LPA_L2].[Bonus],
[LPA_L2].[SalesPersonID] AS [EmployeeId],
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID] AS [SalesPersonId]
FROM
(
( [AdventureWorks].[HumanResources].[Employee] [LPA_L1]
LEFT JOIN [AdventureWorks].[Sales].[SalesPerson] [LPA_L2]
ON [LPA_L1].[EmployeeID]=[LPA_L2].[SalesPersonID]
)
INNER JOIN [AdventureWorks].[Sales].[SalesOrderHeader]
ON [LPA_L2].[SalesPersonID]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID]
)
WHERE
(
(
( [AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] = 43697)
)
AND
(
( [LPA_L2].[SalesPersonID] IS NOT NULL)
)
)
**Test3 **(indicate Left join in the Designer, ObeyWeakRelation=true (default) in code):
code is the same as above, the relation typedlist in the designer is LEFT.
Generated SQL:
SELECT
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] AS [SalesOrderId],
[AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate],
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber],
[LPA_L1].[BirthDate], [LPA_L2].[Bonus],
[LPA_L2].[SalesPersonID] AS [EmployeeId],
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID] AS [SalesPersonId]
FROM
(
( [AdventureWorks].[HumanResources].[Employee] [LPA_L1]
LEFT JOIN [AdventureWorks].[Sales].[SalesPerson] [LPA_L2]
ON [LPA_L1].[EmployeeID]=[LPA_L2].[SalesPersonID]
)
RIGHT JOIN [AdventureWorks].[Sales].[SalesOrderHeader]
ON [LPA_L2].[SalesPersonID]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID]
)
WHERE
(
(
( [AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] = 43697)
)
AND
(
( [LPA_L2].[SalesPersonID] IS NOT NULL)
)
)
**Test4 **(test this with DynamicList)
[TestMethod]
public void CheckSalesOrderDynamicListWithNullSalesPerson()
{
// setyp the fields
ResultsetFields fields = new ResultsetFields(7);
fields.DefineField(SalesOrderHeaderFields.SalesOrderId, 0);
fields.DefineField(SalesOrderHeaderFields.OrderDate, 1);
fields.DefineField(SalesOrderHeaderFields.SalesOrderNumber, 2);
fields.DefineField(SalesOrderHeaderFields.SalesPersonId, 3);
fields.DefineField(SalesPersonFields.BirthDate, 4);
fields.DefineField(SalesPersonFields.Bonus, 5);
fields.DefineField(SalesPersonFields.EmployeeId, 6);
// the SalesPersonId is null for this SalesOrder
IRelationPredicateBucket filter = new RelationPredicateBucket(SalesOrderHeaderFields.SalesOrderId == 43697);
filter.Relations.Add(SalesOrderHeaderEntity.Relations.SalesPersonEntityUsingSalesPersonId);
// fetch the DynamicList, obey weak relations to force a LEFT JOIN
filter.Relations.ObeyWeakRelations = true;
DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(fields, results, filter);
}
// test: as a LEFT JOIN is emmited, we expect the order fetched
Assert.AreEqual(1, results.Rows.Count);
// test: the SalesPersonId should be null
Assert.AreEqual(null, results.Rows[0]["SalesPersonId"]);
}
Generated SQL:
SELECT
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] AS [SalesOrderId],
[AdventureWorks].[Sales].[SalesOrderHeader].[OrderDate],
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderNumber],
[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID] AS [SalesPersonId],
[LPA_L1].[BirthDate], [LPA_L2].[Bonus],
[LPA_L2].[SalesPersonID] AS [EmployeeId]
FROM
(
( [AdventureWorks].[Sales].[SalesPerson] [LPA_L2]
RIGHT JOIN [AdventureWorks].[Sales].[SalesOrderHeader]
ON [LPA_L2].[SalesPersonID]=[AdventureWorks].[Sales].[SalesOrderHeader].[SalesPersonID]
)
RIGHT JOIN [AdventureWorks].[HumanResources].[Employee] [LPA_L1]
ON [LPA_L1].[EmployeeID]=[LPA_L2].[SalesPersonID]
)
WHERE
(
( [AdventureWorks].[Sales].[SalesOrderHeader].[SalesOrderID] = 43697)
AND
(
( [LPA_L2].[SalesPersonID] IS NOT NULL)
)
)
In all cases, the fist Assert fails. I admit this is not the apparently expected results, we will see into this.
