Linq predicate query results is not working for further Linq Join

706 views Asked by At

I am using results from Predicate query to write join in Linq query. But it is throwing the following error. However when I run similar query in SQL, it returns the expected results.

var predicate = PredicateBuilder.False<Product>();
foreach (var productId in productIds)
{
    var tempGuid = productId;
    predicate = predicate.Or(p => p.ProductId== tempGuid.ToString());
}

// This query is returning products back
var products = from p in context.ProductSet.AsExpandable().Where(predicate)
                           select p;

var accounts = (from a in context.AccountSet
                join cl in context.ContractDetailSet 
                on a.AccountId equals cl.AccountId.Id
                join p in products on  \\ From predicate query
                cl.ProductId.Id equals p.ProductId
                where a.StateCode == AccountState.Active                    
                select a).ToList();

Note: I have removed the ToList() in the end and it does not throw the error then, but when you try to use the accounts object then it throws the same error again.

Error

An exception of type 'System.NullReferenceException' occurred in Microsoft.Xrm.Sdk.dll but was not handled in user code.

Additional information: Object reference not set to an instance of an object.

1

There are 1 answers

0
Scorpion On BEST ANSWER

Following works for me perfectly, Replace Linq with QueryExpression.

var qe = new QueryExpression {EntityName = "account", ColumnSet = new ColumnSet()};
qe.ColumnSet.Columns.Add("name");

qe.LinkEntities.Add(new LinkEntity("account", "contractdetail", "accountid", "accountid", JoinOperator.Inner));
qe.LinkEntities[0].Columns.AddColumns("productid", "title");
qe.LinkEntities[0].EntityAlias = "contractdetails";

// Check Account State
FilterExpression accountState = qe.Criteria.AddFilter(LogicalOperator.And);
accountState.Conditions.Add(new ConditionExpression("statecode", ConditionOperator.Equal, 0));

FilterExpression productFilter = qe.LinkEntities[0].LinkCriteria.AddFilter(LogicalOperator.Or);
foreach (var product in products)
{
    var condition = new ConditionExpression
    {
        AttributeName = "productid",
        Operator = ConditionOperator.Equal
    };
    condition.Values.Add(product.ProductId);
    productFilter.Conditions.Add(condition); 
}                

EntityCollection resultsCollection = _OrgService.RetrieveMultiple(qe);

For more details about QueryExpression check the links below.

  1. Retrieve multiple with the QueryExpression
  2. Use the ConditionExpression