带有 NULL 的 LINQ 查询

Question

我有一个从 CRM 2011 查询集的 LINQ 查询（抱歉；很长）。现在它会拉取数据，但它不包括带有 NULL 的数据，我希望它拉取带有和不带有 NULL 的数据。无论如何都要用这个查询来做到这一点。我有点卡住了。谢谢！

更新：我已经更新了代码以包含两个左连接以包含 NULL。但现在我收到此错误：

“‘GroupJoin’操作必须后跟‘SelectMany’操作，其中集合选择器调用‘DefaultIfEmpty’方法。”

关于如何修复错误有什么想法吗？

var linqQuery = (from r in gServiceContext.CreateQuery("opportunity")
    join c in gServiceContext.CreateQuery("contact") on ((EntityReference)r["new_contact"]).Id equals c["contactid"] into opp
    join n in gServiceContext.CreateQuery("annotation") on r["opportunityid"] equals ((EntityReference)n["objectid"]).Id into notes
    from o in opp.DefaultIfEmpty()
    from nt in notes.DefaultIfEmpty()
    where ((EntityReference)r["new_channelpartner"]).Id.Equals(lProfileProperty.PropertyValue) && ((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_sourcepick") ? string.Empty : r.FormattedValues["new_sourcepick"],
              CreatedOn = !r.Contains("createdon") ? string.Empty : ((DateTime)r["createdon"]).ToShortDateString(),
              CreatedOnSort = !r.Contains("createdon") ? string.Empty : ((DateTime)r["createdon"]).Ticks.ToString(),
              State = !o.Contains("address1_stateorprovince") ? string.Empty : ((String)o["address1_stateorprovince"]),
              Zip = !o.Contains("address1_postalcode") ? string.Empty : ((String)o["address1_postalcode"]),
              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,
              ContactStreetAddress = !o.Contains("address1_line1") ? string.Empty : o["address1_line1"],
              ContactStreetAddress2 = !o.Contains("address1_line2") ? string.Empty : o["address1_line2"],
              ContactCity = !o.Contains("address1_city") ? string.Empty : o["address1_city"],
              ContactState = !o.Contains("address1_stateorprovince") ? string.Empty : o["address1_stateorprovince"],
              ContactZip = !o.Contains("address1_postalcode") ? string.Empty : o["address1_postalcode"],
              ContactCountry = !o.Contains("address1_country") ? string.Empty : o["address1_country"],
              ContactPhone = !o.Contains("telephone1") ? string.Empty : o["telephone1"],
              ContactMobilePhone = !o.Contains("mobilephone") ? string.Empty : o["mobilephone"],
              ContactEmail = !o.Contains("emailaddress1") ? string.Empty : o["emailaddress1"],
              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(),
              DistributorStatus = !r.Contains("new_distributorstatuspicklist") ? "Unopened" : r.FormattedValues["new_distributorstatuspicklist"],
              ColderNotes = !nt.Contains("notetext") ? string.Empty : nt["notetext"],
              ColderNotesCreatedOn = !nt.Contains("createdon") ? string.Empty : ((DateTime)nt["createdon"]).ToShortDateString(),
              ColderNotesCreatedBy = !nt.Contains("createdby") ? string.Empty : ((EntityReference)nt["createdby"]).Name,
});

Answer 1

您需要将两个连接更改为 LEFT 连接。您可以按照此处的示例进行操作

还有其他语法，但这应该可以工作为你。

编辑：这是我为此使用的语法（已消毒，所以我希望我没有破坏任何重要的东西）。它有点不同，但我发现它更容易使用。希望这会有所帮助：

        var x =
            (from A in db.Table1
             from B in db.Table2
            .Where(p => p.Table1_ID == A.ID)
            .DefaultIfEmpty()
            from C in db.Table3
            .Where(c => c.Table2_ID == B.ID)
            .DefaultIfEmpty()
            where A.field1 == value1
            select new { [fieldlist] });