Wriju's BLOG

.NET, Cloud and everything

ADO.NET Entity Framework : Adding User Defined Function in Storage Model

Sometimes you may want to use same logic again and again. Let’s suppose you do not have access to your database which allows you to write function or stored proc. Never mind, EF allows you to define the logic at Model and you should be able to experience the same.

You want to search an employee with its id. Open the edmx file in XML view and add the the below code under <schema>

<Function Name="GetEmployeeFN" IsComposable="false">
    Select * from Emp WHERE EmpId = @id
  <Parameter Name="id" Type="int"></Parameter>
And in the model browser you will find it just like when you import and SQL Stored Proc 
After that do a simple function import and get the power. 
using (TestDBEntities db = new TestDBEntities())
    foreach (var k in db.GetEmployeeFN(41))