LINQ where clause throwing error with 2nd where

问题:

UPDATE

Even doing a search on the contacts firstName or LastName causes issues:

var contacts =
            (
                from c in context.ContactSet
                join m in context.py3_membershipSet on c.ContactId equals m.py3_Member.Id
                where m.statuscode.Value == 1
                && ((c.FirstName != null && c.FirstName == searchTerm) || (c.LastName!=null && c.LastName == searchTerm) || (c.FullName != null && c.FullName == searchTerm))  
                orderby c.LastName
                select new
                {
                    ContactId = c.ContactId,
                    FirstName = c.FirstName,
                    LastName = c.LastName,
                    BranchCode = c.py3_BranchArea,
                    Branch = (c.FormattedValues != null && c.FormattedValues.Contains("py3_brancharea") ? c.FormattedValues["py3_brancharea"] : "N/a"),
                    JobTitle = c.JobTitle,
                    Organisation = (c.ParentCustomerId != null ? c.ParentCustomerId.Name : "N/a"),
                    joinedAsCode = c.py3_SOLACEMemberJoinedAs,
                    JoinedAs = (c.FormattedValues != null && c.FormattedValues.Contains("py3_solacememberjoinedas") ? c.FormattedValues["py3_solacememberjoinedas"] : "N/a"),
                    Expertise = (c.py3_SOLACEMemberAreasofExpertise != null && c.py3_SOLACEMemberAreasofExpertise.Trim() != String.Empty ? c.py3_SOLACEMemberAreasofExpertise : "N/a"),
                    Title = c.Salutation
                }
            );

Which screams back:

'py3_membership' entity doesn't contain attribute with Name = 'firstname'.

---

Ive got the following code that het some info from an Online CRM:

        var context = new XrmServiceContext();
    var contacts1 =
        (
            from c in context.ContactSet
            join m in context.py3_membershipSet on c.ContactId equals m.py3_Member.Id
            where m.statuscode.Value == 1

            orderby c.LastName
            select new
            {
                ContactId = c.ContactId,
                FirstName = c.FirstName,
                LastName = c.LastName,
                BranchCode = c.py3_BranchArea,
                Branch = (c.FormattedValues != null && c.FormattedValues.Contains("py3_brancharea") ? c.FormattedValues["py3_brancharea"] : "N/a"),
                JobTitle = c.JobTitle,
                Organisation = (c.ParentCustomerId != null ? c.ParentCustomerId.Name : "N/a"),
                joinedAsCode = c.py3_SOLACEMemberJoinedAs,
                JoinedAs = (c.FormattedValues != null && c.FormattedValues.Contains("py3_solacememberjoinedas") ? c.FormattedValues["py3_solacememberjoinedas"] : "N/a"),
                Expertise = (c.py3_SOLACEMemberAreasofExpertise != null && c.py3_SOLACEMemberAreasofExpertise.Trim() != String.Empty ? c.py3_SOLACEMemberAreasofExpertise : "N/a")
            }
        );

I then bind this to a datalist as an array, which all works fine.

However I want to be able to limit the results to a value selected from a dropdownlist, and expected the following to work:

        var context = new XrmServiceContext();
    var contacts1 =
        (
            from c in context.ContactSet
            join m in context.py3_membershipSet on c.ContactId equals m.py3_Member.Id
            where m.statuscode.Value == 1 &&
                c.FormattedValues["py3_brancharea"] == ddlBranchTags.SelectedItem.Value


            orderby c.LastName
            select new
            {
                ContactId = c.ContactId,
                FirstName = c.FirstName,
                LastName = c.LastName,
                BranchCode = c.py3_BranchArea,
                Branch = (c.FormattedValues != null && c.FormattedValues.Contains("py3_brancharea") ? c.FormattedValues["py3_brancharea"] : "N/a"),
                JobTitle = c.JobTitle,
                Organisation = (c.ParentCustomerId != null ? c.ParentCustomerId.Name : "N/a"),
                joinedAsCode = c.py3_SOLACEMemberJoinedAs,
                JoinedAs = (c.FormattedValues != null && c.FormattedValues.Contains("py3_solacememberjoinedas") ? c.FormattedValues["py3_solacememberjoinedas"] : "N/a"),
                Expertise = (c.py3_SOLACEMemberAreasofExpertise != null && c.py3_SOLACEMemberAreasofExpertise.Trim() != String.Empty ? c.py3_SOLACEMemberAreasofExpertise : "N/a")
            }
        );

However, this throws the following error:

Invalid 'where' condition. An entity member is invoking an invalid property or method.

Its the same even if I hard code the branchtag criteria rather than going of the DDL value. Ive also tried doing a select on the contacts1 set eg:

