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

Comments

  1. Hello,
    I 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.

    ReplyDelete

Post a comment

Popular posts from this blog

Assembly Microsoft.Dynamics.Service.Plugins.dll can not be loaded. Dynamics CRM 365 Engine version 9 - CRM User creation error

Exception caught instantiating TERADATA report server extension SQL Reporting Services

Could not load file or assembly 'System.ServiceModel, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. The system cannot find the file specified at Configuration class initiation in CrmServiceHelper.cv