The ‘orderBy’ expression is limited to invoking the ‘ ‘ parameter

LINQ is widely used in projects and it makes querying very handy. Using LINQ for querying CRM data has some limitations though, some of the standard LINQ features are not supported to use with CRM.

One such limitation is when we are joining entities and also sorting results. To use orderby, we are limited to the first entity only (the left side entity in join). So the query in Example 1 works perfectly fine:

Example 1:

var results = from c in ContactSet
join a in AccountSet
on c.ContactId equals a.PrimaryContactId.Id
orderby c.FullName
select new
{
c.FullName,
c.Address1_City,
a.Name,
a.Address1_Name
};

But trying to sort by columns of other entity is not supported. So if we try Example 2 it will throw the following NotSupportedException

The ‘orderBy’ expression is limited to invoking the ‘c’ parameter.

Exaple 2:

var results = from c in ContactSet
join a in AccountSet
on c.ContactId equals a.PrimaryContactId.Id
orderby a.Name
select new
{
c.FullName,
c.Address1_City,
a.Name,
a.Address1_Name
};

Of course if you run similar quires directly on SQL Server database they will work fine.

Solution:

If you are getting this exception there are two possible solutions:

  • Modify your quey as per Example 1 and put orderby columns’s entity firs or on the left side of join
  • For queries involving orderby from multiple entities, select data, store it in memory and use a separate LINQ query to sort it.
    This MSDN document list limitations of LINQ for CRM

Enjoy working with CRM 🙂

Advertisements