Microsoft Dynamics 2011 N:N LINQ query with where clause containing Guid

Question

I have constructed a simple query to return a Users Team membership (N: N relationship). This works fine for all users, however, when I add a where clause to restrict to a specific user it throws a fault exception (see stack trace below).

Strangely this works fine with "where Users.FullName.StartsWith("Alex")". Does the Dynamics CRM SDK LINQ implementation not support Guides in where clauses?

Any advice?

Example code

 using (var service = new OrganizationService("Xrm"))
        {
            using (var xrm = new XrmServiceContext(service))
            {
                var AlexUser = xrm.SystemUserSet.Where(p => p.FullName.StartsWith("Alex")).First();
                var AlexID = AlexUser.Id;

                var Test =
                        from Users in xrm.SystemUserSet
                        join TeamMemberships in xrm.TeamMembershipSet on Users.Id equals TeamMemberships.SystemUserId
                        join Teams in xrm.TeamSet on TeamMemberships.TeamId equals Teams.Id
                        where Users.Id == AlexID     // <-- problematic where clause
                        orderby Users.FullName
                        select new
                        {
                            FullName = Users.FullName,
                            UserID = Users.Id,
                            TeamName = Teams.Name
                        };

                var Test1 = Test.ToList();
            }
        }

Stacktrace:

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.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs) 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.Client.Services.OrganizationService.<>c__DisplayClass19.b__18(IOrganizationService s) at Microsoft.Xrm.Client.Services.OrganizationService.InnerOrganizationService.UsingService[TResult](Func2 action) at Microsoft.Xrm.Client.Services.OrganizationService.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.GetEnumerator[TElement](Expression expression) at Microsoft.Xrm.Sdk.Linq.Query1.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at aspirets.crm.test.Program.Main(String[] args) in C:\Users\a_marshall\documents\visual studio 2010\Projects\aspirets.crm\aspirets.crm.test\Program.cs:line 37 at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart()

Hi Peter, thank you very much this worked! If you post this solution as an answer rather than a comment then I'll mark the question as Accepted. — Alex Marshall

Peter Majeed Peter Majeed · Accepted Answer · 2012-04-24T14:07:20

Instead of Users.Id, try Users.SystemUserId. Similarly, instead of Teams.Id, try Teams.TeamId.

As for the reason why this works, I'm not aware of any documentation that states this, but because entities in the generated early-binding file inherit from Entity, they necessarily have an Id property. However, because the early-bound OrganizationServiceContext maps entity attributes directly to the CRM database, the tables of which don't contain an Id column, using the Id attribute with the LINQ provider won't work, so you'd have to use the actual database/schema names.

Microsoft Dynamics 2011 N:N LINQ query with where clause containing Guid

1 Answers