Checking out one of the new stored procedure features in EF4

Wednesday, July 22 2009 - Data Access

I certainly have not yet explored the depths of EF4 to the same extent as I have with the current version and I have a lot of discoveries ahead of me.

Thanks to a tweet this morning by Zekq, I finally saw one of the promised improvements to EF’s stored procedure support.

In EF v1, you can easily create a function based on a stored procedure through function mapping for stored procs that return scalars. But calling those functions is not as easy. The code generator does not create a class method for the function. The only way to execute these functions is through EntityClient. You can easily find examples of this on the web or if you have my book, Programming Entity Framework, there is an example in VB and C# at the beginning of Chapter 13.

Now in EF4, the default code generator does indeed create the function so that it is now simple to call the function in your code without spinning up and executing an EntityConnection and EntityCommand.

If you haven’t mapped functions before, here is a quick walkthrough:

When you import a stored proc with teh EDM Wizard (or Update Wizard), the procedure gets inserted into the store schema (SSDL) but not into your conceptual model.

functionmappinga

You need to map the procedure to your CSLD using a Function Import.

Step 1: Right click on the function in the model browser and choose Add Function Import.

 

functionmappingb

Step 2: Define the Function Import. Here I have indicated that the function returns an Int32 result.

functionmappingb1 

Now the function is part of your CSDL. You will not see it in the designer but you will see it in the raw XML and the Model Browser.

functionmappingc

So far this is exactly the same as EFv1. The difference is that the code generator has add the function to the EFWorkshopEntities class and I can easily call it in my code now as I would any other function.

 functionmappingd

Tagged as: ,

#1 shankar(india) on 7.22.2009 at 3:24 PM

Excellent Article. Will you be covering EF4 in your book version 2 and when is it being released? I use oracle 10g with 2000 entities and EF1 was difficult plumbing for stored procedures.

#2 Julie on 7.22.2009 at 5:15 PM

@shankar - no comment about book version 2 at the moment. Still recovering from v1 ;-)

#3 zeqk on 7.23.2009 at 8:22 AM

I really wait for EF 4. I use a return scalar value function import for a custom insert without autoincrement PK

#4 Julie on 7.23.2009 at 8:30 AM

I know it's more code but for now, you could create a custom method for your objectContext that executes the EntityClient code on your behalf.. just an idea.

#5 Markus on 3.29.2010 at 9:40 AM

We tried to use the VS2010 RC POCO template for code generation and realized that the function was not added to the xyEntities class. Is that intentional or is this supposed to work?

#6 Julie on 3.29.2010 at 9:43 AM

They should be there. At one point, the POCO template created methods when there is a return type but not when there is no return type. This was unintentional on the part of the EF team and they said they would be fixing it. I haven't checked latest bits.

#7 Markus on 3.29.2010 at 10:29 AM

Just found out myself. It should basically work, but not with SPs that have NO return type.

So I fixed the template for me

...

region.Begin("Function Imports");

foreach (EdmFunction edmFunction in container.FunctionImports)

{

var parameters = FunctionImportParameter.Create(edmFunction.Parameters, code, ef);

string paramList = String.Join(", ", parameters.Select(p => p.FunctionParameterType + " " + p.FunctionParameterName).ToArray());

string returnTypeElement = "int";

if (edmFunction.ReturnParameter == null)

{

#>

<#=Accessibility.ForMethod(edmFunction)#> void <#=code.Escape(edmFunction)#>(<#=paramList#>)

{

<#

}

else

{

returnTypeElement = code.Escape(ef.GetElementType(edmFunction.ReturnParameter.TypeUsage));

#>

<#=Accessibility.ForMethod(edmFunction)#> ObjectResult<<#=returnTypeElement#>> <#=code.Escape(edmFunction)#>(<#=paramList#>)

{

<#

}

foreach (var parameter in parameters)

{

if (!parameter.NeedsLocalVariable)

{

continue;

}

#>

ObjectParameter <#=parameter.LocalVariableName#>;

if (<#=parameter.IsNullableOfT ? parameter.FunctionParameterName + ".HasValue" : parameter.FunctionParameterName + " != null"#>)

{

<#=parameter.LocalVariableName#> = new ObjectParameter("<#=parameter.EsqlParameterName#>", <#=parameter.FunctionParameterName#>);

}

else

{

<#=parameter.LocalVariableName#> = new ObjectParameter("<#=parameter.EsqlParameterName#>", typeof(<#=parameter.RawClrTypeName#>));

}

<#

}

if (edmFunction.ReturnParameter == null)

{

#>

base.ExecuteFunction("<#=edmFunction.Name#>"<#=code.StringBefore(", ", String.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray()))#>);

}

<#

}

else

{

#>

return base.ExecuteFunction<<#=returnTypeElement#>>("<#=edmFunction.Name#>"<#=code.StringBefore(", ", String.Join(", ", parameters.Select(p => p.ExecuteParameterName).ToArray()))#>);

}

<#

}

}

region.End();

...

#8 Victor on 4.18.2010 at 6:31 PM

Markus, thx.

#9 SlimShaggy on 4.24.2010 at 6:18 AM

Julie,

When I follow the steps you outlined to map a sproc that returns a bigint, I get a function that returns an ObjectResult<long?>, although the Column Information displays "nullable - false". Why does it make the result nullable, and is there any way to get just an ObjectResult<long>? (Actually it's just a single long that I want, not a collection of longs, but that looks impossible in EF4, although in Linq to SQL it was.)

#10 Julie on 4.24.2010 at 8:30 AM

@SS - A good question that, unfortunately, I don't have an answer for. I'd recommend asking on the MSDN forums for EF since lots of team members hang out there. And please, can you come back and provide the url to the thread here since I'd love to know myself?

#11 SlimShaggy on 4.24.2010 at 9:37 AM

Thank you for your suggestion. Here's the URL: social.msdn.microsoft.com/.../8be637a1-3f0c-4

#12 Jamie on 9.05.2010 at 12:07 PM

Is there a way to map a SP against an Entity? Having all your stored procedures against the context might make it a little unmanageable. E.g. context.Orders.TotalOrdersSinceDate()

#13 Julie on 9.05.2010 at 12:24 PM

@Jamie - kind of. Function Import let's you map a sproc to an existing entity but you still have to call it as a function (e.g., context.TotalOrdersSinceDate) and a function is not the same as a query.