Dynamics CRM 2016 (Online) - Retrieve appointments

Question

I'm trying to retrieve all the appointments from our CRM online environment using a LINQ query (I'm a newbie with programming). Getting the appointment data is easily done but I also want to retrieve the Required Attendees for the appointments (this could be an account, contact e.g) and fetch some Metadata (like the Name, E-mailadress for example) from the attendees. Unfortunately it seems impossible to get this done and was hoping someone could help me with this.

public AppointmentData[] RetrieveActivities(bool persistChange)
    {
        var appointmentData = new List<AppointmentData>();

        using (var context = new FmServiceContext(_service))
        {
            var appointments = (from app in context.AppointmentSet
                join a in context.AccountSet on app.Account_Appointments.AccountId equals a.AccountId
                where app.StateCode != 0
                select new {app, a});


            foreach (var apappointments in appointments)
            {
                appointmentData.Add(new AppointmentData
                {
                    //this should be the list of required attendees
                    RequiredAttendees = new ActivityParty[]
                    {
                        Attendeename = apappointments.a.Name

                    },
                    //Appointment data
                    AppointmentType = apappointments.app.fm_Typeafspraak == null ? null : DataHelper.GetOptionSetValueLabel(apappointments.app.LogicalName, "fm_typeafspraak", apappointments.app.fm_Typeafspraak.Value, _service),
                    Subject = apappointments.app.Subject,
                    StartDate = apappointments.app.ScheduledStart,
                    EndDate = apappointments.app.ScheduledEnd,
                    Duration = apappointments.app.ScheduledDurationMinutes,
                    Location = apappointments.app.Location,
                    Description = apappointments.app.Description,
                    Priority = apappointments.app.PriorityCode == null ? null : DataHelper.GetOptionSetValueLabel(apappointments.app.LogicalName, "prioritycode", apappointments.app.PriorityCode.Value, _service),
                    Status = apappointments.app.StateCode.ToString()
                });
            }

        }
        return appointmentData.ToArray();
    }

Answer 1

I don't think you need the join as the activity party ids are already there in your query.. You may be hitting a limitation in join functionality here. Here's an approach:

foreach(var app in appointments)
{
    var requiredAttendees = app.RequiredAttendees.ToList();

    foreach(var ra in requiredAttendees)
    {
         // do whatever you want with each required attendee here, perform a separate retrieve for more details

    }
}

Also I recognize that this is an extra step. If you want to try to get your join to work I'd recommend going against the partyid instead. If you're going to go that route you may need a nested query or more complex join since the relationship to ActivityParty is 1:N. Check out this link if you only care about the first required attendee: https://www.periscopedata.com/blog/4-ways-to-join-only-the-first-row-in-sql.html

Answer 2

Solved it! I did use Brendon's approach to do get it done. First I queried all the appointments,
Here's my code:

 public AppointmentData[] RetrieveAppointments(bool persistChange)
    {
        var appointmentData = new List<AppointmentData>();


        using (var context = new FmServiceContext(_service))
        {
            //First we get all relevant appointments
            var appointments = (from app in context.AppointmentSet
                                where app.StateCode != 0
                                select new { app});


            foreach (var appointment in appointments)
            {
               //we loop all the returned attendees of the appointments
                var attendees = new List<Attendee>();
                foreach (var attendee in appointment.app.RequiredAttendees)
                {
                    if (attendee.PartyId != null)
                    {
                        //if an attendee is an account
                        if (attendee.PartyId.LogicalName == "account")
                        {
                            var account = (from acc in context.AccountSet
                                where acc.AccountId == attendee.PartyId.Id
                                select new {acc});
                        }

                         //add the attendee
                          {
                            attendees.Add(new Attendee
                            {

                                // get additional metadata of the attendee
                            });
                        }
                      appointmentData.Add(new AppointmentData
                {
                    Attendees = attendees,
                    // get additional metadata of the appointment
                });
                    }
                }
             }
             return appointmentData.ToArray();
        }
    }
}

The result: a list of appointments with the a list of required attendees for that appointment.