How to use a user defined scalar-valued function in LINQ ?

Question

0.00/5 (No votes)

See more:

I want to convert a SQL which use a user defined scalar-valued function into LINQ.
How can I use the user defined scalar-valued function in LINQ?

My SQL is like:

SQL

select ...
from ATABLE
where binarychk(ATABLE.A, ATABLE.B)

"binarychk" is the user defined scalar-valued function:

SQL

create FUNCTION [dbo].[binarychk] (@bit1 varchar(200), @bit2 varchar(200))  
RETURNS char AS  
BEGIN 
	declare @counter int, @flag char
	select @flag = 'F', @counter = 1
	select @bit2 = substring(@bit2, len(@bit2)-len(@bit1)+1, len(@bit1))
	while  @counter <= len(@bit1) and @flag = 'F'
	begin
		if (substring(@bit1, @counter, 1) = convert(char,(convert(bit, substring(@bit1, @counter, 1))  & convert(bit, substring(@bit2, @counter, 1))  ) ) and substring(@bit1, @counter, 1) = '1'
		   )
		select @flag = 'T'
		select @counter = @counter +1
	end
	return @flag
END

Posted 8-Feb-12 15:09pm

Ares Cheung

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dean Oliver · Answer 1 · 2012-02-08T18:34:00

Example function

SQL

CREATE FUNCTION ReverseCustName(@string varchar(100))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @custName varchar(100)
    -- Implementation left as exercise for users.
    RETURN @custName
END

Then in your c# code

C#

[Function(Name = "dbo.ReverseCustName", IsComposable = true)]
[return: Parameter(DbType = "VarChar(100)")]
public string ReverseCustName([Parameter(Name = "string",
    DbType = "VarChar(100)")] string @string)
{
    return ((string)(this.ExecuteMethodCall(this,
        ((MethodInfo)(MethodInfo.GetCurrentMethod())),
        @string).ReturnValue));
}

Finally you would call your user defined function in code.

C#

from x in exampleTable
select new {x.Column = ReverseCustName(x.Value)};

Please note this is for Linq To SQL

If you referring to EF follow this link
http://stackoverflow.com/questions/5526422/custom-sql-functions-and-code-first-ef-4-1[^]

Please vote if this was the correct answer to your problem.
Regards, Hope you come right.

Ares Cheung · Answer 2 · 2012-02-09T14:20:00

Solution 2

Where should I put the [Function(Name...)] code?
In my business logic .cs class ? or in the EDM.designer.cs class?

I tried to put the code in my business logic .cs class. It told me that the namespace name 'Function' could not be found.

Anything I missed?

Posted 9-Feb-12 14:20pm