使用实体框架 4.0/.edmx 从 c# 调用标量函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14368505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling scalar function from c# using Entity Framework 4.0 / .edmx
提问by jay
I would like to map my scalar function to my .edmx but it fails. I right click on my entity framework mapping, and choose update model from database. It appears in my stored procedures folder in my model browser.
我想将我的标量函数映射到我的 .edmx 但它失败了。我右键单击我的实体框架映射，然后从数据库中选择更新模型。它出现在我的模型浏览器的存储过程文件夹中。
However, when I want to add it to my Function Importsfolder in the model browser, the message scalar function does not appearshows in the drop down list. Can someone help me?
但是，当我想将其添加到Function Imports模型浏览器中的文件夹时，消息标量函数未显示在下拉列表中。有人能帮我吗？
I can call the scalar function using the old way, such as:
我可以使用旧方式调用标量函数，例如：
dbContext.ExecuteStoreQuery<DateTime?>(
"SELECT dbo.getMinActualLoadDate ({0}, {1}, {2}) AS MyResult",
LoadPkid, LoadFkStartLoc, TripSheetPkid).First();
but it is not the best way. My manager would like me to find a way be able to put the scalar function in the "function import" folder so I can call the scalar function using the following code instead of the previous code:
但这不是最好的方法。我的经理希望我找到一种方法可以将标量函数放在“函数导入”文件夹中，这样我就可以使用以下代码而不是以前的代码来调用标量函数：
dbContext.ExecuteFunction("getMinActualLoadDate ", paramList);
I tried to add an image to display what I mean but as my reputation is still low, I am unable to do so. However the image could be found here: http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/756865e5-ff25-4f5f-aad8-fed9d741c05d
我试图添加一个图像来显示我的意思，但由于我的声誉仍然很低，我无法这样做。但是，可以在此处找到该图像：http: //social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/756865e5-ff25-4f5f-aad8-fed9d741c05d
Thanks.
谢谢。
回答by spajce
I guess you miss the Edit Function Importdialog, where you can generate Complex Types. try to explore.
我想您错过了Edit Function Import可以生成Complex Types的对话框。尝试探索。
If you have successfully created the scalars, you can now navigate like this
如果您已成功创建scalars，您现在可以像这样导航
using (var con = new DatabaseEntities())
{
long? invoiceNo = con.sp_GetInvoiceMaxNumber(code.Length + 2).First();
....
}
回答by Pavel Luzhetskiy
I've encountered same problem. And here is solution I've found my self suitable enough (tested in EF5, but should also work in EF4):
我遇到了同样的问题。这是我发现我自己足够合适的解决方案（在 EF5 中测试，但也应该在 EF4 中工作）：
There is no support of mapping scalar-value functions out of the box but you can execute them directly.
不支持开箱即用的映射标量值函数，但您可以直接执行它们。
You can also edit edmx file to make edmx generate proper method for scalar-value function, but it ll be deleted if you ll synch you model with database.
您也可以编辑 edmx 文件使 edmx 为标量值函数生成正确的方法，但如果您将模型与数据库同步，它将被删除。
Write scalar-valued function implementation yourself:
自己编写标量值函数实现：
string sqlQuery = "SELECT [dbo].[CountMeals] ({0})";
Object[] parameters = { 1 };
int activityCount = db.Database.SqlQuery<int>(sqlQuery, parameters).FirstOrDefault();
Or edit edmx and add Xml for custom maping of scalar-valued function:
或者编辑 edmx 并添加 Xml 以自定义标量值函数的映射：
<Function Name="CountActivities" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<CommandText>
SELECT [dbo].[CountActivities] (@personId)
</CommandText>
<Parameter Name="personId" Type="int" Mode="In" />
</Function>
This information was found in this blog post
回答by dperish
Here is my solution to this issue, which is almost exactly what your manager was asking for.. albeit 18 months late.
这是我对此问题的解决方案，这几乎正是您的经理所要求的……尽管晚了 18 个月。
As an vanilla method:
作为香草方法：
/// <summary>
/// Calls a given Sql function and returns a singular value
/// </summary>
/// <param name="db">Current DbContext instance</param>
/// <typeparam name="T">CLR Type</typeparam>
/// <param name="sql">Sql function</param>
/// <param name="parameters">Sql function parameters</param>
/// <param name="schema">Owning schema</param>
/// <returns>Value of T</returns>
public T SqlScalarResult<T>(DbContext db,
string sql,
SqlParameter[] parameters,
string schema = "dbo") {
if (string.IsNullOrEmpty(sql)) {
throw new ArgumentException("function");
}
if (parameters == null || parameters.Length == 0) {
throw new ArgumentException("parameters");
}
if (string.IsNullOrEmpty(schema)) {
throw new ArgumentException("schema");
}
string cmdText =
$@"SELECT {schema}.{sql}({string.Join(",",
parameters.Select(p => "@" + p.ParameterName).ToList())});";
// ReSharper disable once CoVariantArrayConversion
return db.Database.SqlQuery<T>(cmdText, parameters).FirstOrDefault();
}
}
And as an extension method to EF:
并作为 EF 的扩展方法：
namespace System.Data.Entity {
public static class DatabaseExtensions {
/// <summary>
/// Calls a given Sql function and returns a singular value
/// </summary>
/// <param name="db">Current DbContext instance</param>
/// <typeparam name="T">CLR Type</typeparam>
/// <param name="sql">Sql function</param>
/// <param name="parameters">Sql function parameters</param>
/// <param name="schema">Owning schema</param>
/// <returns>Value of T</returns>
public static T SqlScalarResult<T>(this Database db,
string sql,
SqlParameter[] parameters,
string schema = "dbo") {
if (string.IsNullOrEmpty(sql)) {
throw new ArgumentException("sql");
}
if (parameters == null || parameters.Length == 0) {
throw new ArgumentException("parameters");
}
if (string.IsNullOrEmpty(schema)) {
throw new ArgumentException("schema");
}
string cmdText =
$@"SELECT {schema}.{sql}({string.Join(",",
parameters.Select(p => "@" + p.ParameterName).ToList())});";
// ReSharper disable once CoVariantArrayConversion
return db.SqlQuery<T>(cmdText, parameters).FirstOrDefault();
}
}
}
Though it doesn't smoke here, I would suggest unit testing before any serious use.
虽然这里不抽烟，但我建议在任何认真使用之前进行单元测试。
回答by Md. Alim Ul Karim
The one and the only solution is to convert the function scalar type to table value type with a single value in the table, please see the code sample.
唯一的解决方案是将函数标量类型转换为表中具有单个值的表值类型，请参阅代码示例。
You don't have to change anything in the EDMX XML, please modify the SQL function
EDMX XML 中无需更改任何内容，请修改 SQL 函数
Scalar function as it was, which doesn't work
原来的标量函数，它不起作用
CREATE FUNCTION [dbo].[GetSha256]
(
-- Add the parameters for the function here
@str nvarchar(max)
)
RETURNS VARBINARY(32)
AS
BEGIN
RETURN ( SELECT * FROM HASHBYTES('SHA2_256', @str) AS HASH256 );
END -- this doesn't work.
Scalar function -> Converted to Table Valued function , it works
标量函数 -> 转换为表值函数，它有效
CREATE FUNCTION [dbo].[GetSha2561]
(
-- Add the parameters for the function here
@str nvarchar(max)
)
RETURNS @returnList TABLE (CODE varbinary(32))
AS
BEGIN
INSERT INTO @returnList
SELECT HASHBYTES('SHA2_256', @str);
RETURN; -- This one works like a charm.
END
Edmx screenshot
edmx 截图
回答by sachin
Code in page:
页面代码：
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
using (MayEntities context = new MayEntities())
{
string str = context.Database.SqlQuery<string>("select dbo.HeyYou()").Single().ToString();
Response.Write(str); //output:'Hey this works'
}
}
}
scalar function :
标量函数：
CREATE FUNCTION [dbo].[HeyYou] ()
RETURNS varchar(20)
AS
BEGIN
RETURN 'Hey this works'
END
go
