locked
LINQ to Entities does not recognize the method RRS feed

  • Question

  • User-595461256 posted

    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;
    }
    }

    Thursday, March 14, 2019 8:03 AM

All replies

  • User753101303 posted

    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.

    Thursday, March 14, 2019 8:56 AM
  • User-595461256 posted

    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

    Thursday, March 14, 2019 9:50 AM
  • User1120430333 posted

    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.

    Thursday, March 14, 2019 9:55 AM
  • User753101303 posted

    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.

    Thursday, March 14, 2019 10:01 AM
  • User-474980206 posted
    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.
    Friday, March 15, 2019 3:09 PM