LinqToSQL - no supported translation to SQL

Question

Hi,

I have been puzzling over a problem this morning with LinqToSQL. I'll try and summarise with the abbreviated example below to explain my point.

I have DB two tables:

table Parent
{
   ParentId
}

table Child
{
   ChildId
   ParentId [FK]
   Name
   Age
}

These have LinqToSQL equivalent classes in my project, however, I have written two custom model classes that I want my UI to use, instead of using the LinqToSQL classes.

My data access from the front end goes through a service class, which in turn calls a repository class, which queries the data via linq.

At the repository level I return an IQueryable by:

return from data in _data.Children
       select new CustomModel.Child
       {
          ChildId = data.ChildId,
          ParentId = date.ParentId
       };

My service layer then adds an additional query restriction by parent before returning the list of children for that parent.

return _repository.GetAllChildren().Where(c => c.Parent.ParentId == parentId).ToList();

So at this point, I get the method has no supported translation to sql error when I run everything as the c.Parent property of my custom model cannot be converted. [The c.Parent property is an object reference to the linked parent model class.]

That all makes sense so my question is this:

Can you provide the querying process with some rules that will convert a predicate expression into the correct piece of SQL to run at the database and therefore not trigger an error?

I haven't done much work with linq up to now so forgive my lack of experience if I haven't explained this well enough.

Also, for those commenting on my choice of architecture, I have changed it to get around this problem and I am just playing around with ideas at this stage. I'd like to know if there is an answer for future reference.

Many thanks if anyone can help.

Answer 1

Firstly, it begs the question: why is the repository returning the UI types? If the repo returned the database types, this wouldn't be an issue. Consider refactoring so that the repo deals only with the data model, and the UI does the translation at the end (after any composition).

---

If you mean "and have it translate down to the database" - then basically, no. Composable queries can only use types defined in the LINQ-to-SQL model, and a handful of supported standard functions. Something similar came up recently on a related question, see here.

For some scenarios (unusual logic, but using the typed defined in the LINQ-to-SQL model), you can use UDFs at the database, and write the logic yourself (in TSQL) - but only with LINQ-to-SQL (not EF).

If the volume isn't high, you can use LINQ-to-Objects for the last bit. Just add an .AsEnumerable() before the affected Where - this will do this bit of logic back in managed .NET code (but the predicate won't be used in the database query):

return _repository.GetAllChildren().AsEnumerable()
            .Where(c => c.Parent.ParentId == parentId).ToList();