Hi,

I am trying to generate below SQL to LLBLGen for Audit purpose

SELECT
a.ebaDateTime,
a.ebaOperationGUID,
a.ebaByFullName,
a.ebaAuditTypeDescription,
a.ebaData,
a.ebaAuditIsDeleted,
b.ebdEmployeeId FROM dbo.inkEmployeeBankAudit a LEFT JOIN dbo.inkEmployeeBank b ON b.ebdEmployeeBankId = a.ebaEmployeeBankId ** AND** b.ebdEmployeeId = @EmployeeId;

I have spent 45 minutes to one hour on ChatGPT and Grok. Thay provided various answers and none of them worked due to no WHERE in SQL but it has AND

Can you please provide LLBLGen query using API (old way) not in LINQ or queryspecs.

Below is one of the example that AI suggested.

// 1. Define fields to fetch
IEntityField2[] fields = new IEntityField2[]
{
    InkEmployeeBankAuditFields.EbaDateTime,
    InkEmployeeBankAuditFields.EbaOperationGUID,
    InkEmployeeBankAuditFields.EbaByFullName,
    InkEmployeeBankAuditFields.EbaAuditTypeDescription,
    InkEmployeeBankAuditFields.EbaData,
    InkEmployeeBankAuditFields.EbaAuditIsDeleted,
    InkEmployeeBankFields.EbdEmployeeId // from joined table
};

// 2. Create the bucket
IRelationPredicateBucket bucket = new RelationPredicateBucket();

// 3. Create a dynamic relation (source: audit, target: bank)
DynamicRelation auditToBank = new DynamicRelation(typeof(InkEmployeeBankEntity))
{
    JoinHint = JoinHint.Left
};

// 4. Set source entity and ON clause
auditToBank.SourceEntityType = typeof(InkEmployeeBankAuditEntity); // source table

PredicateExpression onClause = new PredicateExpression();
onClause.Add(InkEmployeeBankFields.EbdEmployeeBankId == InkEmployeeBankAuditFields.EbaEmployeeBankId);
onClause.AddWithAnd(InkEmployeeBankFields.EbdEmployeeId == employeeId); // extra ON filter

auditToBank.OnClause = onClause;

// 5. Add relation to the bucket
bucket.Relations.Add(auditToBank);

// 6. Fetch results
DataTable results = new DataTable();
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, bucket, allowDuplicates: true);
}

in sort want to generate instead 'AND' in SQL instead of WHERE

You don't need to create a DynamicRelation, you can use one of the relationships generated as well. See: https://www.llblgen.com/Documentation/5.12/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Filtering%20and%20Sorting/gencode_filteringadvanced.htm#custom-filters-for-entityrelations

// 0. **Don't use AI poop, use the documentation!** As you can see, the crap AI spit out is wrong in many levels. 

// 1. Define fields to fetch
IEntityField2[] fields = new IEntityField2[]
{
    InkEmployeeBankAuditFields.EbaDateTime,
    InkEmployeeBankAuditFields.EbaOperationGUID,
    InkEmployeeBankAuditFields.EbaByFullName,
    InkEmployeeBankAuditFields.EbaAuditTypeDescription,
    InkEmployeeBankAuditFields.EbaData,
    InkEmployeeBankAuditFields.EbaAuditIsDeleted,
    InkEmployeeBankFields.EbdEmployeeId // from joined table
};

// 2. Create the bucket and relationship. I might have the field wrong, (Ebb instead of Eba)
IRelationPredicateBucket bucket = new RelationPredicateBucket();
var joinRelation = InkEmployeeBankAuditEntity.Relations.InkEmployeeBankEntityUsingEbaEmployeeBankId;

// 3. set predicate to be appended to ON clause:
joinRelation.CustomFilter = (InkEmployeeBankFields.EbdEmployeeId == employeeId); // extra ON filter

// 5. Add relation to the bucket
bucket.Relations.Add(auditToBank, JoinHint.Left);       // specify left join

// 6. Fetch results
DataTable results = new DataTable();
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, bucket, allowDuplicates: true);
}

QuerySpec or linq would have gotten you there way easier btw. It can fetch a datatable if you want...

Hi Otis,

Thank you.

var joinRelation = InkEmployeeBankAuditEntity.Relations.InkEmployeeBankEntityUsingEbaEmployeeBankId;

I do not have relationship between EmployeeBankAudit and EmployeeBank