var results = contacts1.select(c=> c.BranchTag == ddlBranchTags.SelectedItem.Value

But that throws the same error.

If I remove the branchTag where clause it works as expected. I think its fair to assume that Ive gone wayward with this, so any useful / constructive pointers (for a LINQ newb) would be really appreciated. Thanks.

回答1:

LINQ-to-CRM is actually fairly limited in terms of the expression forms it can support. If you consider what it's doing behind the scenes – taking your entire LINQ expression and translating it into a CRM QueryExpression – this makes more sense.

Basically, it's not valid to expect to be able to embed arbitrary C# into your query (even if it compiles), as it's not possible to translate all code into a CRM query. The query provider could hypothetically be smart enough to determine what it can submit as a query to CRM, and what code it then has to execute client-side to get your final desired results, but that's generally a pretty hard problem – one which the LINQ provider does not attempt to solve.

In Where clauses specifically, the LINQ provider scans the expressions for basic forms of <attribute name accessor> <simple comparison> <value>. It understands early-bound codegen attribute accessors (entity.Attribute_name), indexer access (entity["attribute_name"]), and entity.GetAttributeValue<T>("attribute_name") access. Those are the only things you can use on the left side of a comparision in a Where clause (or in an OrderBy clause). In your second query, you're accessing FormattedValues, which the query provider doesn't understand/support.

An additional limitation is that a single Where clause can only address one "target" entity. So the case where you have filters on both "m" and "c" in a single predicate is not supported. I'm less clear on the reasons for this limitation, but that's how it is. You can still accomplish the equivalent filter by splitting the two conditions (joined by an && in your current query) into two separate Where clauses.

It's natural to want to write LINQ-to-CRM queries as though you are using LINQ-to-Objects, but doing so will generally be frustrating. LINQ-to-CRM is somewhat limited, but many of those limitations are inherited from the underlying CRM SDK query systems it's built upon. It's still a decent API for writing CRM queries, as long as you keep the limitations in mind.

If you're writing a query that is going to require you to execute some arbitrary code to filter/order/map your results, the general technique that should be used is to separate your query into two parts: the query that should be submitted to CRM, and your aribitrary code transformation. You write the most data-efficient query you can for getting your CRM data, force evaluation of the query using something like ToList or ToArray (as LINQ is otherwise lazily-evaluated), and then you proceed with your code on the results of that evaluation.

回答2:

This looks to be a limitation of the CRM LINQ Provider.

where: The left side of the clause must be an attribute name and the right side of the clause must be a value. You cannot set the left side to a constant. Both the sides of the clause cannot be constants. Supports the String functions Contains, StartsWith, EndsWith, and Equals.

(Emphasis mine)

It probably has to do with indexing into the FormattedValues property of your Contact Set. You can try using a let clause and see if that helps.

EDIT: Here's an example of how to use let. I'm not sure if this will actually help - I can't test it at the moment.

from c in context.ContactSet
let formattedValue = c.FormattedValues["py3_brancharea"]
join m in context.py3_membershipSet on c.ContactId equals m.py3_Member.Id
where m.statuscode.Value == 1 &&
    formattedValue == ddlBranchTags.SelectedItem.Value

Notice how I let formattedValue be the value from c.FormattedValue["py3_brancharea"] and then use that in the where clause. It might get you past the limitation.

回答3:

I believe you can get around this issue by using method syntax for where instead of query syntax.

var context = new XrmServiceContext();
var contacts1 =
(
    from c in context.ContactSet.Where(c => c.FormattedValues["py3_brancharea"] == ddlBranchTags.SelectedItem.Value)
    join m in context.py3_membershipSet.Where(m => m.statuscode.Value == 1)
    on c.ContactId equals m.py3_Member.Id
    // ...
);

More insight on using method syntax. If you prefer a solution that lets you use query syntax, you can use LINQKit as explained here. As an aside, the LINQKit answer mentions that join gets performed on the client side, so you might want to change it to a where clause.

回答4:

Based on @Peter Majeed's answer, I solved my problem by using the double where clause:

var contacts =
            (
                from c in context.ContactSet
                join m in context.py3_membershipSet on c.ContactId equals m.py3_Member.Id
                where m.statuscode.Value == 1
                where ((c.FirstName != null && c.FirstName == searchTerm) || (c.LastName!=null && c.LastName == searchTerm) || (c.FullName != null && c.FullName == searchTerm))  
                orderby c.LastName
                select new
                {
                    ContactId = c.ContactId,
                    FirstName = c.FirstName,
                    LastName = c.LastName,
                    BranchCode = c.py3_BranchArea,
                    Branch = (c.FormattedValues != null && c.FormattedValues.Contains("py3_brancharea") ? c.FormattedValues["py3_brancharea"] : "N/a"),
                    JobTitle = c.JobTitle,
                    Organisation = (c.ParentCustomerId != null ? c.ParentCustomerId.Name : "N/a"),
                    joinedAsCode = c.py3_SOLACEMemberJoinedAs,
                    JoinedAs = (c.FormattedValues != null && c.FormattedValues.Contains("py3_solacememberjoinedas") ? c.FormattedValues["py3_solacememberjoinedas"] : "N/a"),
                    Expertise = (c.py3_SOLACEMemberAreasofExpertise != null && c.py3_SOLACEMemberAreasofExpertise.Trim() != String.Empty ? c.py3_SOLACEMemberAreasofExpertise : "N/a"),
                    Title = c.Salutation
                }
            );

Essentially the answer is to split each logical 'chunk' of the query with its own where clause. It would appear this allows LINQ to make one query based on the first 'where' clause and then do a secondary filter on that via the second 'where' clause.