I have a table with more than one FK to the same table. I'd like to create a TypedList joining that table twice, once on each key.

As far as I can tell there is no way to do this in LLBLGen, and I need to resort to a TypedView. Is this correct?

True.

This limitation will be addressed shortly in an update to the runtime libs, however the designer will have this ability within a few months.

I have the same issue.

Asset - AssetID (PK) - <other fields>

Relationship - AssetID1 (PK) (FK1) - AssetID2 (PK) (FK2) - <other fields>

I need to be able to retrieve all assets that are related to a particular asset. Whether they are referenced via AssetID1 or AssetID2.

What would that view look like?

JohnRLewis wrote:

I have the same issue.

Asset - AssetID (PK) - <other fields>

Relationship - AssetID1 (PK) (FK1) - AssetID2 (PK) (FK2) - <other fields> I need to be able to retrieve all assets that are related to a particular asset. Whether they are referenced via AssetID1 or AssetID2. What would that view look like?

At the moment your only option is a view. The updates which make this possible are currently in beta and are expected within a few weeks as final. Your question is what the sql looks like for the view?

How do I do this using the beta? I do not see the ability to add the table twice.

JohnRLewis wrote:

How do I do this using the beta? I do not see the ability to add the table twice.

Yes you can do it in code:

/// <summary>
/// Tests the group by clause functionality.
/// </summary>
[Test]
public void GroupByTestCustomList()
{
    DataAccessAdapter adapter = new DataAccessAdapter();
    ResultsetFields fields = new ResultsetFields(3);

    fields.DefineField(EmployeeFieldIndex.FirstName, 0, "FirstNameManager", "Manager");
    fields.DefineField(EmployeeFieldIndex.LastName, 1, "LastNameManager", "Manager");
    fields.DefineField(EmployeeFieldIndex.LastName, 2, "AmountEmployees", "Employee", AggregateFunction.Count);
    IRelationPredicateBucket filter = new RelationPredicateBucket();
    filter.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeId, "Employee", "Manager", RelationWeaknessHint.None);
    filter.PredicateExpression.Add(PredicateFactory.CompareValue(EmployeeFieldIndex.LastName, ComparisonOperator.Equal, "Manager", "Buchanan"));
    IGroupByCollection groupByClause = new GroupByCollection();
    groupByClause.Add(fields[0]);
    groupByClause.Add(fields[1]);

    try
    {
        DataTable tlist = new DataTable();
        adapter.FetchTypedList(fields, tlist, filter, 0, null, true, groupByClause);

        Assert.AreEqual(1, tlist.Rows.Count);
        Assert.AreEqual(3, (int)tlist.Rows[0]["AmountEmployees"]);
    }
    finally
    {
        adapter.Dispose();
    }
}

This joins employees twice.

Selfservicing:

/// <summary>
/// Tests the group by clause functionality.
/// </summary>
[Test]
public void GroupByTestCustomList()
{
    ResultsetFields fields = new ResultsetFields(3);

    fields.DefineField(EmployeeFieldIndex.FirstName, 0, "FirstNameManager", "Manager");
    fields.DefineField(EmployeeFieldIndex.LastName, 1, "LastNameManager", "Manager");
    fields.DefineField(EmployeeFieldIndex.LastName, 2, "AmountEmployees", "Employee", AggregateFunction.Count);
    IRelationCollection relations = new RelationCollection();
    relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeId, "Employee", "Manager", RelationWeaknessHint.None);
    IPredicateExpression filter = new PredicateExpression();
    filter.Add(PredicateFactory.CompareValue(EmployeeFieldIndex.LastName, ComparisonOperator.Equal, "Manager", "Buchanan"));

    IGroupByCollection groupByClause = new GroupByCollection();
    groupByClause.Add(fields[0]);
    groupByClause.Add(fields[1]);

    DataTable tlist = new DataTable();
    TypedListDAO dao = new TypedListDAO();
    dao.GetMultiAsDataTable(fields, tlist, 0, null, filter, relations, true, groupByClause, null, 0, 0);

    Assert.AreEqual(1, tlist.Rows.Count);
    Assert.AreEqual(3, (int)tlist.Rows[0]["AmountEmployees"]);
}