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.
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.