CRM 2011 LINQ query with multiple inner joins and a single WHERE clause (VB .NET)

I am having a great deal of difficuly trying to get LINQ queries working with CRM 2011. The problem occurs whenever I have three or more joined tables (doesn't seem to matter which CRM tables), and I try to add a WHERE clause (doesn't matter on which table I am filtering). If I remove the WHERE clause, the query runs fine and I can loop through the result set. I can also leave the WHERE clause, but remove the third join, and it also works. I have tried this with numerous CRM entities, and get the same error of "The result selector of the 'Join' operation must return an anonymous type of two properties." It appears there is a limitation that if I want to use a single WHERE clause, I am limited to joining only two tables.

I am using the early-bind CRM Context generated code method when issuing the LINQ query. This is code pulled from the CRM SDK examples, except I added the WHERE clause. I cannot find an example anywhere of how to do this.

Dim MyVar = From a In svcContext.AccountSet _
Join c In svcContext.ContactSet On a.PrimaryContactId.Id Equals c.ContactId _
Join l In svcContext.LeadSet On a.OriginatingLeadId.Id Equals l.LeadId _
Where a.Name.Contains("c") _
Select New With {c.FullName}

F开发者_运维知识库or Each MyItem In MyVar
   Debug.Print(MyItem.FullName)
Next

Thanks for any assistance!

I don't have CRM set up to test to see if it's just a limitation of their provider, but could you try the following:

Dim MyVar = From a In svcContext.AccountSet _
    Where a.Name.Contains("c") _
    Join c In svcContext.ContactSet On a.PrimaryContactId.Id Equals c.ContactId _ 
    Join l In svcContext.LeadSet On a.OriginatingLeadId.Id Equals l.LeadId _ 
    Select c.FullName

A couple comments: unlike TSQL, the order of operations in LINQ is more flexible. This way you can limit your first table before doing the join (depending on how that is translated by the CRM provider).

Second, do you want to know where "c" is anywhere in the name, or just at the start? If at the start, consider Where a.Name.StartsWith("c") One other comment, in your Select projection, you're needlessly projecting into an enumerable class. If you're just projecting a single value, you don't need the additional class overhead. Then in your foreach, just do Debug.Print(MyItem) because MyItem is the full name. Also in VB, when using the query syntax, you don't need the New With {...}. If you wanted to project an anonymous type with multiple columns you can do the following in VB just as you would in SQL:

Dim query = From c In Customers
            Select C.FullName, C.CompanyName