so joinRelation line give me error after Relation as I do not have InkEmployeeBankEntityUsingEbaEmployeeBankId.

My table structure for inkEmployeeBank ebdEmployeeBankId, ebdEmployeeId, ebdBankPaymentOptionTypeId, ebdBankAccountName, ebdBankBSB, ebdBankAccountNumber, ebdBankDescription, ebdBankValue

My table structure for inkEmployeeBankAudit ebaEmployeeBankAuditId, ebaEmployeeBankId, ebaDateTime, ebaOperationGUID, ebaByFullName, ebaAuditTypeDescription

and want to generate this SQL SELECT a.ebaDateTime, a.ebaOperationGUID, a.ebaByFullName, a.ebaAuditTypeDescription, a.ebaData, a.ebaAuditIsDeleted, b.ebdEmployeeId FROM dbo.inkEmployeeBankAudit a LEFT JOIN dbo.inkEmployeeBank b ON b.ebdEmployeeBankId = a.ebaEmployeeBankId AND b.ebdEmployeeId = @EmployeeId;

You're not exactly new here, Kakaiya You know we need that kind of info to give you a good answer. So if there's no relationship defined between entities, you need to define that yourself using the DynamicRelation. The API is very simple, you should have discovered it purely through intellisense in the editor!

IEntityField2[] fields = new IEntityField2[]
{
    InkEmployeeBankAuditFields.EbaDateTime,
    InkEmployeeBankAuditFields.EbaOperationGUID,
    InkEmployeeBankAuditFields.EbaByFullName,
    InkEmployeeBankAuditFields.EbaAuditTypeDescription,
    InkEmployeeBankAuditFields.EbaData,
    InkEmployeeBankAuditFields.EbaAuditIsDeleted,
    InkEmployeeBankFields.EbdEmployeeId // from joined table
};

var onClause = new PredicateExpression(InkEmployeeBankFields.EbdEmployeeBankId == InkEmployeeBankAuditFields.EbaEmployeeBankId);
onClause.AddWithAnd(InkEmployeeBankFields.EbdEmployeeId == employeeId);

var joinRelation = new DynamicRelation(EntityType.InkEmployeeBankAuditEntity, JoinHint.Left, EntityType.InkEmployeeBankEntity, 
                    onClause);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(auditToBank); 

DataTable results = new DataTable();
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, bucket, allowDuplicates: true);
}

Hi Otis,

Giving error in VS.

Error below:

IEntityField2[] fields = new IEntityField2[]
  {
    InkEmployeeBankAuditFields.EbaDateTime,
    InkEmployeeBankAuditFields.EbaOperationGUID,
    InkEmployeeBankAuditFields.EbaByFullName,
    InkEmployeeBankAuditFields.EbaAuditTypeDescription,
    InkEmployeeBankAuditFields.EbaData,
    InkEmployeeBankAuditFields.EbaAuditIsDeleted,
    InkEmployeeBankFields.EbdEmployeeId // from joined table
  };

var onClause = new PredicateExpression(InkEmployeeBankFields.EbdEmployeeBankId == InkEmployeeBankAuditFields.EbaEmployeeBankId);
onClause.AddWithAnd(InkEmployeeBankFields.EbdEmployeeId == employeeId);

var joinRelation = new DAL.RelationClasses.DynamicRelation(*ERROR*EntityType.InkEmployeeBankAuditEntity**, JoinHint.Left, *ERROR*EntityType.InkEmployeeBankEntity**,
                    onClause);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(*ERROR*auditToBank**);

DataTable results = new DataTable();
using (var adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(*ERROR*fields**, results, bucket, allowDuplicates: true);
}

Also, How to attach image? - Found it.

I'm not going to help you on this, if you don't come up with something yourself. I don't know your model/project, so I'm guessing what the names can be. If I make a typo, that's easily fixed on your side by looking at what intellisense gives you. So type EntityType. and you'll get a list of entities, and pick the one you need. I get the feeling you aren't really understanding what code you're writing, which makes it very difficult for me to help you at all, because I'm now basically writing your code, which I'm not going to do.

Errors can be copied in visual studio, no need to create images. (the red lines below the code aren't the error message, that's in the Errors pane!)

bucket.Relations.Add(ERRORauditToBank**);

That's my fault, it should be bucket.Relations.Add(joinRelation);

However, if you knew what the code meant you'd have known what to fill in there! Please read the docs, they're there to help you make things understand!

Otis,

Thank you and I will read more Docs.