Really struggle with the CRM query logic and was hoping someone might be able to scan this and tell me what is the difference between this code and my SQL query equivalent.
Note: I did take out some of the prefix on the entity and field names to protect some company specific information. Hopefully I did not mess that up.
SQL Query - Selects 10 records
USE CRM_MSCRM
GO
SELECT
bc.loannumberName,
c.fullname,
bc.loannumber,
c.CreatedOn,
bc.name,
*
FROM [CRM_MSCRM].[dbo].[buildercontract] bc
INNER JOIN [CRM_MSCRM].[dbo].[contact] c ON c.contactid = bc.customer
INNER JOIN [CRM_MSCRM].[dbo].[loan] l ON bc.loannumber = l.loanid
WHERE
c.customertypecode = 5
AND bc.name IS NOT NULL
AND bc.customer IS NOT NULL
AND bc.loannumber IS NOT NULL
AND c.createdon >= '01/01/2016'
AND c.createdon < '01/01/2017'
AND l.primaryborrower IS NOT NULL
Code from C# Plugin - Selects over 500 records
So what I am doing wrong here? I've tried to make this work exactly like the SQL above.
Note: I did take out some names to protect some company specific information. Hopefully I did not mess that up.
private static QueryExpression GetQueryExpressionByBuilderContact(DateTime? startDate, DateTime? endDate)
{
const int borrowerContactTypeCode = 4;
var queryExpression = new QueryExpression("buildercontract") { ColumnSet = new ColumnSet("name", "customer", "loannumber") };
// Add Builder Contract Link (Inner Join)
LinkEntity contractLink = queryExpression.AddLink(Contact.EntityLogicalName, "customer", "contactid", JoinOperator.Inner);
contractLink.Columns.AddColumn("contactid");
contractLink.Columns.AddColumn("customertypecode");
contractLink.Columns.AddColumn("createdon");
contractLink.LinkCriteria.AddCondition(new ConditionExpression("createdon", ConditionOperator.GreaterEqual, startDate));
contractLink.LinkCriteria.AddCondition(new ConditionExpression("createdon", ConditionOperator.LessThan, endDate));
contractLink.LinkCriteria.AddCondition(new ConditionExpression("customertypecode", ConditionOperator.Equal, borrowerContactTypeCode));
// Add Loan Link (Inner Join)
LinkEntity loanLink = queryExpression.AddLink(loan.EntityLogicalName, "loannumber", "loanid", JoinOperator.Inner);
loanLink.Columns.AddColumn("loanid");
loanLink.Columns.AddColumn("primaryborrower");
loanLink.LinkCriteria.AddCondition("primaryborrower", ConditionOperator.NotNull);
// Add Filter
var filterExpression = new FilterExpression();
var nameFilter = new FilterExpression(LogicalOperator.And);
nameFilter.Conditions.Add(new ConditionExpression("name", ConditionOperator.NotNull));
nameFilter.Conditions.Add(new ConditionExpression("customer", ConditionOperator.NotNull));
nameFilter.Conditions.Add(new ConditionExpression("loannumber", ConditionOperator.NotNull));
filterExpression.AddFilter(nameFilter);
queryExpression.Criteria = filterExpression;
int queryCount = 1250; // The number of records per page to retrieve.
int pageNumber = 1; // Initialize the page number.
// Assign the pageinfo properties to the query expression.
queryExpression.PageInfo = new PagingInfo
{
Count = queryCount,
PageNumber = pageNumber,
PagingCookie = null // The current paging cookie. When retrieving the first page, pagingCookie should be null.
};
//queryExpression.AddOrder("fullname", OrderType.Ascending);
return queryExpression;
}
This is the first time I have tried to use a CRM query expression. So I suspect there is sometime really simple here I am misunderstanding or overlooking.
Any guidance, suggestions, etc. are much appreciated. I've been messing with this for a couple of days now and cannot get it right.
Best regards,
Jon Rothlander
