LLBGen for dummies

Question

I have an "opportunity" to work with LLBLGEN ORM, I have already spent two days on trying to query a JOIN command over several tables (without any success). Now at home I also trying to understand the logic of this ORM on smaller project (ie. Recruiter->JobOffer->Candidate). And yet this piece of software is whooping my ass. So I went to the documentation and tried to get some knowledge from with, unfortunately without any success.

So I'm asking here, is there somewhere a tutorial for real dummies that easily explains the very fundamental usage of LLBLGEN :

• where (ok, I've got this covered already)
• join
• multi join

Maybe someone has some code and db, that can share and illustrates this concepts done in friendly way.

Thanks!

Answer 1

Joins are expressed using a Relation object. If you want to retreive all Candidates of a specific Recruiter you could write something like this, asuming the tables have the correct Foreign Key relations

var list = new CandidateCollection();
var relationsToUse = new RelationCollection
{
  JobOfferEntity.Relations.CandidateEntityUsingCandidateId,
  RecruiterEntity.Relations.JobOfferEntityUsingJobOfferId
};
var filter = new PredicateExpression
{
  new FieldCompareValuePredicate(RecruiterFields.Id, ComparisonOperator.Equal, recruiterId)
};
list.GetMulti(filter, relationsToUse);

Answer 2

There are some central concepts in LLBLGen which I'll explain with samples in code and equivalent Sql query.

1. Predicate, IPredicate and PredicateExpression: These are translated to Where clauses. You can think of PredicateExpression as a complex predicate that's made of several predicates joined with AND and OR.

Note: Treat the code snippets below as pseudo code as I don't have access to LLBLGen right now.

    var pred = CustomerFields.Id == 5;
    new DataAccessAdapter.FetchEntityCollection(coll, new RelationPredicateBucket(pred));

This will roughly translate to:

  SELECT * FROM Customer WHERE Id = 5

You can combine several predicates using PredicateExpression:

   var predEx = new PredicateExpression();
   predEx.Add(CustomerFields.Id == 5);
   predEx.AddWithOr(CustomerFields.Name == "X");

Is equivalent to:

   SELECT * FROM Customer WHERE Id = 5 OR Name = 'X'

2. Relations: Relations represents the relations in your database. There's a Relations property in every Entity in generated code that contains every relation of that entity. For example if you have a Customer table that has a one-to-many relation with your Order table, the corresponding Entities will have a static Relations property that contains those relations:

   CustomerEntity.Relations.OrderEntityUsingCustomerId; OrderEntity.Relations.CustomerEntityUsingCustomerId;

You use these relations when you want to perform a join and return results based on the join. For example if you want to fetch all customers that have an order that's varlue is greater than 50000 you do this:

var pred = OrderFields.Value > 50000;
var rpb = new RelationPredicateBucket();
rpb.PredicateExpression.Add(pred);
rpb.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);//perform join

This will translate to:

SELECT C.* FROM Customer AS C JOIN Order AS O ON C.Id = O.CustomerId WHERE O.Value > 50000

For multiple joins you just add more relations, to get customer that have orders with values higher than 50000 that have an OrderDetail that's quantity is more than 1:

var pred = OrderFields.Value > 50000 & OrderDetailFields.Quantity > 1;
var rpb = new RelationPredicateBucket();
rpb.PredicateExpression.Add(pred);
rpb.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);//perform customer and order join
rpb.Relations.Add(OrderEntity.Relations.OrderDetailEntityUsingOrderId);//perform order and order detail join

Which produces this sql query:

SELECT C.* FROM Customer AS C JOIN Order AS O ON C.Id = O.CustomerId JOIN OrderDetail AS OD ON OD.OrderId = O.Id WHERE O.Value > 50000 AND OD.Quantity > 1