Library Powered By

This library is powered by Entity Framework Extensions

What's EntityFrameworkExtras?

EntityFrameworkExtras provides some useful additions to EntityFramework, such as executing Stored Procedures with User-Defined Table Types and Output Parameters.

Executing a Stored Procedure with a User Defined Table Type

• Define a stored procedure class

[StoredProcedure("storedproc_AddMemberWithAddresses")]
public class AddMemberStoredWithAddressesProcedure
{
        [StoredProcedureParameter(SqlDbType.NVarChar, ParameterName = "ForeName")]
	public string FirstName { get; set; }

	[StoredProcedureParameter(SqlDbType.NVarChar,ParameterName = "SurName")]
	public string LastName { get; set; }

	[StoredProcedureParameter(SqlDbType.Int)]
	public int Age { get; set; }

	[StoredProcedureParameter(SqlDbType.Udt)]
	public List<Address> Addresses { get; set; }
}

• A User Defined Table Type parameter is declared as a List<> (List<Address>). The UDT will also require some attributes:

[UserDefinedTableType("udt_Address")]
public class Address
{
	[UserDefinedTableTypeColumn(1)]
	public string Line1 { get; set; }

	[UserDefinedTableTypeColumn(2)]
	public string Line2 { get; set; }

	[UserDefinedTableTypeColumn(3)]
	public string Postcode { get; set; }
}

• Execute the Stored Procedure with either a DbContext or an ObjectContext

DbContext context = new DbContext("ConnectionString");

var proc = new AddMemberStoredWithAddressesProcedure()
	{
		FirstName = "Michael",
		LastName = "Bovis",
		Age = 26,
		Addresses = new List<Address>()
		{
			new Address() {Line1 = "16", Line2 = "The Lane", Postcode = "MA24WE"}
		}
	};

context.Database.ExecuteStoredProcedure(proc);

Executing a Stored Procedure with an Output parameter

• To add an Output parameter, you need to set the Direction parameter to ParameterDirection.Output.

[StoredProcedure("storedProc_GetOldestAge")]
public class GetOldestAgeStoredProcedure
{
	[StoredProcedureParameter(SqlDbType.Int, Direction = ParameterDirection.Output)]
	public int Age { get; set; }
}

• Execute the Stored Procedure, and the parameter will be set to the output parameter value

var proc = new GetOldestAgeStoredProcedure();

context.Database.ExecuteStoredProcedure(proc);

int age = proc.Age; //Is now the oldest age

Useful links

• Website
• Download
• NuGet Entity Framework Core
• Nuget Entity Framework 6
• Nuget Entity Framework 5
• Nuget Entity Framework 4

Contribute

The best way to contribute is by spreading the word about the library:

• Blog it
• Comment it
• Star it
• Share it

A HUGE THANKS for your help.

More Projects

• Projects:
  • EntityFramework Extensions
  • Dapper Plus
  • C# Eval Expression
• Learn Websites
  • Learn EF Core
  • Learn Dapper
• Online Tools:
  • .NET Fiddle
  • SQL Fiddle
  • ZZZ Code AI
• and much more!

To view all our free and paid projects, visit our website ZZZ Projects.