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

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.

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.

Thanks for the reposonse, Jim. I have already tried the method you suggested, and I just tried your code again. It doesn't like that WHERE clause when another JOIN follows it.

I get the error "The method 'Join' cannot follow the method 'Where' or is not supported. Try writing the query in terms of supported methods or call the 'AsEnumerable' or 'ToList' method before calling unsupported methods. " An I will be using the "Leads" table, along with a lot more fields in the result set.

This is just a dumbed down version of what I am trying to do, getting the same errors no matter what. – Sean Buck Sep 1 at 18:01 Sounds like a limitation of the CRM provider unfortunately. Try providing feedback to the vendor.

If it is Microsoft, try posting on connect.microsoft. Com – Jim Wooley Sep 2 at 0:30.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.