CRM 2011组和计数 [英] CRM 2011 GROUP and COUNT

问题描述

我正在尝试对具有相同所有者名称的CRM记录进行分组，并同时获取每个GROUP的记录计数。因此，我有一个可以按计划运行的DLL，并且可以从CRM 2011中提取信息。但是，我似乎无法将其分组，也无法对每个组的记录进行计数。例如，所有拥有鲍勃所有者的记录都会说鲍勃您有X条记录。并且任何与 Ted的所有者有关的记录都会说Ted您有X条记录，等等。每个所有者都有。任何想法如何做到这一点？到目前为止，这是我所拥有的：

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.

谢谢！

推荐答案

此主题之前已经出现，但是基本的答案是您无法通过LINQ 完成此操作，但是您可以通过Microsoft的FetchXML 。实际上， GROUP BY < SDK中的/ code> 可以尽可能完美地满足您示例的要求。

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.

这篇关于CRM 2011组和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！