Is it possible to use/access scalar functions with LINQ to SQL?

Question

We have scalar functions in our database for returning things like "number of tasks for a customer" or "total invoice amount for a customer".

We are experimenting and looking to try to do this w/o stored procedures ... normally we would just call this function in our stored procedure and return it as a single value.

Is there a way to use or access scalar functions with LINQ to SQL? If so, I would be interested in see an example of how to ... if not, how would it be best to handle this type of situation ... if it is even doable.

Answer 1

LINQ-to-SQL supports use with UDFs, if that is what you mean. Just drag the UDF onto the designer surface and you're done. This creates a matching method on the data-context, marked [Function(..., IsComposable=true)] or similar, telling LINQ-to-SQL that it can use this in queries (note that EF doesn't support this usage).

You would then use it in your query like:

var qry = from cust in ctx.Custs
           select new {Id = cust.Id, Value = ctx.GetTotalValue(cust.Id)};

which will become TSQL something like:

SELECT t1.Id, dbo.MyUdf(t1.Id)
FROM CUSTOMER t1

(or there-abouts).

The fact that it is composable means that you can use the value in queries - for example in a Where()/WHERE - and so reduce the data brought back from the server (although obviously the UDF will still need to be executed in some way).

Here's a similar example, showing a pseudo-UDF at use on a data-context, illustrating that the C# version of the method is not used.

Actually, I'm currently looking at such UDFs to provide "out of model" data in a composable way - i.e. a particular part of the system needs access to some data (that happens to be in the same database) that isn't really part of the same model, but which I want to JOIN in interesting ways. I also have existing SPs for this purpose... so I'm looking at porting those SPs to tabular UDFs, which provides a level of contract/abstraction surrounding the out-of-model data. So because it isn't part of my model, I can only get it via the UDF - yet I retain the ability to compose this with my regular model.

Answer 2

I believe this MSDN documentation is what you're after (as part of this wider topic of calling user-defined functions in LINQ to SQL). Can't say I've done it myself, but it sounds right...