Find User with least number of Tasks due on a Date

In this scenario, we need to find a user in a team with the least number of Tasks assigned to them on a particular date so that we can assign a newly created Task record to them. This example can easily be extended to look at all activities due on a date (activitypointer record).

using System;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Collections.Generic;
using System.Workflow.ComponentModel.Compiler;
using System.Workflow.ComponentModel.Serialization;
using System.Workflow.ComponentModel;
using System.Workflow.ComponentModel.Design;
using System.Workflow.Runtime;
using System.Workflow.Activities;
using System.Workflow.Activities.Rules;

using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.Sdk.Query;
using Microsoft.Crm.Workflow;
using Microsoft.Crm.Workflow.Activities;

namespace CustomWorkflow
{    
    [CrmWorkflowActivity("Find User with least Tasks due on specified Date", "Custom")]
	public partial class AvailableUser: SequenceActivity
	{
        protected override ActivityExecutionStatus Execute(ActivityExecutionContext executioncontext)
        {
            # region get an instance of the Crm Web service
            IContextService contextservice = (IContextService)executioncontext.GetService(typeof(IContextService));
            IWorkflowContext context = contextservice.Context;
            ICrmService crm = context.CreateCrmService(true);
            # endregion

            GetUserWhoHasLeastTasksDueOn(crm);

            return ActivityExecutionStatus.Closed;
        }

        private void GetUserWhoHasLeastTasksDueOn(ICrmService crm)
        {
            # region variables
            List users = null;
            int? leastcount = null;
            Guid leastuser = Guid.Empty;
            # endregion

            /* get a list of users in the team */
            users = GetTeamMembers(crm);

            /* only proceed if we have found some users */
            if (users != null)
            {
                foreach (Guid userid in users)
                {
                    /* get a list of Task activities due on the specified date for this user */
                    List tasks = FindTasksDueOnDateForUser(userid, crm);

                    /* check if this user's due Tasks are less than what we've found so far */
                    if (tasks != null && ( leastcount == null || leastcount > tasks.Count))
                    {
                        leastcount = tasks.Count;
                        leastuser = userid;
                    }
                }
            }

            /* assign our output User variable a value */
            if (!leastuser.Equals(Guid.Empty))
                user = new Lookup(EntityName.systemuser.ToString(), leastuser);
        }

        private List FindTasksDueOnDateForUser(Guid userid, ICrmService crm)
        {
            List tasks = null;

            try
            {
                ConditionExpression owner = new ConditionExpression("ownerid", ConditionOperator.Equal, userid.ToString());
                ConditionExpression duedate = new ConditionExpression("scheduledend", ConditionOperator.On, dueDate.Value);

                FilterExpression filter = new FilterExpression();
                filter.AddCondition(owner);
                filter.AddCondition(duedate);
                filter.FilterOperator = LogicalOperator.And;

                ColumnSet cols = new ColumnSet(new string[] { "activityid" });

                QueryExpression query = new QueryExpression();
                query.ColumnSet = cols;
                query.Criteria = filter;
                query.EntityName = EntityName.task.ToString();

                BusinessEntityCollection collection = crm.RetrieveMultiple(query);

                if (collection != null)
                    tasks = collection.BusinessEntities;
            }
            catch
            {
            }

            return tasks;
        }

        private List GetTeamMembers(ICrmService crm)
        {
            List members = null;

            try
            {
                RetrieveMembersTeamRequest request = new RetrieveMembersTeamRequest();
                request.EntityId = team.Value;
                request.MemberColumnSet = new ColumnSet(new string[] { "systemuserid" });

                RetrieveMembersTeamResponse response = (RetrieveMembersTeamResponse)crm.Execute(request);

                members = new List();

                if (response.BusinessEntityCollection != null)
                    foreach (systemuser user in response.BusinessEntityCollection.BusinessEntities)
                        members.Add(user.systemuserid.Value);
            }
            catch
            {
            }

            return members;
        }

        # region workflow dependencies (inputs and outputs)
        public static DependencyProperty dueDateProperty = DependencyProperty.Register("dueDate", typeof(CrmDateTime), typeof(AvailableUser));

        public static DependencyProperty teamProperty = DependencyProperty.Register("team", typeof(Lookup), typeof(AvailableUser));

        public static DependencyProperty userProperty = DependencyProperty.Register("user", typeof(Lookup), typeof(AvailableUser));

        [CrmInput("The estimated close date")]
        public CrmDateTime dueDate
        {
            get
            {
                return (CrmDateTime)base.GetValue(dueDateProperty);
            }
            set
            {
                base.SetValue(dueDateProperty, value);
            }
        }

        [CrmInput("The team handling this Task")]
        [CrmReferenceTarget("team")]
        public Lookup team
        {
            get
            {
                return (Lookup)base.GetValue(teamProperty);
            }
            set
            {
                base.SetValue(teamProperty, value);
            }
        }

        [CrmOutput("Available User")]
        [CrmReferenceTarget("systemuser")]
        public Lookup user
        {
            get
            {
                return (Lookup)base.GetValue(userProperty);
            }
            set
            {
                base.SetValue(userProperty, value);
            }
        }
        # endregion
    }
}