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

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,

Solution 1:

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)

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

Solution 1:

Related

Recent Posts