How to use Where condition in PXDatabase Select

Hello ​@arthia You can use like this :

E.g.

Filter records with WHERE condition

var results = PXDatabase.Select<Table>(
    new PXDataFieldRestrict(Table.fieldName, PXDbType.VarChar, "VALUE", PXComp.EQ)
).RowCast<Table>().ToList();

With Integer

var results = PXDatabase.Select<Table>(
    new PXDataFieldRestrict(Table.inventoryID, PXDbType.Int, 123, PXComp.EQ)
).RowCast<Table>().ToList();

Multiple Condition

var results = PXDatabase.Select<Table>(
    new PXDataFieldRestrict(Table.status, PXDbType.VarChar, "A", PXComp.EQ),
    new PXDataFieldRestrict(Table.active, PXDbType.Bit, true, PXComp.EQ)
).RowCast<Table>().ToList();

Note : 

• PXDatabase.Select<T> bypasses DAC logic and caches. it hits SQL directly.

• Always use PXDataFieldRestrict for filtering.

• Never use business logic here (because graph is bypassed).

I hope it helps.

Hi ​@Abhishek Niikam , Thank you for your response.

I tried with the suggested BQL query but I’m facing issue, shared the details in screenshot

Hello ​@arthia You can try this if your field name is not different from the property name:

var results = PXDatabase.Select<KNDCSPImageDetails>(new PXDataFieldRestrict(nameof(KNDCSPImageDetails.IsProcessed), PXDbType.Bit, 0, PXComp.EQ)).RowCast<KNDCSPImageDetails>().ToList();

var results1 = PXDatabase.SelectMulti<KNDCSPImageDetails>(new PXDataFieldRestrict(nameof(KNDCSPImageDetails.IsProcessed), PXDbType.Bit, 0, PXComp.EQ)).RowCast<KNDCSPImageDetails>().ToList();

I hope it helps!

Hi ​@Abhishek Niikam , Thank you sharing some more BQL approaches, still facing issue.

Hi ​@arthia were you able to find a solution? Thank you!

Hi ​@Chris Hackett , No, we didn’t get solution

Hello ​@arthia, this works for me for testing purposes.

 

Hi ​@arthia ,

below query worked for me,

   IEnumerable<SOOrder> sOOrders= PXDatabase.SelectRecords<SOOrder>(new PXDataFieldValue<SOOrder.completed>(0));

Hope above helps !!

Hi @arthia

Here are examples to filter records using a Where condition with PXDatabase:

 List<string> soOrderTypes = PXDatabase.SelectMulti<SOOrderType>(
    new PXDataField<SOOrderType.orderType>(), // Index 0
    new PXDataField<SOOrderType.behavior>())  // Index 1
    .Where(r => r.GetString(1) == "SO")       // Filter by Behavior (String)
    .Select(r => r.GetString(0))              // Select the OrderType
    .ToList();

 foreach (string s in soOrderTypes)
 {
     // Process order type records
 }

List<SOOrderType> soOrderTypes = PXDatabase.SelectMulti<SOOrderType>(
   new PXDataField<SOOrderType.orderType>(), // Index 0
   new PXDataField<SOOrderType.behavior>(),  // Index 1
   new PXDataField<SOOrderType.isSystem>(),  // Index 2
   new PXDataField<SOOrderType.descr>()    //  Index 3
   )  
   .Where(r => r.GetString(1) == "SO" && // Filter by Behavior (String)
               r.GetBoolean(2) == true) // Filter by IsSystem (Boolean)     
   .Select(r => new SOOrderType
   {
      OrderType = r.GetString(0),
      Behavior = r.GetString(1),
      Descr = r.GetString(3),
   })
   .ToList();

foreach (SOOrderType s in soOrderTypes)
{
    // s.IsSystem -> value is null, because only OrderType, Behavior, Descr were assigned in the .Select()

    // Process order type records
}