Inner joins using LLBLGen?

Question

How could I do a simple join using LLBLGen?

table1 - clientTable (address, phone, etc) table2 - employeeTable (name, etc) table3 - clientEmployeeTable (clientid, employeeid)

I'm filling out a datagrid using the employeeId with fields for the client information (address, phone, etc) and I'm not sure how I could retrieve this using LLBLGen. I suppose I could create a stored procedure but maybe there's an easier way?

I'm completely new with LLBLGen.

I've been using stored procedures meanwhile but maybe there's a better way.

// in stored proc

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


// in code 
DataTable dt=RetrievalProcedures.GetEmployeeNote(EmployeeId);
rgridNotes.DataSource = dt;

Answer 1

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

Answer 2

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.