DynamicsCRM QueryExpression Criteria inside an EntityReference

Question

I'm Using the CRM SDK from PowerShell, Trying to query a contract linked to specific Account name, If I do the query against Account GUID I get the results, but I want to query against the name inside the EntityReference object, and not the GUID, for example (this works fine):

$query = new-object Microsoft.Xrm.Sdk.Query.QueryExpression('new_contract')
$query.Criteria.AddCondition('new_account', [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Equal, $AccountGuid)

The Above results looks like that:

Key                 Value
---                 -----
createdby           Microsoft.Xrm.Sdk.EntityReference
createdon           21/10/2014
modifiedby          Microsoft.Xrm.Sdk.EntityReference
modifiedon          28/02/2016
modifiedonbehalfby  Microsoft.Xrm.Sdk.EntityReference
new_account         Microsoft.Xrm.Sdk.EntityReference
[...]               [...]

The new_account Properties are:

Id            : dab2909d-6149-e411-93fc-005056af5481
LogicalName   : account
Name          : CustomerNameText
KeyAttributes : {}
RowVersion    : 
ExtensionData : System.Runtime.Serialization.ExtensionDataObject

I want to create a Query for new_contract with new_account Name LIKE CustomerNameText

I tried:

  $query.Criteria.AddCondition('new_account',([Microsoft.Xrm.Sdk.EntityReference].Attributes['new_account']).Name, [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Like, $AccountName)

But it not works get the following error:

Exception calling "RetrieveMultiple" with "1" argument(s): "Link entity with name or alias new_account is not found"
At line:2 char:5
+     $temp = $service.RetrieveMultiple($query);
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FaultException`1

I hope i'm clear, but if not, comment with any question.

Powershell code is preferable, c# is ok as well

Thanks...

Answer 1

You cannot use QueryExpression criteria for EntityReference name. With EntityReference you can only use Guid.

If you want to Query by name, you can use LinkEntity in QueryExpression. You will have to use LinkCriteria

var q = new QueryExpression();
            q.EntityName = "new_contract";
            q.ColumnSet = new ColumnSet();
            q.ColumnSet.Columns.Add("new_contractid");

            q.LinkEntities.Add(new LinkEntity("new_contract", "account", "new_account", "accountid", JoinOperator.Inner));
            q.LinkEntities[0].Columns.AddColumns("accountid", "name");
            q.LinkEntities[0].EntityAlias = "temp";
            q.LinkEntities[0].LinkCriteria.AddCondition("name", ConditionOperator.Equal, "yourAccountName");

P.SIf the above snippet, is not working for you. And you want to retrieve by accountName. On way to do this is use your existing code using Guid of account. But first you have to retrieve accountGuid by name. In that case

  public Guid getAccountGuidByName(string name)
    {

        QueryExpression accountQuery = new QueryExpression("account");
        accountQuery.Criteria.AddCondition("name", ConditionOperator.Equal, "yourAccountName");
        EntityCollection entCol = Service.RetrieveMultiple(accountQuery);
        if (entCol.Entities.Count > 0)
        {
            return entCol.Entities[0].Id;
        }
       return Guid.Empty;

    }

just for example i don't know how can you call function etc in powershell but i am providing c# code, as you said its ok if it in C# then use it like below :

$query = new-object Microsoft.Xrm.Sdk.Query.QueryExpression('new_contract')
$query.Criteria.AddCondition('new_account', [Microsoft.Xrm.Sdk.Query.ConditionOperator]::Equal, getAccountGuidByName("yourAccountName"));