Using IRepository in the SalesLogix web client can allow querying the SalesLogix entity model directly, similar to how you used to query the database directly using T-SQL queries. Ryan has already created a great post here about this, and I expanded on that here. Today I want to talk about one more functionality of the IRepository, that is how to use Projection to return a distinct list of records from your query.
Lets take a look at how to return a distinct list of Families in the Product table; The first step is to build your normal query using IRepository:
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IProduct> repository = Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IProduct>();
Sage.Platform.Repository.ICriteria criteria = repository.CreateCriteria();
criteria.Add(repository.EF.IsNotNull("Family"));
criteria.AddOrder(repository.EF.Asc("Family")); |
This established what records you will be returning. In my case I just added a restriction to only return products with a Family, and then I ordered the result by Family in Ascending order. Now that we have our query we can then add Projection to it:
criteria.SetProjection(repository.PF.Distinct(repository.PF.Property("Family"))); |
Finally we return our distinct list:
System.Collections.IList products = criteria.List(); |
With that returned we can then do something with it like bind it to a combo box:
cboFamily.DataSource = products;
cboFamily.DataBind(); |
Lets look at it all together:
System.Collections.Generic.IList<Sage.SalesLogix.PickLists.PickList> picklists = Sage.SalesLogix.PickLists.PickList.GetPickListItemsByName("Product Family", true);
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IProduct> repository = Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IProduct>();
Sage.Platform.Repository.ICriteria criteria = repository.CreateCriteria();
criteria.Add(repository.EF.IsNotNull("Family"));
criteria.AddOrder(repository.EF.Asc("Family"));
criteria.SetProjection(repository.PF.Distinct(repository.PF.Property("Family")));
System.Collections.IList products = criteria.List();
cboFamily.DataSource = products;
cboFamily.DataBind(); |
One last thing to note. If you want to return a distinct result on more than one field you can change this:
criteria.SetProjection(repository.PF.Distinct(repository.PF.Property("Family"))); |
To this:
criteria.SetProjection(repository.PF.Distinct(repository.PF.ProjectionList()
.Add(repository.PF.Property("Family"))
.Add(repository.PF.Property("Product")))); |