It is possible to register custom functions that exist in the database so that they can be called by Entity Framework Code First LINQ queries.
If we want this method to be callable by a LINQ query, we need to add the DbFunctionAttribute to it, specifying the name of the database function we wish to call, because the .NET method and the database function names can be different:
And for calling it:
However, for certain database functions, it requires a bit more work to get done. Let us consider now the FORMAT function and a .NET implementation:
Besides adding the DbFunctionAttribute attribute:
it also requires that we register it explicitly in our model, for that, we override the OnModelCreating method and add a custom convention:
The convention being:
Now we have the FORMAT function available to LINQ:
Now, I hear you ask: why for SOUNDEX we just need to add a simple attribute and for FORMAT we need so much more? Well, it just happens that SOUNDEX is defined in the Entity Framework SQL Server provider manifest - see it here. All of the functions in SqlFunctions are present in the manifest, but the opposite is not true - not all functions in the manifest are in SqlFunctions, but that's the way it is! Thanks to @divega for the explanation.
Some things worth mentioning:
You cannot specify two functions with the same name and different parameters.
There is an open request to add the FORMAT function to the list of functions supported out of the box by Entity Framework Code First: https://entityframework.codeplex.com/workitem/2586 through the SqlFunctions class, but in the meantime, this might be useful!