I am getting run time error while trying to loop the expression
Error: LINQ to Entities does not recognize the method
General Error Definition :An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code
I don't know where exactly I need to change in the code to fix
and What error description says?
Please assist me to fix the issue.
Code:
var test =TestChargeReserveRepository.GetAll(x => YearIds.Contains(x.YearId) && Positions.Contains(x.PositionId))
.GroupBy(x => new { x.YearId, x.Year.Jurisdiction.JurisdictionName})
.Select(x => new
{
Amount = x.Where(z => ConvertNotesToXml.Deserialize(z.XmlDetails).Last().Action == "Reversal Of Tax Charges").Sum(y => (y.Charge * -1)),
If working against a database the C# expression is converted to SQL and the issue is that it can't translate your client side Deserialize method. You have XML on the db side and want to use a criteria based on this XML content ?
You have many ways to approach that. If this information is used often on the client side I would likely try to create a computed column server side that would extract this information from, my XML column and would use then that in EF.
The problem is that the EF engine cannot make T-SQL to be submitted to the database engine for execution based on the Linq query you have formulated, becuase it has no clue as to what Deserialize() is and it cannot make the T-SQL becuase of it.
What you could to in using this Deserialize() function is to query the database without it, get the results, disconnect from the database so the resulting list is disconnected so that a loop iteration cannot have EF go back to the database on each iteration
in the loop and you loop on the collection of objects in the list.
Becuase you are using a disconnected list of objects disconnected from the database sitting in memory, you are using Linq-2-Object and not Linq-2-Entities.
You could just Linq query the disconnected list which would be a Linq-2-Object query there is no T-SQL being made, becuase you're not using EF. You're just querying a collection of objects in memory.
If you find the post has answered your issue, then please mark post as 'answered'.
you can not use any user defined or library methods (such xml routines) in a queriable linq query. This is because the query engine can not convert the code to sql. You should select xml as a string in the select(), then convert to list, so your sql query is
run, and converted to objects, then select again to do the deserialize, and calc.
A better option is just do the query in native sql.
Member
7 Points
15 Posts
LINQ to Entities does not recognize the method
Mar 14, 2019 08:03 AM|pughal_dotmvc|LINK
Hi ,
Thanks in Advance.
I am getting run time error while trying to loop the expression
Error: LINQ to Entities does not recognize the method
General Error Definition :An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code
I don't know where exactly I need to change in the code to fix
and What error description says?
Please assist me to fix the issue.
Code:
var test =TestChargeReserveRepository.GetAll(x => YearIds.Contains(x.YearId) && Positions.Contains(x.PositionId))
.GroupBy(x => new { x.YearId, x.Year.Jurisdiction.JurisdictionName})
.Select(x => new
{
Amount = x.Where(z => ConvertNotesToXml.Deserialize(z.XmlDetails).Last().Action == "Reversal Of Tax Charges").Sum(y => (y.Charge * -1)),
YearId = x.Key.YearId,
Jurisdiction = x.Key.JurisdictionName
});
My Deserialize method
public static List<Message> Deserialize(string notesXmlString)
{
Note MessageList = new Note();
StringReader reader = new StringReader(notesXmlString ?? "");
XmlSerializer ser = new XmlSerializer(MessageList.GetType());
XmlTextReader XmlReader = new XmlTextReader(reader);
try
{
MessageList = (Note)ser.Deserialize(XmlReader);
return MessageList.MessageList;
}
catch (Exception)
{
return null;
}
}
All-Star
47410 Points
17538 Posts
Re: LINQ to Entities does not recognize the method
Mar 14, 2019 08:56 AM|PatriceSc|LINK
Hi,
If working against a database the C# expression is converted to SQL and the issue is that it can't translate your client side Deserialize method. You have XML on the db side and want to use a criteria based on this XML content ?
You have many ways to approach that. If this information is used often on the client side I would likely try to create a computed column server side that would extract this information from, my XML column and would use then that in EF.
Member
7 Points
15 Posts
Re: LINQ to Entities does not recognize the method
Mar 14, 2019 09:50 AM|pughal_dotmvc|LINK
Yes.I am getting XML from database table and get the matching values.I would like to achieve or resolve the error in the code side not db side
Contributor
4623 Points
3903 Posts
Re: LINQ to Entities does not recognize the method
Mar 14, 2019 09:55 AM|DA924|LINK
The problem is that the EF engine cannot make T-SQL to be submitted to the database engine for execution based on the Linq query you have formulated, becuase it has no clue as to what Deserialize() is and it cannot make the T-SQL becuase of it.
What you could to in using this Deserialize() function is to query the database without it, get the results, disconnect from the database so the resulting list is disconnected so that a loop iteration cannot have EF go back to the database on each iteration in the loop and you loop on the collection of objects in the list.
Becuase you are using a disconnected list of objects disconnected from the database sitting in memory, you are using Linq-2-Object and not Linq-2-Entities.
You could just Linq query the disconnected list which would be a Linq-2-Object query there is no T-SQL being made, becuase you're not using EF. You're just querying a collection of objects in memory.
All-Star
47410 Points
17538 Posts
Re: LINQ to Entities does not recognize the method
Mar 14, 2019 10:01 AM|PatriceSc|LINK
Then try :
Amount = x.ToList().Where(z => ConvertNotesToXml.Deserialize(z.XmlDetails).Last().Action == "Reversal Of Tax Charges").Sum(y => (y.Charge * -1)),
to force running the db query first (note that it means you get ALL rows from your db) before further processing on the client side.
All-Star
56344 Points
14717 Posts
Re: LINQ to Entities does not recognize the method
Mar 15, 2019 03:09 PM|bruce (sqlwork.com)|LINK
A better option is just do the query in native sql.