Tuesday, February 7, 2012

CRM 2011 LINQ Left Join Through Entity Reference

Let's say we have a custom lookup field new_accountid on our phonecall activity (don't ask me why I don't just use from/to field :)) and that field is NOT a required field.

We want to get the account number using LINQ in our code somehow.

This is the way to do it. The trick is that we have to check whether the new_accountid is null and combine those records with the ones that are null.

Hope this helps, Andreas

2 comments:

  1. var query = (from acc in svcContext.CreateQuery("account").ToList()
    join con in svcContext.CreateQuery("contact") on acc["accountid"] equals con["parentcustomerid"] into acc_con
    from con in acc_con.DefaultIfEmpty()

    select new
    {
    AccountName = acc["name"] != null ? acc["name"] : null,

    ContactName = con != null ? con["fullname"] : null

    }).ToList();


    foreach (var a in query)
    {
    System.Console.WriteLine(a.AccountName + " " + a.ContactName);
    System.Console.ReadLine();
    }

    I Couldn't get the Contact Information from the above code. Could you please let me know the issue for this code?

    Thanks,
    Ganesh.J.

    ReplyDelete
  2. I think my code is specific to activity party, you just have to do normal simple linq to do what you want:

    var query = (from acc in desService.CreateQuery("account")
    join con in desService.CreateQuery("contact") on acc["accountid"] equals con["parentcustomerid"]

    select new
    {
    AccountName = acc["name"] != null ? acc["name"] : null,

    ContactName = con != null ? con["fullname"] : null

    }).ToList().Take(5);


    foreach (var a in query)
    {
    System.Console.WriteLine(a.AccountName + " " + a.ContactName);
    }
    System.Console.ReadLine();

    ReplyDelete