Hello everybody,

recently friend of mine gave me wonderful question:

In PXSelect command, I saw Search, Search2, Search3… keywords, please explain the difference.

Thats really good question which shows his attentiveness to details.

So, no let's go part by part.

Targets

First of all, Search statement can be applied to those kinds of attributes: PXSelector, PXDbScalar and PXDefault. 

Also you can apply Search statement to cases when you updated something in cache of Acumatica and what to reopen that part. It can look like this:

Document.Search<POOrder.orderNbr>(currentPoOrder.OrderNbr, currentPoOrder.OrderType);

or like this:

[PXDefault(typeof (Search<Company.baseCuryID>))]

Don't mix attribute use with search in graph. The first form you can use in your methods, while second you can use in DAC class. 

As attributes

If to speak about attributes Search allows you to set specific value of of field in your DAC class. In other words it selects exact field rather then a record. The field specification goes as paramter. If to speak about syntax it is identical to PXSelect.

Following search options exist:

I hope with this table you can now better understand which search to use

7 Comments

• Dmitrey Makarov-Paton said 15 months ago

  Thanks, Yuriy, great post. Please post more. Pls, can you clarify one thing though?
  
  Case 1. (DataView.Search) Does the query executed against the database every time or it searches the caches first? What happens on the consecutive calls to the search.
  
  Case 2.
  Does the query executed when the item inserted into the cache only.? What happens on the consecutive calls also. Does the cache of the type specified is searched?

• docotor said 15 months ago

  Hello Dmitrey,
  for case 1 queries will be executed first time against db, other times if possible against cache in order to minimize load on db.
  For case 2.I'm not sure that I understand it. Can you please add a bit more description to your question

• Dmitrey said 14 months ago

  Hi Yuriy, thank you for answering. I'm observing a different behaviour though. Perhaps you can spot an issue. Please see my contrived example below. My observations as follows...
  1. DataView.Search always executes SELECT TOP(1) ... for a new key value. Regardless, where there items of this type in the cache.
  2. Same behaviour for Search in PXDefault
  
  Please try the code below. Set up SQL PROFILER to for RPC:Competed & SQL:BatchCompleted events.
  Thanks. Keep up with your awesome posts.
  
  public class DataContext : PXGraph<DataContext>, IDisposable {
  
  public PXSelect<PriceList> PriceList;
  public PXSelect<ParkingLot> ParkingLots;
  
  public void Init() {
  PriceList.Select();
  }
  
  public void Dispose() => this.Clear();
  public ParkingLot CreateParkingLot() => (ParkingLot)ParkingLots.Cache.CreateInstance();
  }
  
  
  public class Test {
  
  public void DoThings1() {
  
  using (DataContext dc = PXGraph.CreateInstance<DataContext>()) {
  dc.Init(); // Select all prices
  
  int parkingLotId = 2;
  dc.ParkingLots.Select(); // Database is quired, how many records you have in db now in cache
  ParkingLot item = dc.ParkingLots.Cache.Cached.Cast<ParkingLot>().FirstOrDefault(row => row.ParkingLotID == parkingLotId);
  Debug.Assert(item != null, "Must in the cache");
  dc.ParkingLots.SetValueExt<ParkingLot.grade>(item, "B"); // Your other example! item's status does not change to Updated!!
  
  //.....
  var instance1 = (ParkingLot) dc.ParkingLots.Search<ParkingLot.parkingLotID>(parkingLotId); // Database queried again. SELECT TOP(1) ... WHERE ID=1
  Debug.Assert(Object.ReferenceEquals(instance1, item), "Same");
  Debug.Assert(instance1.ParkingLotID == item.ParkingLotID, "Same");
  
  //....
  var instance2 = (ParkingLot) dc.ParkingLots.Search<ParkingLot.parkingLotID>(parkingLotId); // 2nd call, Database NOT queried here unless you change ID to 2.
  Debug.Assert(Object.ReferenceEquals(instance1, instance2), "Same");
  
  
  var instance3 = dc.ParkingLots.Insert(dc.CreateParkingLot().With(r => r.Grade = "C")); // Triggers query on pxdefault
  Debug.Assert(Decimal.Compare(instance3.PricePerDay.Value, 5) == 0);
  
  
  var instance4 = dc.ParkingLots.Insert(dc.CreateParkingLot().With(r => r.Grade = "B")); // Triggers query on pxdefault
  Debug.Assert(Decimal.Compare(instance4.PricePerDay.Value, 10) == 0);
  }
  }
  }
  
  
  public class PriceList : IBqlTable {
  public abstract class grade : IBqlField { }
  [PXDBString(1, IsKey = true)]
  public string Grade { get; set; }
  
  public abstract class price : IBqlField { }
  [PXDBDecimal]
  public decimal? Price { get; set; }
  }
  
  /// <summary>
  /// </summary>
  [Serializable]
  public class ParkingLot : IBqlTable {
  public abstract class parkingLotID : IBqlField {}
  [PXDBIdentity(IsKey = true)]
  [PXUIField(DisplayName = "ID", Enabled = false, Visible = false)]
  [PXDefault]
  public virtual int? ParkingLotID { get; set; }
  
  
  public abstract class grade : IBqlField { }
  [PXDBString(1, IsFixed = true)]
  [PXDefault("A")]
  public virtual string Grade { get; set; }
  
  
  public abstract class pricePerDay : IBqlField { }
  [PXDBDecimal()]
  [PXDefault(typeof(Search<PriceList.price, Where<PriceList.grade, Equal<Current<ParkingLot.grade>>>>))]
  public virtual decimal? PricePerDay { get; set; }
  }
  
  
  create table [dbo].[PriceList](
  [Grade] [nchar](1) NOT NULL PRIMARY KEY,
  [Price] [decimal](18, 2) NOT NULL,
  )
  
  create table [dbo].[ParkingLot](
  [ParkingLotID] [int] IDENTITY(1,1) NOT NULL primary key,
  [Grade] [nchar](1) NULL,
  [PricePerDay] [decimal](18, 2) NULL,
  )

• docotor said 14 months ago

  Hi Dmitrey,
  thanks for valuable test and comment. You are right.

• docotor said 14 months ago

  Just want to add. The query that always execute against database are PXSelectReadOnly and it's modifications. Other kind of queries can execute against db, but also can omit db, and read everything from cache or part from cache and part from db.

• Dmitrey Makarov-Paton said 14 months ago

  Thanks Yuri. Where do you work? What's your skype/email?

• docotor said 14 months ago

  My skype is zaletskiy. For now I work as remote contractor and I mainly specialize on Acumaitca extending. I've send you an email message also.