1

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?

Golo Roden
  • 140,679
  • 96
  • 298
  • 425
  • it's just a simple `case... when...` with a `like 'xxx%'` in Sql... You may look at the generated code to see how it's translated. – Raphaël Althaus Jun 26 '14 at 10:07
  • There is an equivalent in SQL: `CASE`/`WHEN` in `HAVING` (even for much much more complex expressions). That said if LINQ isn't able to translate that code to SQL then it'll simply throw an exception... – Adriano Repetti Jun 26 '14 at 10:07
  • 1
    You cannot plug-in just any method. It will fail if it cannot translate the expression tree. – Dennis_E Jun 26 '14 at 10:09
  • Since you are asking about custom linq providers, it's hard to say what "it" will do in a particular scenario. But most providers throw an exception. An alternative approach would be for it to drop the constraints (in case of a WHERE clause) that it doesn't understand and have these constraints be evaluated in memory (using linq 2 objects) – Rune Jun 26 '14 at 10:12
  • 1
    Basically it can't transform arbitrary code but it can transform all code its been told about in whatever combinations you would care to give it. If it hasn't been told about a certain piece of code it will just throw some kind of an error (or it might ignore it or it can frankly do whatever it wants but one would hope it throws an exception). – Chris Jun 26 '14 at 10:13
  • Read what is supported: http://msdn.microsoft.com/en-us/library/bb386970.aspx (presuming LINQ-To-SQL) – Tim Schmelter Jun 26 '14 at 10:14

2 Answers2

2

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
1

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.

Laurent LA RIZZA
  • 2,905
  • 1
  • 23
  • 41