How is it possible for a LINQ provider to transform arbitrary code?

Question

I understand that a LINQ provider is the "thing" that transforms the actual LINQ query into a SQL query (or whatever). It does so by traversing the AST of the LINQ query, and rendering an appropriate SQL query. So far, so good.

Now I can imagine that this works for simple C# code, such as

where person.Age >= 18

which can be (mostly directly) translated to SQL. But what if I provide arbitrary complex C# code, such as:

where person.Name.StartsWith(person.Age < 25 ? 'X' : 'Y')

There is no equivalent to this in SQL, so what does the LINQ provider do in such a case?

Answer 1

There is no equivalent to this in SQL

Not sure how it will actually write the SQL (you could test it), but it could be simply:

where person.Name like (case when person.Age < 25 then 'X' else 'Y' end) + '%'

To get the actual SQL (assuming it works): profile the connection.

But indeed; not all things are possible, and often it will simply throw an exception to indicate that it doesn't recognise something or cannot construct the SQL. For example, if you do:

where person.Name.StartsWith(MyCustomMethod(person) ? 'X' : 'Y')

then I would expect it to fail.

Answer 2

The ternary operator does not seem to much a fuss to render by using a CASE expression.

 CASE WHEN Person < 25 THEN 'X' ELSE 'Y' END

Otherwise, you get an exception kindly instructing you not to use method calls in Linq To SQL queries. I think that StartsWithis supported though.