CRM 2011 组和计数

Question

我正在尝试对具有相同“所有者”名称的 CRM 记录进行分组，并获取每个组的记录计数。因此，我有一个按计划运行并从 CRM 2011 中提取信息的 DLL。但我似乎无法对其进行分组并对每个组的记录进行计数。例如，所有者为“Bob”的所有记录都会说 Bob 您有 X 条记录。任何拥有“Ted”的记录都会说 Ted 您有 X 条记录，等等。对于每个所有者都有。知道如何做到这一点吗？这就是我到目前为止所得到的：

              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("[email protected]", "[email protected]", "", "", MailPriority.High, "You have X new leads", MailFormat.Html, System.Text.Encoding.UTF8, lBodyHTML, "", smtpServer, smtpAuthentication, smtpUsername, smtpPassword);

                    }

任何帮助都会很棒。我现在被困住了。

谢谢！

Answer 1

本主题之前已经出现过，但基本答案是您无法通过 LINQ 执行此操作 ，但是 您可以通过 Microsoft 的 FetchXML。事实上，GROUP BY 上的第一个示例SDK 中的内容与任何 SDK 一样完美地满足了示例的要求。

Answer 2

试试这个：

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 包含 IGrouping 的集合
您必须迭代 IEnumerable 集合来创建您感兴趣的成员的机会列表。 （您使用 new 运算符进行的投影）

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"])
            }
        );
}        

基本上，我接受了您的查询并添加了 group by 运算符，并在投影语句中我按ownerid 放置了计数。