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