LINQ - where on two entities

(0) Share

Report

Posted on by EnriqueMdz

1,067

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

I have the same question (0)

All responses (10)

Answers (1)

Suggested answer

Mahadeo Matre 17,021 on at

Like (0)

Report

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]

Was this reply helpful? Yes No
EnriqueMdz 1,067 on at

Like (0)

Report

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();

Was this reply helpful? Yes No
Suggested answer

Mahadeo Matre 17,021 on at

Like (1)

Report

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

Was this reply helpful? Yes No
EnriqueMdz 1,067 on at

Like (0)

Report

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();

Was this reply helpful? Yes No
Suggested answer

Mahadeo Matre 17,021 on at

Like (1)

Report

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

Was this reply helpful? Yes No
EnriqueMdz 1,067 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
EnriqueMdz 1,067 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

Mahadeo Matre 17,021 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
EnriqueMdz 1,067 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

CrmLeo 35 on at

Like (0)

Report

@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]

Was this reply helpful? Yes No