Linq to CRM (early bound) join statement throws exception when lambda expression doesn't

Question

Is there a bug in the Microsoft linq to CRM provider, or am I doing something that linqToCrm doesn't support?

I have a simple function that determines if the user is assigned a role which doesn't work.

public static bool IsSystemUserInRole(Guid systemUserId,
                                      string roleName,
                                      Microsoft.Xrm.Sdk.IOrganizationService service)
{
    using (var crmService = new CrmContext(service))
    {
        return (from sr in crmService.SystemUserRolesSet
                join r in crmService.RoleSet
                    on sr.RoleId.Value equals r.RoleId.Value
                where sr.SystemUserId.Value == systemUserId && r.Name == roleName
                select sr.SystemUserId).FirstOrDefault() != null;
    }
}

But strangely enough, if I rewrite it as two lambda expressions, it works fine.

public static bool IsSystemUserInRole(Guid systemUserId,
                                      string roleName,
                                      Microsoft.Xrm.Sdk.IOrganizationService service)
{
    using (var crmService = new CrmContext(service))
    {
        var role = crmService.RoleSet.FirstOrDefault(r => r.Name == roleName);
        return role != null 
                && crmService.SystemUserRolesSet.FirstOrDefault(
                    ur => ur.SystemUserId == systemUserId
                          && ur.RoleId == role.RoleId) != null;
    }
}

The Exception is

System.ServiceModel.FaultException`1[Microsoft.Xrm.Sdk.OrganizationServiceFault]: 'SystemUserRoles' entity doesn't contain attribute with Name = 'name'. (Fault Detail is equal to Microsoft.Xrm.Sdk.OrganizationServiceFault).

and the stack trace is

Server stack trace: at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation) at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at Microsoft.Xrm.Sdk.IOrganizationService.Execute(OrganizationRequest request) at Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy.ExecuteCore(OrganizationRequest request) at Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy.Execute(OrganizationRequest request) at Microsoft.Xrm.Sdk.Client.OrganizationServiceContext.Execute(OrganizationRequest request) at Microsoft.Xrm.Sdk.Linq.QueryProvider.RetrieveEntityCollection(OrganizationRequest request, NavigationSource source) at Microsoft.Xrm.Sdk.Linq.QueryProvider.Execute(QueryExpression qe, Boolean throwIfSequenceIsEmpty, Boolean throwIfSequenceNotSingle, Projection projection, NavigationSource source, List1 linkLookups, String& pagingCookie, Boolean& moreRecords) at Microsoft.Xrm.Sdk.Linq.QueryProvider.Execute[TElement](QueryExpression qe, Boolean throwIfSequenceIsEmpty, Boolean throwIfSequenceNotSingle, Projection projection, NavigationSource source, List1 linkLookups) at Microsoft.Xrm.Sdk.Linq.QueryProvider.Execute[TElement](Expression expression) at Microsoft.Xrm.Sdk.Linq.QueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source) at CRM.Business.IntegrationServices.SystemUserService.IsSystemUserInRole(Guid systemUserId, String roleName, IOrganizationService service) at CRM.Plugin.OnExecute(IServiceProvider provider)

Would you believe I wrote a LINQ query today where I tried including two entities in the where statement and only figured out what to do by googling the error and finding this question? Amazing. — Peter Majeed
@PeterMajeed That's the beauty of StackOverflow... Everyone helping everyone. — Daryl
And the horror of certain Microsoft design decisions... :-) — Peter Majeed
vote (to tell microsoft) to correct this -> connect.microsoft.com/dynamicssuggestions/feedback/details/… — Eric Labashosky

Peter Majeed Peter Majeed · Accepted Answer · 2011-10-17T18:59:37

Where statements from different entities need to be introduced in separate where statements.

The where clause applies a filter to the results, often using a Boolean expression. The filter specifies which elements to exclude from the source sequence. Each where clause can only contain conditions against a single entity type. A composite condition involving multiple entities is not valid. Instead, each entity should be filtered in separate where clauses.

The below should probably take care of it.

public static bool IsSystemUserInRole(Guid systemUserId,
                                      string roleName,
                                      Microsoft.Xrm.Sdk.IOrganizationService service)
{
    using (var crmService = new CrmContext(service))
    {
        return (from sr in crmService.SystemUserRolesSet
                join r in crmService.RoleSet
                    on sr.RoleId.Value equals r.RoleId.Value
                where sr.SystemUserId.Value == systemUserId
                where r.Name == roleName
                select sr.SystemUserId).FirstOrDefault() != null;
    }
}

Linq to CRM (early bound) join statement throws exception when lambda expression doesn't

1 Answers