none
Could not translate expression in one case but it does in another RRS feed

  • Question

  • Ok admittedly I am a Linq junkie and the database I designed is a true relational database with so many tables and foreign key relationships/constraints that Homeland security would place it on its list to watch list if it was a flying passenger.

    I have the below working code which, as is shown now throws the exception Could not translate expression. I will show the full exception at the bottom of this post.

    var ExportAuthorities = from itm in GTCDataContext.Contacts
                            where itm.Person.ID_NonULA != null
                            where itm.ID_Meeting == UserMeeting.Meeting_ID
                            select new
                            {
                                Name = itm.Person.Name_First + " " + itm.Person.Name_Middle + " " + itm.Person.Name_Last,
                                Company = itm.Person.Person_Non_ULA.Location_Company.Text,
                                Nationality = itm.Person.Person_Non_ULA.Nationality.Text,
                                Passport = DeterminePassport( itm ),
                                Country = itm.Person.Person_Non_ULA.Location_Country.Text,
                                NonULAID = itm.Person.Person_Non_ULA.Person_NonULA_ID,
                                /* The following three items share the same cell. Only one can 
                                 * return data to populate the cell
                                 */
                                MissingAuthorities = DetermineMIAExports( itm.Person.Person_Non_ULA ),  //  It is used here as well, but no problems.
                                USPersonUSCompany = DetermineIfUSPersonAndCompany( itm.Person.Person_Non_ULA ),
                                ItemToAuthorities = from ia in itm.Person.Person_Non_ULA.Export_Item_Authorities
                                                    select new
                                                    {
                                                        ID = ia.Export_Item_Authority_ID,
                                                        Item = ia.Export.Name,
                                                        Authority = ia.Authority.Name
                                                    },
    /* Failure Here */          EditVisible = PassportOn // The next line causes it to fail!
                                   && !IsPersonUSAndCompanyUS( itm.Person.Person_Non_ULA )
                            };


    Ok if I comment out the method call on the last line the && IsPersonUSAndCompanyUS() the above works . But if I leave it in, in that context, it throws the exception.

    Now the interesting thing is the line shown above it uses the same parameter , which I believe the exception is based off of (shown above bolded where its calling DetermineMIAExports ). Now that call to MIA actually internally also calls IsPersonUSAndCompanyUs() as well.  WT?

    So why does the inclusion at the top level calling IsPerson... causing problems but is quite fine within a method call?

    Thoughts, concerns... Thanks

    Here is the actual exception

    Could not translate expression 'Table(Contact).Where(itm => (itm.Person.ID_NonULA != null)).Where(itm => (itm.ID_Meeting = Invoke(value(System.Func`1[System.Nullable`1[System.Int32]])))).Select(itm => new <>f__AnonymousType7`10(Name = ((((itm.Person.Name_First + " ") + itm.Person.Name_Middle) + " ") + itm.Person.Name_Last), Company = itm.Person.Person_Non_ULA.Location_Company.Text, Nationality = itm.Person.Person_Non_ULA.Nationality.Text, Passport = value(ASP.controls_attendeesnonula_ascx).DeterminePassport(itm), Country = itm.Person.Person_Non_ULA.Location_Country.Text, NonULAID = itm.Person.Person_Non_ULA.Person_NonULA_ID, MissingAuthorities = value(ASP.controls_attendeesnonula_ascx).DetermineMIAExports(itm.Person.Person_Non_ULA), USPersonUSCompany = value(ASP.controls_attendeesnonula_ascx).DetermineIfUSPersonAndCompany(itm.Person.Person_Non_ULA), ItemToAuthorities = itm.Person.Person_Non_ULA.Export_Item_Authorities.Select(ia => new <>f__AnonymousType6`3(ID = ia.Export_Item_Authority_ID, Item = ia.Export.Name, Authority = ia.Authority.Name)), EditVisible = (Invoke(value(System.Func`1[System.Boolean])) && Not(value(ASP.controls_attendeesnonula_ascx).IsPersonUSAndCompanyUS(itm.Person.Person_Non_ULA)))))' into SQL and could not treat it as a local expression.


    William Wegerson (www.OmegaCoder.Com)
    Wednesday, April 22, 2009 11:57 PM
    Moderator

Answers

  • The problem here isn't the itm.Person.Person_Non_ULA but the method IsPersonUSAndCompanyUS.

    In the other cases you have written methods you have them on the select new - this is fine because we don't need to run these methods until we start creating your objects in-memory once the TSQL has been generated and returned the results so we can run the methods on the client and pass in the query results as part of the object creation. 

    Great.

    The IsPersonUSAndCompanyUS however is part of the selection criteria and all the actual selection happens server-side on SQL server. For that to happen we need to translate the expression tree to TSQL but IsPersonUSAndCompanyUS can't be sent to the server - it doesn't have this method - nor can we generate TSQL for it because it is not an expression tree but compiled IL code in the form of a method.

    The best thing to do here is to put the logic of IsPersonUSAndCompanyUS inline in the expression so that LINQ to SQL has the semantics in expression form for translation to TSQL.

    [)amien
    Thursday, April 23, 2009 4:41 AM
    Moderator

All replies

  • What's the return type (or full signature) of IsPersonUSAndCompanyUS? I assume it is a store function defined in the dbml..? Does it work if you don't do the &&, i.e. map PassportOn and the function in to separate members in the anonymous type..?
    Kristofer - Huagati Systems Co., Ltd. - www.huagati.com - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools
    Thursday, April 23, 2009 2:57 AM
    Answerer
  • The problem here isn't the itm.Person.Person_Non_ULA but the method IsPersonUSAndCompanyUS.

    In the other cases you have written methods you have them on the select new - this is fine because we don't need to run these methods until we start creating your objects in-memory once the TSQL has been generated and returned the results so we can run the methods on the client and pass in the query results as part of the object creation. 

    Great.

    The IsPersonUSAndCompanyUS however is part of the selection criteria and all the actual selection happens server-side on SQL server. For that to happen we need to translate the expression tree to TSQL but IsPersonUSAndCompanyUS can't be sent to the server - it doesn't have this method - nor can we generate TSQL for it because it is not an expression tree but compiled IL code in the form of a method.

    The best thing to do here is to put the logic of IsPersonUSAndCompanyUS inline in the expression so that LINQ to SQL has the semantics in expression form for translation to TSQL.

    [)amien
    Thursday, April 23, 2009 4:41 AM
    Moderator
  • Kristofer: The return type is bool.

    Damien: Your logic is impeccable I will try it tomorrow and report back.

    Thanks!
    William Wegerson (www.OmegaCoder.Com)
    Thursday, April 23, 2009 5:13 AM
    Moderator
  • Works like a charm, thank you for taking the time to explain it in detail. I pulled up the code to the top level (with the new) and all is well. Thanks Damien!
    William Wegerson (www.OmegaCoder.Com)
    Thursday, April 23, 2009 2:11 PM
    Moderator
  • OmegaMan, please send [dudu.ferreira@gmail.com] or post here the solution of this problem.

    best regards.


    Pefs

    Tuesday, July 10, 2012 2:49 PM
  • It would be better if you simply post your problem to the forums. This post was three years ago and the verbal advice given by Damien still applies.

    William Wegerson (www.OmegaCoder.Com)

    Tuesday, July 10, 2012 7:14 PM
    Moderator