Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / LINQ - where on two en...
Microsoft Dynamics CRM (Archived)

LINQ - where on two entities

(0) ShareShare
ReportReport
Posted on by EnriqueMdz 1,065

Hi all,

I have two custom entities(IE: A and B) on which I need to filter based on an specific field within both Entities. Please see the example below:

DateTime PresentDate = DateTime.Now.Date;
OptionSetValue Active = new OptionSetValue(753290000);
 var Query = (from A in LINQContext.EntityASet
                                                join B in LINQContext.EntityBSet
                                                on A.Customer.Id equals B.Id
                                                where A.Country == B.Country
                                                where A.ModifyOn > PresentDate
                                                where A.StateCode != Active
                                                orderby A.ModifiedOn descending
                                                select A.Id).ToList();


Basically, I want to get all A records that have the same country that the B records.  However, I'm getting the next error:

variable '<>h__TransparentIdentifier0' of type '<>f__AnonymousType0`2[Plugin.EntityA, Plugin.EntityB]' referenced from scope '', but it is not defined

Does anybody know why this happens or a workaround?

Thanks in advance!

*This post is locked for comments

  • Verified answer
    CrmLeo Profile Picture
    CrmLeo 35 on at
    RE: LINQ - where on two entities

    @Enrique: You may need to find a different approach (i.e. breaking your LINQ queries into 2 queries etc...), as your LINQ query above fails because of the LINQ limitations. Per Microsoft's documentation: "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." is the limitation of the where operator - [View:https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/org-service/use-linq-construct-query:750:50]

  • EnriqueMdz Profile Picture
    EnriqueMdz 1,065 on at
    RE: LINQ - where on two entities

    Hi Mahadeo, as always thanks for all your help. The problem is that I need to get all records that share the same country. Entity A has a lookup to Country and also a lookup to Contact. On the other hand, contact also has a lookup to country, so, the goal is to get all Entity A records that share the same country on their contacts set in the contact lookup.

  • Suggested answer
    Mahadeo Matre Profile Picture
    Mahadeo Matre 17,021 on at
    RE: LINQ - where on two entities

    Hi..

    Check join condition

    A.Customer.Id equals B.Id

    i think B.Id need to be changed to actual record id field e.g. accountid / contactid, also when selecting make sure you are selecting correct id field.  

  • EnriqueMdz Profile Picture
    EnriqueMdz 1,065 on at
    RE: LINQ - where on two entities

    More details about the issue:

    System.InvalidOperationException occurred

     HResult=0x80131509

     Message=variable '<>h__TransparentIdentifier0' of type '<>f__AnonymousType0`2[t]' referenced from scope '', but it is not defined

     Source=<Cannot evaluate the exception source>

     StackTrace:

      at System.Linq.Expressions.Compiler.VariableBinder.Reference(ParameterExpression node, VariableStorageKind storage)

      at System.Linq.Expressions.Compiler.VariableBinder.VisitParameter(ParameterExpression node)

      at System.Linq.Expressions.ParameterExpression.Accept(ExpressionVisitor visitor)

      at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)

      at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression node)

      at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)

      at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)

      at System.Linq.Expressions.ExpressionVisitor.VisitMember(MemberExpression node)

      at System.Linq.Expressions.MemberExpression.Accept(ExpressionVisitor visitor)

      at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)

      at System.Linq.Expressions.ExpressionVisitor.Visit(ReadOnlyCollection`1 nodes)

      at System.Linq.Expressions.Compiler.VariableBinder.VisitLambda[T](Expression`1 node)

      at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)

      at System.Linq.Expressions.Compiler.VariableBinder.Visit(Expression node)

      at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda, DebugInfoGenerator debugInfoGenerator)

      at System.Linq.Expressions.LambdaExpression.Compile()

      at Microsoft.Xrm.Sdk.Linq.QueryProvider.CompileExpression(LambdaExpression expression)

      at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateExpressionToValue(Expression exp, ParameterExpression[] parameters)

      at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateExpressionToConditionValue(Expression exp, ParameterExpression[] parameters)

      at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhereMethodCall(MethodCallExpression mce, FilterExpressionWrapper parentFilter, Func`2 getFilter, Func`2 getLinkLookup, BinaryExpression parent, Boolean negate)

      at Microsoft.Xrm.Sdk.Linq.QueryProvider.TranslateWhereBoolean(String parameterName, Expression exp, FilterExpressionWrapper parentFilter, Func`2 getFilter, List`1 linkLookups, BinaryExpression parent, Boolean negate)

      at Microsoft.Xrm.Sdk.Linq.QueryProvider.GetQueryExpression(Expression expression, Boolean& throwIfSequenceIsEmpty, Boolean& throwIfSequenceNotSingle, Projection& projection, NavigationSource& source, List`1& 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.Query`1.GetEnumerator()

      at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)

      at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

      at GVM_Console_app.Program.Main(String[] args) in C:\plugins\Program.cs:line 81

  • EnriqueMdz Profile Picture
    EnriqueMdz 1,065 on at
    RE: LINQ - where on two entities

    Using metadatabrowser I confirmed that all fields point to the correct entities but the error persist. Still don't know why this happens. Is there another workaround to run this query in another way? maybe not using LINQ.

  • Suggested answer
    Mahadeo Matre Profile Picture
    Mahadeo Matre 17,021 on at
    RE: LINQ - where on two entities

    Hi,

    Is Country is lookup in both entities?  or same data type..

    If it is lookup then A.Country.Id ==B.Country.Id and make sure has column with this name.

    Also you can add multiple columns in join

    var Query  = (from A in LINQContext.EntityASet

                                                join B in LINQContext.EntityBSet

                                                on A.Customer.Id equals B.Id  && A.Country equals B.Country                                      

                                                orderby A.ModifiedOn descending

                                                select A.Id).ToList();

    stackoverflow.com/.../26488779

  • EnriqueMdz Profile Picture
    EnriqueMdz 1,065 on at
    RE: LINQ - where on two entities

    HI Mahadeo, thanks again. Like I said, even if I remove those extra filters the error persist, so, looks like the problem is not with the AND(&&) statement. For what I can see, the main problem is in the line "where A.Country == B.Country".

    Here's the query removing all extra filters("AND" and "where" clauses apart from the main one) :

    var Query  = (from A in LINQContext.EntityASet

                                                 join B in LINQContext.EntityBSet

                                                 on A.Customer.Id equals B.Id

                                                 where A.Country == B.Country

                                                 orderby A.ModifiedOn descending

                                                 select A.Id).ToList();

  • Suggested answer
    Mahadeo Matre Profile Picture
    Mahadeo Matre 17,021 on at
    RE: LINQ - where on two entities

    Enrique,

    You can use AND operator in Linq query.. instead of AND you have to use && when adding two conditions.

    ar CompletedTDRsExpiringInFuture = (from A in LINQContext.EntityASet

                                                  join B in LINQContext.EntityBSet

                                                  on A.Customer.Id equals B.Id

                                                  where A.Country == B.Country

                                                  && A.ModifyOn > PresentDate

                                                  && A.StateCode != Active

                                                  orderby A.ModifiedOn descending

                                                  select A.Id).ToList();

    Here is correct information

    docs.microsoft.com/.../basic-linq-query-operations

  • EnriqueMdz Profile Picture
    EnriqueMdz 1,065 on at
    RE: LINQ - where on two entities

    Hi Mahadeo, first of all thanks for your answer. I think "and" conditions doesn't work for LINQ queries, in the example of the link that you provided above the author uses where clauses one below the other without "and". Also, if I remove all where clauses except for the first one the error still persist:

    var Query  = (from A in LINQContext.EntityASet

                                                  join B in LINQContext.EntityBSet

                                                  on A.Customer.Id equals B.Id

                                                  where A.Country == B.Country

                                                  orderby A.ModifiedOn descending

                                                  select A.Id).ToList();

  • Suggested answer
    Mahadeo Matre Profile Picture
    Mahadeo Matre 17,021 on at
    RE: LINQ - where on two entities

    Hi,,

    Change your query as ..

    var CompletedTDRsExpiringInFuture = (from A in LINQContext.EntityASet

                                                   join B in LINQContext.EntityBSet

                                                   on A.Customer.Id equals B.Id

                                                   where A.Country == B.Country

                                                   and A.ModifyOn > PresentDate

                                                   and A.StateCode != Active

                                                   orderby A.ModifiedOn descending

                                                   select A.Id).ToList();

    You need to use Logical operators for multiple conditions..

    more details

    [View:https://www.c-sharpcorner.com/blogs/how-to-use-multiple-where-condition-in-linq:750:50]

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans