CRM 2011 GROUP and COUNT

Question

I am trying to GROUP the CRM records that have the same "Owner" name and also get a count of the records for each GROUP. So I have a DLL that runs on a schedule and pulls the info down from CRM 2011. But I can't seem to get it to group and do a count of the records for each group. For example all the records with the Owner of "Bob" will say Bob you have X records. And any records with the Owner of "Ted" would say Ted you have X records, etc. For every owner there is. Any idea how to do this? This is what I have so far:

              var linqQuery = (from r in orgServiceContext.CreateQuery("opportunity")
                             join c in orgServiceContext.CreateQuery("systemuser") on ((EntityReference)r["ownerid"]).Id equals c["systemuserid"] into opp
                             from o in opp.DefaultIfEmpty()
                             //where ((OptionSetValue)r["new_leadstatus"]).Equals("100000002")
                             select new

                             {
                                 OpportunityId = !r.Contains("opportunityid") ? string.Empty : r["opportunityid"],
                                 CustomerId = !r.Contains("customerid") ? string.Empty : ((EntityReference)r["customerid"]).Name,
                                 Priority = !r.Contains("opportunityratingcode") ? string.Empty : r.FormattedValues["opportunityratingcode"],
                                 ContactName = !r.Contains("new_contact") ? string.Empty : ((EntityReference)r["new_contact"]).Name,
                                 Source = !r.Contains("new_source") ? string.Empty : ((String)r["new_source"]),
                                 CreatedOn = !r.Contains("createdon") ? string.Empty : ((DateTime)r["createdon"]).ToShortDateString(),
                                 Eval = !r.Contains("new_distributorevaluation") || ((OptionSetValue)r["new_distributorevaluation"]).Value.ToString() == "100000000" ? "NA" : r.FormattedValues["new_distributorevaluation"].Substring(0, 2),
                                 EvalVal = !r.Contains("new_distributorevaluation") ? "100000000" : ((OptionSetValue)r["new_distributorevaluation"]).Value.ToString(),
                                 DistributorName = !r.Contains("new_channelpartner") ? string.Empty : ((EntityReference)r["new_channelpartner"]).Name,
                                 Notes = !r.Contains("new_distributornotes") ? string.Empty : r["new_distributornotes"],
                                 EstimatedCloseDate = !r.Contains("estimatedclosedate") ? string.Empty : r["estimatedclosedate"],
                                 MaturityValue = !r.Contains("estimatedvalue") ? string.Empty : ((Money)r["estimatedvalue"]).Value.ToString(),
                                 SentToDistributorOn = !r.Contains("new_senttodistributoron") ? DateTime.MinValue.ToShortDateString() : ((DateTime)r["new_senttodistributoron"]).ToShortDateString(),
                                 LeadStatus = !r.Contains("new_leadstatus") ? string.Empty : ((OptionSetValue)r["new_leadstatus"]).Value.ToString(),
                                 EmailedToRSM = !r.Contains("new_emailedtorsm") ? string.Empty : r.FormattedValues["new_emailedtorsm"],
                                 EmailedToDistributor = !r.Contains("new_emailedtodistributor") ? string.Empty : r.FormattedValues["new_emailedtodistributor"],
                                 Owner = !r.Contains("ownerid") ? string.Empty : ((EntityReference)r["ownerid"]).Name,
                                 OwnerEmail = !o.Contains("internalemailaddress") ? string.Empty : ((String)o["internalemailaddress"]),
                             }).ToArray();

            foreach (var distributor in linqQuery)
            {



                DateTime dtCreatedOn = Convert.ToDateTime(distributor.CreatedOn);
                DateTime dtSentOn = Convert.ToDateTime(distributor.SentToDistributorOn);

                // New Lead Notification
                    if (((distributor.Owner.ToString() == distributor.Owner.ToString()) && (DateTime.Now - dtCreatedOn).Days <= 1) && (distributor.LeadStatus == "100000000") && ((distributor.EmailedToRSM == "No") || (distributor.EmailedToRSM == null)) && (String.IsNullOrEmpty(distributor.Owner.ToString()) == false))
                    {

                        int count = 0;
                        count = count + 1;
                        string lBodyHTML = "";

                        lBodyHTML = "You have " + distributor.CustomerId.ToString() + " " + distributor.CreatedOn.ToString() + " " + count.ToString() + " new leads. Please review them for follow up or assignment.";

                        string smtpServer = Convert.ToString(Globals.HostSettings["SMTPServer"]);
                        string smtpAuthentication = Convert.ToString(Globals.HostSettings["SMTPAuthentication"]);
                        string smtpUsername = Convert.ToString(Globals.HostSettings["SMTPUsername"]);
                        string smtpPassword = Convert.ToString(Globals.HostSettings["SMTPPassword"]);
                        string xResult = Mail.SendMail("[e-mail]", "[e-mail]", "", "", MailPriority.High, "You have X new leads", MailFormat.Html, System.Text.Encoding.UTF8, lBodyHTML, "", smtpServer, smtpAuthentication, smtpUsername, smtpPassword);

                    }

Any help would be awesome. I'm stuck right now.

Thanks!

Answer 1

This topic has come up before, but the basic answer is that you can't do this via LINQ, but you can via Microsoft's FetchXML. In fact, the first example on GROUP BY in the SDK addresses the requirements of your example about as perfectly as any SDK can.

Answer 2

Try this:

var linqQuery =
    from r in orgServiceContext.CreateQuery("opportunity")
    join c in orgServiceContext.CreateQuery("systemuser") on ((EntityReference)r["ownerid"]).Id equals c["systemuserid"]
    group r by ((EntityReference)r["ownerid"]).Id into oop
    select new
    {
      Key = oop.Key,
      Count = oop.Count(),
      Opportunities = oop //contains the list of opportunities grouped by OwnerId
    };

Opportunities contains a collection of IGrouping You must iterate through the collection IEnumerable<Microsoft.Xrm.Sdk.Entity> to create the list of opportunities with the members you are interested. (the projection you made by using the new operator)

var outputOpportunities = new ArrayList();
foreach (IGrouping<Guid, Microsoft.Xrm.Sdk.Entity> group in linqQuery)
{
    foreach (Microsoft.Xrm.Sdk.Entity opportunity in group)
        outputOpportunities.Add
        (new
            {
                OpportunityId = !opportunity.Contains("opportunityid") ? string.Empty : opportunity["opportunityid"],
                CustomerId = !opportunity.Contains("customerid") ? string.Empty : ((EntityReference)opportunity["customerid"]).Name,
                Priority = !opportunity.Contains("opportunityratingcode") ? string.Empty : opportunity.FormattedValues["opportunityratingcode"],
                ContactName = !opportunity.Contains("new_contact") ? string.Empty : ((EntityReference)opportunity["new_contact"]).Name,
                Source = !opportunity.Contains("new_source") ? string.Empty : ((String)opportunity["new_source"]),
                CreatedOn = !opportunity.Contains("createdon") ? string.Empty : ((DateTime)opportunity["createdon"]).ToShortDateString(),
                Eval = !opportunity.Contains("new_distributorevaluation") || ((OptionSetValue)opportunity["new_distributorevaluation"]).Value.ToString() == "100000000" ? "NA" : opportunity.FormattedValues["new_distributorevaluation"].Substring(0, 2),
                EvalVal = !opportunity.Contains("new_distributorevaluation") ? "100000000" : ((OptionSetValue)opportunity["new_distributorevaluation"]).Value.ToString(),
                DistributorName = !opportunity.Contains("new_channelpartner") ? string.Empty : ((EntityReference)opportunity["new_channelpartner"]).Name,
                Notes = !opportunity.Contains("new_distributornotes") ? string.Empty : opportunity["new_distributornotes"],
                EstimatedCloseDate = !opportunity.Contains("estimatedclosedate") ? string.Empty : opportunity["estimatedclosedate"],
                MaturityValue = !opportunity.Contains("estimatedvalue") ? string.Empty : ((Money)opportunity["estimatedvalue"]).Value.ToString(),
                SentToDistributorOn = !opportunity.Contains("new_senttodistributoron") ? DateTime.MinValue.ToShortDateString() : ((DateTime)opportunity["new_senttodistributoron"]).ToShortDateString(),
                LeadStatus = !opportunity.Contains("new_leadstatus") ? string.Empty : ((OptionSetValue)opportunity["new_leadstatus"]).Value.ToString(),
                EmailedToRSM = !opportunity.Contains("new_emailedtorsm") ? string.Empty : opportunity.FormattedValues["new_emailedtorsm"],
                EmailedToDistributor = !opportunity.Contains("new_emailedtodistributor") ? string.Empty : opportunity.FormattedValues["new_emailedtodistributor"],
                Owner = !opportunity.Contains("ownerid") ? string.Empty : ((EntityReference)opportunity["ownerid"]).Name,
                OwnerEmail = !opportunity.Contains("internalemailaddress") ? string.Empty : ((String)opportunity["internalemailaddress"])
            }
        );
}        

Basically i took your query and added the group by operator and in the projection statement i put the count by ownerid.