Here are a few basics using IRepository and ICriteria to query data in slx web.

Basic select statement

The most basic use of IRepository is using the Repository to return all rows back. This code shows getting a Repository for Products and then using the repository FindAll method to return all rows from the products entity.

Sage.Platform.Repository.IRepository
rep = Sage.Platform.EntityFactory.GetRepository();
//return ALL products
System.Collections.Generic.IList results = rep.FindAll();


Adding order by

In this code example we get the Product repository back but then need to also setup ICriteria to add an order by to our result. Once we have a criteria setup which we do below very easily because of our use of the RepositoryHelper that gives us access to CreateCriteria right from the repository.
Finally we call the AddOrder method off of criteria, AddOrder is expecting a parameter of type IOrder. We can use the ExpressionFactory, (EF) provided for use again because of the RepositoryHelper, to add the Asc order by to our query.

Sage.Platform.RepositoryHelper rep =
Sage.Platform.EntityFactory.GetRepositoryHelper();

Sage.Platform.Repository.ICriteria criteria = rep.CreateCriteria();

criteria.AddOrder(rep.EF.Asc("Name"));
System.Collections.IList results = criteria.List();

Jason talks more about the RepositoryHelper and ICriteria in his post
using-icriteria-like-ado-and-some-hql-too

Basic select with criteria

In this code we are setting up another criteria but this type with a where clause. In this example the query is something like this in SQL – select * from Account Where accountid = ‘some passed in ID’. But the big different in the method we are used to using SQL and the criteria we will setup using IRepository and ICriteria is the idea that we say where account entity = pass in account entity. We compare entities instead of ID’s… you can see that in the
criteria.Add(rep.EF.Eq("Account", account));
statement below notice how the parameters are “Account” and account. The “Account” is actually the name of the entity (or relationship) as it appears in Application Architect (AA). The lowercase account is actually a passed in variable or in our case a variable we setup in the first line of code by getting the current bindingsource off the form.

Sage.Entity.Interfaces.IAccount account =
BindingSource.Current as Sage.Entity.Interfaces.IAccount;

Sage.Platform.RepositoryHelper rep =
Sage.Platform.EntityFactory.GetRepositoryHelper();

Sage.Platform.Repository.ICriteria criteria = rep.CreateCriteria();

criteria.Add(rep.EF.Eq("Account", account));
System.Collections.IList results = criteria.List();


Creating a join

Use ICriteria CreateAlias method to create a join to another entity via an Application Architect relationship. In this case we have created a relationship between Account and Product in the Application Architect called AssociatedProducts. Then we use the CreateAlias method to setup that join, notice that the first parameter is the actual relationship name (from AA), the second parameter is our alias just for use in the code – these two could be named the same thats no problem. I just named them differently here to point out the difference between the two parameters. I combined the criteria.CreateAlias and the criteria.Add statements together just to show another possibility, but you certainly could seperate the two statements on two separate lines of code…

Sage.Entity.Interfaces.IAccount account =
BindingSource.Current as Sage.Entity.Interfaces.IAccount;

Sage.Platform.RepositoryHelper rep =
Sage.Platform.EntityFactory.GetRepositoryHelper();

Sage.Platform.Repository.ICriteria criteria = rep.CreateCriteria();

criteria.CreateAlias("AssociatedProducts","associatedproducts").Add(rep.EF.Eq("associatedproducts.Account", account));

System.Collections.IList results = criteria.List();


Setting a Projection

(used for aggregations or things like distinct)
We need to use the ProjectionFactory if we want to use a distinct in our select statement. In this case we are returning a distinct set of Products. We use criteria to get a ProjectionList then use the ExpressionFactory to setup each item in that projectionlist. You can think of the projectionlist as the items that you want in your select statement. If you only have one item in the projectionlist you can just add the projection as seen in with criteria.SetProjection(rep.PF.Distinct(Rep.PF.Property("Name"))); Or if you have multiple items in the projectionlist you add them using criteria.Add().


Sage.Platform.RepositoryHelper rep =
Sage.Platform.EntityFactory.GetRepositoryHelper();

Sage.Platform.Repository.ICriteria criteria = rep.CreateCriteria();

criteria.SetProjection(rep.PF.Distinct(Rep.PF.Property("Name")));
//OR
criteria.SetProjection(rep.PF.Distinct(Rep.PF.ProjectionList());
criteria.Add(rep.PF.Property("Name"));