The member has no supported translation to SQL. When trying to access a property in my Partial class in the LINQ to SQL statement. Lambda expression?

Question

I have 3 tables in my database

• Country
• City
• House

Country table looks like

 CountryID
 Name    

City table looks like

 CountryID
 CityID
 Name

House

 CountryID
 CityID
 HouseID
 Name

I use LINQ to SQL and the above tables become classes and have their properties etc.

Now I have my own abstract class called

Location

And created Partial Classes for Country, City, House that inherit the Location abstract class

and hence can have common functionality like FindByID() and properties like .Parent (of type Location)

Now lets take .Parent which basically returns the Parent for each class

so

House will return

 .Parent  //as City

City will return

 .Parent  //as Country

Country will return

 .Parent  //as Country

Now when trying to use

City.Parent in a LINQ to SQL statement I get a

 The member 'Location`1[City].Parent' has no supported translation to SQL.

Now people have been mentioning about how you can use Lambda expressions to resolve this. Can someone give me a good example for this kind of situation

what should .Parent look like

 Location Parent
 {
    get
   {
        //Please fill Lambda expression here
   }
 }

Answer 1

Your .Parent property will be fine in LINQ-to-Objects, but as the message states, it will have no clue what to do with this if it needs to create TSQL. It can't read through your C# code (IL at the time) to infer meaning. At best there may be something you can do with some function that returns an Expression of some form, but it won't be fun to work with.

Basically, when interacting with the ORM you can only talk in terms of things it already knows about. You could possibly map a UDF to the data-context and use that, but again - not pretty.

To work with Expression, you'd need to use Expression.Invoke and self-rolled lambdas (when combining them to do interesting things like comparing them to an expected value); it really isn't going to be nice. I could probably get an example together, but frankly I think your DAL/repository should just not use these extra properties, but limit itself to using the ORM properties instead. Even if it violates a little DRY.