Custom Method in LINQ to SQL query

by

As Pranay explains, you cannot have a custom (C#) method as part of the LINQ to SQL query, because LINQ to SQL wouldn’t be able to look at the expression tree of the method and so it cannot translate it to SQL.

One option that you have is to write your function in SQL and store it as a SQL function on the SQL Server (possibly, you could also use SQL CLR, but I have not tried that). Then you can add the function to your DataContext type and LINQ to SQL will translate it to calls to the function on SQL server. Something like:

var result = from u in context.MyTable 
             where context.MyMethod(u) == 10 select u;

The problem, of course, is that you’ll need to write the function in SQL (I think SQL CLR could also work – not sure about the performance and other possible complications though)

I also wrote an article (some time ago) that shows how to do this when you write the “method” as an expression tree way (as a value of type Expression<Func<...>>), which is possible, because in this case, the code is compiled as an expression tree. However, there is some postprocessing that has to be done and you can still write just a single expression that can be easily inlined in the LINQ query.

More Related Contents:

  1. Returning IEnumerable vs. IQueryable
  2. Difference Between Select and SelectMany
  3. LINQ – Left Join, Group By, and Count
  4. When should I dispose of a data context
  5. How can I conditionally apply a Linq operator?
  6. LINQ to SQL – Left Outer Join with multiple join conditions
  7. LINQ to SQL and a running total on ordered results
  8. Understanding .AsEnumerable() in LINQ to SQL
  9. TransactionScope vs Transaction in LINQ to SQL
  10. How to return anonymous type from c# method that uses LINQ to SQL [duplicate]
  11. How to write Asynchronous LINQ query?
  12. Convert Linq Query Result to Dictionary
  13. Am I misunderstanding LINQ to SQL .AsEnumerable()?
  14. How do you perform a CROSS JOIN with LINQ to SQL?
  15. LINQ Where Ignore Accentuation and Case
  16. Get table-data from table-name in LINQ DataContext
  17. DISTINCT() and ORDERBY issue
  18. Linq query with nullable sum
  19. LINQ to SQL using GROUP BY and COUNT(DISTINCT)
  20. Max or Default?
  21. How to make LINQ execute a (SQL) LIKE range search
  22. Will using LINQ to SQL help prevent SQL injection
  23. How do I do a left outer join with Dynamic Linq?
  24. Last and LastOrDefault not supported
  25. Linq “Could not translate expression… into SQL and could not treat it as a local expression.”
  26. Accessing System Databases/Tables using LINQ to SQL?
  27. LINQ to SQL Where Clause Optional Criteria
  28. Linq where column == (null reference) not the same as column == null
  29. If Else in LINQ
  30. C# Linq to SQL: How to express “CONVERT([…] AS INT)”?

Leave a Comment