A Look at Querying Data in SalesLogix using IRepository and the SQL Equivalents

For some who have been working in the SalesLogix LAN client for years, the transition from using SQL queries to retrieving data using a repository with the entity model can be a difficult change. This post will look at some common data retrieval methods and the SQL equivalents to help you get your head around how to get work done using the entity model.

Retrieving All Records from a Table

The following is an example query to retrieve all records in a table.

SQL Equivalent

SELECT * FROM PRODUCT

Code

Sage.Platform.Repository.IRepository<Sage.Entity.Interfaces.IProduct> repository = 
    Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IProduct>();
// returns an IList<IProduct>
result = repository.FindAll();

Sorting Query Results

The following will retrieve all records from a table and sort the results.

SQL Equivalent

SELECT * FROM PRODUCT ORDER BY FAMILY ASC

Code

Sage.Platform.Repository.IRepository<Sage.Entity.Interfaces.IProduct> repository = 
    Sage.Platform.EntityFactory.GetRepository<Sage.Entity.Interfaces.IProduct>();

// setup the query
IQueryable query = (IQueryable)repository;

// setup the expression factory
IExpressionFactory ef = query.GetExpressionFactory();

// setup your criteria
ICriteria criteria = query.CreateCriteria();

// add sort order
criteria.AddOrder(ef.Asc("Family"));

// returns an IList<IProduct>
result = criteria.List<IProduct>();

Query Using Where – Finding All Opportunities for an Account

The following will add a condition to a query to retrieve all opportunities for an account and order results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE ACCOUNTID = 'SOMEACCOUNTID' ORDER BY DESCRIPTION ASC

Code

// Using repository helper
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();

// EF and PF are setup automatically

// setup your criteria
ICriteria criteria = repository.CreateCriteria();

// add condition for account
criteria.Add(repository.EF.Eq("Account", account));

// add sort order
criteria.AddOrder(repository.EF.Asc("Description"));

// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using AND

The following will AND several query conditions together and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS = 'Closed - Won' AND DESCRIPTION = 'Some Description' ORDER BY DESCRIPTION ASC

Code

// Using repository helper
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();

// EF and PF are setup automatically

// setup your criteria
ICriteria criteria = repository.CreateCriteria();

// AND conditions together
criteria.Add(repository.EF.Conjunction()
.Add(repository.EF.Eq("Status", "Closed - Won"))
.Add(repository.EF.Eq("Description", "Some Description")));

// add sort order
criteria.AddOrder(repository.EF.Asc("Description"));

// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using OR

The following will OR several query conditions together and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS = 'Closed - Won' OR STATUS = 'Open' ORDER BY DESCRIPTION ASC

Code

// Using repository helper (EF and PF are setup automatically)
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();

// setup your criteria
ICriteria criteria = repository.CreateCriteria();

// OR conditions together
criteria.Add(repository.EF.Or(repository.EF.Eq("Status", "Closed - Won"),
repository.EF.Eq("Status", "Open")));

// add sort order
criteria.AddOrder(repository.EF.Asc("Description"));

// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using IN

The following will IN several query conditions together and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS IN ('Closed - Won','Open','Closed - Lost') ORDER BY DESCRIPTION ASC

Code

// Using repository helper (EF and PF are setup automatically)
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();

// setup your criteria
ICriteria criteria = repository.CreateCriteria();

// IN conditions together
criteria.Add(repository.EF.In("Status", new string[] {"Closed - Won", "Open", "Closed - Lost"}));

// add sort order
criteria.AddOrder(repository.EF.Asc("Description"));

// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using BETWEEN

The following will BETWEEN several query conditions together and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS BETWEEN 50000 AND 150000 ORDER BY DESCRIPTION ASC

Code

// Using repository helper (EF and PF are setup automatically)
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();

// setup your criteria
ICriteria criteria = repository.CreateCriteria();

// BETWEEN conditions together
criteria.Add(repository.EF.Between("SalesPotential", 50000, 150000));

// add sort order
criteria.AddOrder(repository.EF.Asc("Description"));

// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Query Conditions using LIKE

The following will LIKE a query condition and order the results.

SQL Equivalent

SELECT * FROM OPPORTUNITY WHERE STATUS LIKE 'Closed%' ORDER BY DESCRIPTION ASC

Code

// Using repository helper (EF and PF are setup automatically)
Sage.Platform.RepositoryHelper<Sage.Entity.Interfaces.IOpportunity> repository = 
    Sage.Platform.EntityFactory.GetRepositoryHelper<Sage.Entity.Interfaces.IOpportunity>();

// setup your criteria
ICriteria criteria = repository.CreateCriteria();

// LIKE conditions together
criteria.Add(repository.EF.Like("Status", "Closed%"));

// add sort order
criteria.AddOrder(repository.EF.Asc("Description"));

// returns an IList<IOpportunity>
result = criteria.List<IOpportunity>();

Hopefully that helps make the transition a bit easier. On this same topic, don’t forget to check out Kris Halsrud’s post on using NOT to exclude conditions.

ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

1 Comment

  1. Hi Chris,

    Since SalesLogix uses NHibernate underneath the covers, the question is really if NHibernate supports UNION, which it does not. You’d have to do two separate queries and then combine the collections from both results.

    -Ryan

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!