You probably want to create some fields on related fields . You can add ClientGroup properties to the Client entity to access them transparently. This only works for directly related fields in an (m:1) relation. If you want to join deeper than that, you have to use typed lists.

When you fetch entities and you want to join because of the where statement, you can use relations to join the tables and build a predicate.

Regards

Welcome to LLBLGen! Great product once you re schooled up. One way to get information from a variety of joined tables is with a Typed List of your own design.

Your "my specific fields" get defined in the ResultsetFields. Your WHERE clause is defined with an IPredicateExpression. Your JOIN clauses are brilliantly handled by the IRelationCollection and the .Relations properties of each Entity.

When trying to get up to speed, running the SQL Server profiler (presuming you re using MSSQL) is key to see how your code changes in LLBLGen affect the actual SQL requested from the server. Managing all of the () s in the JOIN and WHERE clauses sometimes requires careful ordering but most of the time works on the first try. Here s a generic snippet that we use:

private static DataTable GetTheGoodsOnEmployeeClients()
{
  // Define the fields that you want in your result DataTable
  ResultsetFields fields = new ResultsetFields(2);
  fields.DefineField(MyEntityFields.FieldName1, 0);
  fields.DefineField(MyEntityFields.FieldName2, 1, "Field Name Alias");

  // Add the WHERE clause to the query - "Condition" can be a literal or a variable passed into this method
  IPredicateExpression filter = new PredicateExpression();
  filter.Add(MyEntityFields.FieldName == "Condition");

  // Add all JOIN clauses to the relation collection
  IRelationCollection relations = new RelationCollection();
  relations.Add(MyEntity.Relations.FKRelationship);
  relations.Add(MyEntity.Relations.FKRelationship, JoinHint.Left);

  ISortExpression sort = new SortExpression();
  sort.Add(MyEntityFields.FieldName | SortOperator.Ascending);

  // Create the DataTable, DAO and fill the DataTable with the above query definition/parameters
  DataTable dt = new DataTable();
  TypedListDAO dao = new TypedListDAO();
  dao.GetMultiAsDataTable(fields, dt, 0, sort, filter, relations, false, null, null, 0, 0);

  return dt;
}        

Your specific need:

SELECT (my specific fields)
FROM [client].[List] abl
    INNER JOIN [client].ClientGroup cg ON cg.ClientGroupId = abl.ClientGroupId

Would thus become:

private static DataTable GetTheGoodsOnEmployeeClients()
{
  // Define the fields that you want in your result DataTable
  ResultsetFields fields = new ResultsetFields(3);
  fields.DefineField(ClientFields.ClientId, 0);
  fields.DefineField(ClientFields.ClientName, 1, "Client");
  fields.DefineField(ClientGroupFields.ClientGroupName, 2, "Group");

  // Add all JOIN clauses to the relation collection
  IRelationCollection relations = new RelationCollection();
  relations.Add(ClientEntity.Relations.ClientGroupEntityUsingClientGroupId);

  // Create the DataTable, DAO and fill the DataTable with the above query definition/parameters
  DataTable dt = new DataTable();
  TypedListDAO dao = new TypedListDAO();
  dao.GetMultiAsDataTable(fields, dt, 0, null, null, relations, false, null, null, 0, 0);

  return dt;
}        

While this seems like a lot of code to do something basic, the code almost writes itself once you re used to it and is vastly faster than writing plumbing. You ll never go back.

Use LinqMetaData

LinqMetaData metaData = new LinqMetaData();

if you use adapter mode

LinqMetaData metaData = new LinqMetaData(adapter);

var result = from a in metaData.TableA
               join b in metaData.TableB on a.Key equals b.Key
             where a.OtherField == value
             select a;