C# call a SQL Server user defined function via Entity Framework

Question

I can't believe I have to ask this question but I'm stuck. I just spent three hours trying to figure this out and I'm stuck.

I was able to do this in a previous version of C# but alias I'm stuck.

Before I continue here is the stack I'm working on...

Visual Studio 2012
SQL Server 2012
.NET Framework 3.5 (I have tried on different versions with the same result).

Consider the following SQL code:

create table dbo.customers
(
    customerid int not null identity(1,1), 
    fname varchar(50), 
    lname varchar(50), 
    favfruit varchar(30)
)
go

alter table customers 
   add constraint customers_customerid_pk primary key (customerid)

insert into customers(Fname, lname, favfruit) 
values ('Melissa', 'Smith', 'Apple'),
       ('Jennifer', 'Jones', 'Pear'),
       ('Jill', 'Brown', 'Apple')       

create function dbo.fngetfruit(@customerid int)
returns varchar(30)
as
begin
    declare @favfruit varchar(30)

    select @favfruit = favfruit
    from dbo.customers c (nolock)
    where c.customerid = @customerid

    return @favfruit
end

select dbo.fngetfruit(2)

This code creates a table (customers) and a user defined function (fngetfruit). This function takes a customerid and returns that customer's favorite fruit from the customer table.

So far so good. Everything works.

Now I wan to call my function (fngetfruit) from a C# console application using Entity Frameworks.

Create a new Console Application.

From inside the console application from solution explorer right click on the application name. Select Add ==> New Item ==> ADO.NET Entity Data Model.

I named my .edmx file CustomersEF.

Select Generate From database. Set up your connection. I took the default entity connection name of customersEntities. Click Next.

In the Choose Your Database Objects and Settings screen select the customers table and the fngetfruit function. Click Finish.

Next you are in a screen that shows your database diagram. In my case I only have a single table named customer. The function is no where to be found.

Open up the console application's main function.

The following code works...

var cusDb = new customersEntities();

var custable = cusDb.customers;

However the following code does not work..

var cusDb = new customersEntities();

var favfruit = cusDb.fngetfruit(1);

Why can't I access my function? what am I missing? Like I said the above steps worked for me in an earlier version.

Thanks,

With a previous version of c#? I find that extremely hard to believe. — Camilo Terevinto
– Camilo Terevinto, Commented Jan 3, 2018 at 17:10
Probably you need to Import it as a Function? This might helps stackoverflow.com/a/32359095/2946329 — Salah Akbari
– Salah Akbari, Commented Jan 3, 2018 at 17:10
Slight detour....you might read this before you continue using that NOLOCK hint everywhere. blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere — Sean Lange
– Sean Lange, Commented Jan 3, 2018 at 17:13
@CamiloTerevinto WHen I first learned the basics of Entity Frameworks the user defined functions just worked. Not it seems they require an additional step. — codingguy3000
– codingguy3000, Commented Jan 3, 2018 at 17:18
That's a previous version of Entity Framework. c# is a language, not a framework. — Camilo Terevinto
– Camilo Terevinto, Commented Jan 3, 2018 at 17:19

David Browne - Microsoft · Accepted Answer · 2018-01-03 18:20:21Z

7

That's a scalar-valued User Defined Function. EF has never supported those directly. It's trivial to add a method to your DBContext to call a scalar-valued UDF. Something like:

public string GetFruit(int customerId)
{
    return Database.SqlQuery<string>("select dbo.fngetfruit(@customerId) fruit", new System.Data.SqlClient.SqlParameter("@customerID", customerId)).Single();
}

EF supports Table-Valued User Defined Functions since version 5 (which requires .NET 4x). Entity Framework Table-Valued Functions (TVFs) (EF5 onwards)

answered Jan 3, 2018 at 18:20

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

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

3 Comments

codingguy3000 Over a year ago

Maybe I was using Linq-to-SQL which had support for Scalar-valued user defined functions.

swinn Over a year ago

Where does Database come from here and what is it?

David Browne - Microsoft Over a year ago

"add a method to your DBContext", so that's DbContext's Database. property. learn.microsoft.com/en-us/dotnet/api/…

Collectives™ on Stack Overflow

C# call a SQL Server user defined function via Entity Framework

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related