An exception is in a CRM LINQ query with joins. The attribute in the second table does not exist

Question

First of all, I'm sorry, because this is the second time I am writing this question, but was previously poorly explained and is now close.

I make a linq query for the search page for the CRM database, and writing a regular query as shown below does not work, I get an exception:

[System.ServiceModel.FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault>] = {"'Contact' entity doesn't contain attribute with Name = 'title'."} 

To request a connection, that in the Where clause there was something like r.Name == "Me" && & j.LastName == "He" I had to execute the request with two Where clauses because I received the same exception as indicated above, saying that table "r" does not have the LastName attribute.

 var cms = from i in aux_pr join cal in Contact on i.aux_CallerRequestorID.Id equals cal.ContactId.Value join sub in Subject on i.aux_ClassificationID.Id equals sub.SubjectId where cal.FullName.Contains(searchTerm) || sub.Title.Contains(searchTerm) 

In this case, how can I fulfill this request. Thanks in advance!

Answer 1

I want to comment on what I found out, and the solution I found for my problem can help someone. There are some limitations in CRM LINQ as described here.

The first thing I found having an entity reference:

 CrmEntityReference Caller { Guid ID; string name; } 

I can select Caller.name, but I CANNOT have Caller.name in the where clause. Solution for this -> Attach table

The second limitation is when we have joins in the query, we can have different tables if they are AND predicates, we must write two sentences, for example:

  where cal.FullName.Contains(searchTerm) where sub.Title.Contains(searchTerm) 

But the problem arises when instead of AND we need to use the OR predicate, the only solution we have is two queries and after combining these queries.

I have four requests for a call that can be executed with only one, now at the development stage the performance is good due to the number of records, but we will see how it works at the testing stage.

Answer 2

try creating two different filters.

  var cms = from i in aux_pr join cal in Contact on i.aux_CallerRequestorID.Id equals cal.ContactId.Value join sub in Subject on i.aux_ClassificationID.Id equals sub.SubjectId where cal.FullName.Contains(searchTerm) || where sub.Title.Contains(searchTerm)