How to use a c# method in a sql stored procedure

Question

I wanted to use linq as so:

MyDBEntities context = new MyDBEntities();
context.MyTable.Where(i => MyMethod(i.column, valueToTest).ToList();

with

public bool MyMethod(Object a, Object b)

but apparently using such a method with isn't possible

so I was hopping I could use the methode in a stored procedure I would be able to call with linq

do you think is it possible ?

I'm sorry but your question makes no sense, the title talks about a Stored Procedure, your sample code uses (i assume) Linq2Sql, and what you're trying to achieve, is just bad... I think you're looking for CLR Stored Procedures. msdn.microsoft.com/en-us/library/ms131094.aspx - But what are you trying to do that SQL doesn't allow you to do already? — Phill
– Phill, Commented Jun 5, 2011 at 22:28
@Phill, it looks like he wants to write a method that will compare a specified column against a specified value and return a bool based on the result. — The Muffin Man
– The Muffin Man, Commented Jun 5, 2011 at 22:37
@Nick - It's such a vague question, I'm not sure why it's being upvoted, there isn't really much to go by, too open for assumptions. — Phill
– Phill, Commented Jun 5, 2011 at 23:41

Ladislav Mrnka · Accepted Answer · 2011-06-05 22:30:47Z

4

Generally it is possible to create C# function and use it in SQL Server (2005 and newer) but it is not so simple - you must use SQL CLR which means separate project for your function, special references, special types, etc. At last you must deploy the assembly to SQL server to be able to use the function in SQL. General documentation also covering how to create custom function:

Creating SQL Server Objects in Managed Code

Once you have your function on SQL server you can use it within stored procedure and you can use it within query. I'm actually not sure if you can import these functions into Linq-to-sql or EF model and use them in Linq-to-sql or Linq-to-entities queries.

answered Jun 5, 2011 at 22:30

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Leniel Maccaferri · Accepted Answer · 2011-06-05 22:24:13Z

2

Take a look here for a complete sample:

Calling custom methods in LINQ-to-SQL

answered Jun 5, 2011 at 22:24

Leniel Maccaferri

103k48 gold badges381 silver badges495 bronze badges

1 Comment

Ladislav Mrnka Over a year ago

I'm afraid this works only for very small subset of functions because you cannot map arbitrary function - SQL doesn't know how to execute it. It works only for functions which are internally mapped to SQL counterparts like Math.xxx, String.xxx, etc.

The Muffin Man · Accepted Answer · 2011-06-05 22:50:21Z

I hope I understand you correctly.

Let's say that MyTable is a database table that contains the columns Name, and Address Here's how you would get a value back whether the results contain the specified value you passed.

public void SomeMethod()
    {
        MyTable table= new MyTable();

        bool b=  MyMethod(table.Name, "Fred");
       if(b)
         //Do something
       else
         //Do something else
    }

    public bool MyMethod(MyTable a, object value)
    {
        using(var context= new MyDBEntities())
        {
            return context.MyTable.Where(i => a == value).Any();
        }
    }

This is what the database table 'MyTable' looks like behind the scenes.(the data context generated this)

public class MyTable
{
    public string Name { get; set; }
    public string Address { get; set; }
}

So you can see in the first method I pass table.Name to MyMethod, that's only possible because MyTable has a pubic property called Name. Also notice that we are using type Object for the value, as the parameter could an int, a string, a date time, who knows.

Note: This is untested code, but should get off to right track if I understand you correctly.

Collectives™ on Stack Overflow

How to use a c# method in a sql stored procedure

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related