Complex Query in QueryExpression in Microsoft CRM 2011
Hi,
Here is tables and relation,QueryExpression statement afterwards.
contact 1--->N new_ticket N --> N new_tour
QueryExpression query = new QueryExpression()
{
EntityName = "contact",
ColumnSet = new ColumnSet(new string[] {
"contactid",
"firstname",
"lastname",
"birthdate",
"fullname"
}
)
};
Query Expression will execute the query against contact.
For performance issue; do not retrieve any columns that we do not need for the current query result.
FilterExpression filterDate = new FilterExpression();
filterDate.Conditions.Add(
new ConditionExpression("begin_date", ConditionOperator.GreaterEqual,DateTime.Now)
);
filterDate.Conditions.Add(
new ConditionExpression("end_date", ConditionOperator.LessThan, DateTime.Now)
);
filterBeginDate.FilterOperator = LogicalOperator.And;
Filter Expression is "where" statement in Ansi Sql. Filter Expression takes condition collections to use in where clause by ConditionExpression class.
//all about ticket
query.LinkEntities.Clear();
query.LinkEntities.Add(new LinkEntity("contact", "new_ticket_contact", "contactid", "contactid", JoinOperator.Inner));
query.LinkEntities[0].EntityAlias = "contacttoticket";
query.LinkEntities[0].AddLink("new_ticket", "new_ticketid", "new_ticketid", JoinOperator.Inner);
query.LinkEntities[0].LinkEntities[0].AddLink("new_tour", "new_tourid", "new_tourid", JoinOperator.Inner);
query.LinkEntities[0].LinkEntities[0].LinkEntities[0].LinkCriteria = filterDate;
LinkEntities make 1 to n or n to n relation between tables.
as code above;
add link to QueryExpression object with calling Add() .
LinkEntity has a parameterized construction that takes fromEntity,toEntity,fromEntityColumnID,toEntityColumnID,joinOperatorType respectively.
Here is important trick:
toEntity means; '1 to n' or 'n to n' connection name of two entities that are given by CRM.
this sample is good for nested inner join (select x.* from x inner join y inner join z on z.id = y.id on y.id = x.id ...).to do this add internal Link for each LinkEntity.
query is now ready to be run by ServiceProxy.
example :
EntityCollection recordColl = helperClass.ServiceProxy.RetrieveMultiple(query);
good luck...
Here is tables and relation,QueryExpression statement afterwards.
contact 1--->N new_ticket N --> N new_tour
QueryExpression query = new QueryExpression()
{
EntityName = "contact",
ColumnSet = new ColumnSet(new string[] {
"contactid",
"firstname",
"lastname",
"birthdate",
"fullname"
}
)
};
Query Expression will execute the query against contact.
For performance issue; do not retrieve any columns that we do not need for the current query result.
FilterExpression filterDate = new FilterExpression();
filterDate.Conditions.Add(
new ConditionExpression("begin_date", ConditionOperator.GreaterEqual,DateTime.Now)
);
filterDate.Conditions.Add(
new ConditionExpression("end_date", ConditionOperator.LessThan, DateTime.Now)
);
filterBeginDate.FilterOperator = LogicalOperator.And;
Filter Expression is "where" statement in Ansi Sql. Filter Expression takes condition collections to use in where clause by ConditionExpression class.
//all about ticket
query.LinkEntities.Clear();
query.LinkEntities.Add(new LinkEntity("contact", "new_ticket_contact", "contactid", "contactid", JoinOperator.Inner));
query.LinkEntities[0].EntityAlias = "contacttoticket";
query.LinkEntities[0].AddLink("new_ticket", "new_ticketid", "new_ticketid", JoinOperator.Inner);
query.LinkEntities[0].LinkEntities[0].AddLink("new_tour", "new_tourid", "new_tourid", JoinOperator.Inner);
query.LinkEntities[0].LinkEntities[0].LinkEntities[0].LinkCriteria = filterDate;
LinkEntities make 1 to n or n to n relation between tables.
as code above;
add link to QueryExpression object with calling Add() .
LinkEntity has a parameterized construction that takes fromEntity,toEntity,fromEntityColumnID,toEntityColumnID,joinOperatorType respectively.
Here is important trick:
toEntity means; '1 to n' or 'n to n' connection name of two entities that are given by CRM.
this sample is good for nested inner join (select x.* from x inner join y inner join z on z.id = y.id on y.id = x.id ...).to do this add internal Link for each LinkEntity.
query is now ready to be run by ServiceProxy.
example :
EntityCollection recordColl = helperClass.ServiceProxy.RetrieveMultiple(query);
good luck...
Hello,
ReplyDeleteI need your for this code please:
QueryExpression LISTECLIENT = new QueryExpression("account");
LISTECLIENT.ColumnSet.AllColumns = true;
LISTECLIENT.LinkEntities.Add(new LinkEntity("account", "invoice", "accountid", "customerid", JoinOperator.Inner));
LISTECLIENT.LinkEntities[0].Columns.AddColumns("name", "new_tvaintracom", "new_bic", "new_iban", "adress1_line1", "adress1_line2", "adress1_line3", "adress1_postalcode", "adress1_city", "adress1_country");
LISTECLIENT.LinkEntities[0].EntityAlias = "ExClient";
LISTECLIENT.LinkEntities[0].LinkCriteria.AddCondition("prompt_factatraiter", ConditionOperator.Equal, true);
EntityCollection LIGNEACCOUNT = _orgService.RetrieveMultiple(LISTECLIENT);
Log.MonitoringLogger.Info("Nombre de client à traiter : " + LIGNEACCOUNT.Entities.Count.ToString());
for (int a = 0; a < LIGNEACCOUNT.Entities.Count; a++)//liste le nombre de ligne de facture
{
Log.MonitoringLogger.Info("Boucle client: " + a.ToString());
Log.MonitoringLogger.Info("Client n°: " + LIGNEACCOUNT.Entities[a].Attributes["accountnumber"].ToString());
Log.MonitoringLogger.Info("Id prompt_exportclient : " + ((AliasedValue)LIGNEACCOUNT.Entities[a].Attributes["ExFact.prompt_exportclienttid"]).Value.ToString());
So my variable LIGNEACCOUNT is nul and i don't understand why.