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.
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