C#通过实体框架调用SQL Server用户定义的函数 [英] 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.

我能够在C#的早期版本中执行此操作，但是别名卡住了.

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(我尝试过使用相同结果的不同版本).

请考虑以下SQL代码:

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)

此代码创建一个表(客户)和一个用户定义的函数(fngetfruit).此函数获取一个customerid并从客户表中返回该客户最喜欢的水果.

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.

现在，我想使用Entity Frameworks从C#控制台应用程序调用我的函数(fngetfruit).

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

创建一个新的控制台应用程序.

Create a new Console Application.

在解决方案资源管理器的控制台应用程序内部，右键单击应用程序名称.选择添加==>新建项==> ADO.NET实体数据模型.

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

我将我的.edmx文件命名为CustomersEF.

I named my .edmx file CustomersEF.

选择从数据库生成.设置您的连接.我采用了customersEntities的默认实体连接名称.点击下一步.

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

在选择数据库对象和设置"屏幕中，选择"customers"表和"fngetfruit"功能.单击完成.

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

接下来，您将在显示数据库图的屏幕中.就我而言，我只有一个名为customer的表.该功能在哪里都找不到.

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.

以下代码有效...

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.

谢谢

推荐答案

这是标量值的用户定义函数.EF从未直接支持那些人.向您的DBContext添加一个方法来调用标量值的UDF很简单.像这样:

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从版本5(需要.NET 4x)开始支持表值用户定义函数.实体框架表值函数(TVF)(从EF5开始)

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

这篇关于C#通过实体框架调用SQL Server用户定义的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！