working SQL statement

select 
S.[ID]
,S.[Surname]
,S.[Firstname] 
,count(L.ID) as 'Total'
from seller S
left join Lot L on L.SellerID = S.ID and L.deleted = 0
group by S.[ID], S.[Surname], S.[Firstname]

this is what i have working but not what i have in mind.

var table = new DataTable();
            var da = new DataAccessAdapter();

            try
            {
                var fields = new ResultsetFields(4);
                fields.DefineField(SellerFields.Id, 0);
                fields.DefineField(SellerFields.Surname, 1);
                fields.DefineField(SellerFields.Firstname, 2);
                fields.DefineField(LotFields.Id, 3, "Loten");

fields[4].ExpressionToApply = new DbFunctionCall("Count({0})", new object[] { LotFields.Id });

var vBucket = new RelationPredicateBucket();
                vBucket.Relations.Add(SellerEntity.Relations.LotEntityUsingSellerId, JoinHint.Left);

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

 IPredicateExpression filter = new PredicateExpression(SellerFields.Deleted == false);
               // filter.Add(LotFields.Deleted == false);
                vBucket.PredicateExpression.Add(filter);
                var vSort = new SortExpression();
                vSort.Add(SellerFields.Surname | SortOperator.Ascending);
                vSort.Add(SellerFields.Firstname | SortOperator.Ascending);

                da.FetchTypedList(fields, table, vBucket, 0, vSort, true, groupByClause);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                da.Dispose();
            }

            return table;

The code above does only provide sellers that have related records.

If 1 seller does not have any "lot" related then the count should be 0. (But this is not shown in the result.

In SQL it's the and L.deleted = 0 added to the join.

How do i do this with llblgen ?

The IEntityRelation interface has a CustomFilter property that you can set.

var relation = SellerEntity.Relations.LotEntityUsingSellerId;
relation.CustomFilter = new PredicateExpression(SellerFields.Deleted == false);

var vBucket = new RelationPredicateBucket();
vBucket.Relations.Add(relation, JoinHint.Left);