0

I have following query in LINQ to LEFT JOIN same entity:

  query = from a in orgSvcContext.CreateQuery("entity1")
            join b in orgSvcContext.CreateQuery("entity1")
            on new { v1 = a["field1"], v2 = a["field2"] } equals new { v1 = b["field2"], v2 = b["field1"] } into gr
            from c_joined in gr.DefaultIfEmpty()
            where c_joined["field0"] == null && (a["field1"].Equals(new Guid(@param)) || a["field2"].Equals(new Guid(@param)))
            select a;

It complains the following error:

invalid 'join' condition. an entity member is invoking an invalid property or method

I refer from here: https://learn.microsoft.com/en-us/dotnet/csharp/linq/join-by-using-composite-keys

Anything wrong with my JOIN? I have no problem if I just use

on a["field1"] equals  b["field2"]

Thanks in advance.

Selim Yildiz
  • 5,254
  • 6
  • 18
  • 28
howexg9
  • 93
  • 1
  • 6

1 Answers1

0

The new { v1 = a["field1"], v2 = a["field2"] } construct cannot be resolved to an attribute name.

The LINQ implementation for Dynamics CRM is very limited as it attempts to convert LINQ expressions into QueryExpression queries. These queries in turn only implement a basic subset of the SQL language.

When joining two entities make shure that after the on the left and right side can be resolved to existing lookup attributes of both entities.

Still keep in mind not all QueryExpression capabilities can be used in LINQ queries.

Henk van Boeijen
  • 7,357
  • 6
  • 32
  • 42
  • I read a thread before. It mentioned FetchXml and QueryExpress don't support composite key JOIN, but LINQ does in Dynamic CRM. That's why I use LINQ. – howexg9 May 02 '21 at 14:24
  • https://stackoverflow.com/questions/4046711/are-composite-joins-possible-using-fetchxml-in-microsoft-dynamics-crm-4-0 – howexg9 May 03 '21 at 14:28
  • Ah, I get it. OP of that thread writes he uses LINQ to join the results of two queries. These two resultsets must have been materialized collections, so the join can take full advantage of LINQ for objects. In that case LINQ for CRM is not invoked at all. In your case you are directly joining on the `IQueryable` created on the `OrganizationServiceContext` and that's why LINQ for CRM steps in, with all of its limitations. – Henk van Boeijen May 03 '21 at 20:32