none
Can you write a UDF in c# for use in a linq expression? RRS feed

  • Question

  • I'd like to use a function in a linq expression much like a user definied function would be used in t-sql. From the documentation I see how to create a UDF on sql server in t-sql, then generate a method from that udf and call that method within a linq expression like 'db.UDFName(table.Column)', but is there a way to use the same or similar syntax for calling a method that is written from scratch in c#? It seems like it would be nice to not to have to write any t-sql at all and still have udf functionality in your linq expressions no?

    Thanks for your assistance,

    bd

    Sunday, July 13, 2008 11:31 AM

Answers

All replies

  • In general, no, you cannot do that.

     

    You could, however, write a SQL function in C# (that is, use the SQL / CLR integration to define a function in C# instead of T-SQL) and then use it like any other UDF.

     

    Thanks,

     

    --Samir

     

    Monday, July 14, 2008 10:44 PM
  • Are you looking for defining a local method in c# and using it within a query? LinqToSQL allows LocalMethodCalls, however as expected they will be evaluated by CLR and not on Server. Please check documentation for Local method calls http://msdn.microsoft.com/en-us/library/bb882670.aspx .

     

     

    Tuesday, July 15, 2008 4:24 PM
  • As others suggested you can write a SQL UDF using a UDF defined using CLR but I have a feeling this is not what you are looking for. I think the key to doing what your are trying to do is to look into what are referred to as Linq Expressions. By defining reusable expressions you can simulate the type of functionality that a UDF serves and if written correctly they should be translatable by Linq to SQL into SQL code that runs on the server. So in essence you get the reuse within your C# code even though the query is "rendered" out as plain SQL with no function call.

    I've been looking into how to create reusable Linq Expressions myself and so far I've found some examples of reusable expresses to test for conditions and dynamically modify the where clause. Also I've had some success so far defining functions which themselves return values of type IQueryable in order to say define a reusable join between multiple tables but allow the selector (projection) to be defined at the time of use. If this is more of what your looking for and your interested in seeing some examples let me know.

    http://stackoverflow.com/questions/986877/how-can-i-reuse-expressions-within-linq-statements

    http://stackoverflow.com/questions/769351/refactoring-linq-iqueryable-expression-to-remove-duplicated-portions-of-queries
    Monday, July 27, 2009 2:33 PM
  • DamienG recently posted an excellent example of that in his blog:
    http://damieng.com/blog/2009/06/24/client-side-properties-and-any-remote-linq-provider

    .
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Monday, July 27, 2009 2:53 PM
    Answerer
  • DamienG recently posted an excellent example of that in his blog:
    http://damieng.com/blog/2009/06/24/client-side-properties-and-any-remote-linq-provider

    Would you mind posting a simple example of how one would go about using the proposed solution to solve BrooklynDev 's original question?

    Below is an example of what I'm aiming for but obviously this does not work as causes a compile time exception of "'f' is a 'variable' but is used like a 'method'".


    Expression<Func<Customer, string>> f = n => n.Firstname;
    
    var query1 = 
    	from c in Customer
    	select new { result = f(c) };
    	
    var query2 = 
    	from c in Customer
    	select new { result = f(c) };

    Monday, August 3, 2009 6:44 PM
  • Hi,

    We had the same problem. It is not supported out of the box and it is a major problem for LOB applications. I ended up writing a code-project article about LINQ expressions reuse, including a very small utility called LinqExpressionPrjection that enables the reuse in projections (including into anonymous types).

    Find the article here.

    You can get the assembly for the projection reuse as a nuget package and the source is on CodePlex.

    Some time has passed since your post. I hope it is still helpful for you. If not, maybe for others reading this thread.


    • Edited by Asher_barak Thursday, June 14, 2012 6:14 PM
    Thursday, June 14, 2012 6:14 PM