Skip to content

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

License

zzzprojects/EntityFrameworkExtras

master
Switch branches/tags
Code

Library Powered By

This library is powered by Entity Framework Extensions

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). 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

Contribute

Want to help us? Your donation directly helps us maintain and grow ZZZ Free Projects.

We can't thank you enough for your support 🙏.

👍 One-time donation

❤️ Become a sponsor

Why should I contribute to this free & open-source library?

We all love free and open-source libraries! But there is a catch... nothing is free in this world.

We NEED your help. Last year alone, we spent over 3000 hours maintaining all our open source libraries.

Contributions allow us to spend more of our time on: Bug Fix, Development, Documentation, and Support.

How much should I contribute?

Any amount is much appreciated. All our free libraries together have more than 100 million downloads.

If everyone could contribute a tiny amount, it would help us make the .NET community a better place to code!

Another great free way to contribute is spreading the word about the library.

A HUGE THANKS for your help!

More Projects

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

About

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

Topics

Resources

License

Stars

Watchers

Forks