Practical Introduction To Entity Framework: Day 2

I have already given an introduction to Entity Framework in Day 1; to view the Day 1 Article click here.

Executing Stored Procedure using Entity Framework

First write a Stored Procedure in your database as in the following.

This procedure takes one input parameter @MerchantID and returns the Name and Address of the respective merchant id.

Create PROCEDURE [dbo].[USP_GetMerchantDetails] --1
(
   @MerchantID int
)
AS
    BEGIN
        select Name, Address from Merchant where id=@MerchantID
    END

To add an entity model to your project check Entity Framework in Day1.

You can select the procedure at the time of creation of the entity model.

Otherwise you can add it after the creation of the project.

Use the following procedure to create a sample.

1. Double-click on Day2Model.dmx.
   
   
   
   
2. Then you will get a edmx diagram screen. Right-click on the that screen and select model browser.
   
   
   
   
3. Then right-click on Stored Procedure of “MyModel.store” and select “Update Model from Database”.
   
   
   
   
4. Check your procedure and finish, then automatically your procedure execution function will be created. You can change the return type also after addition of the procedure.
   
   
   

Now your model browser looks like:



Entity type: Merchant - consider as simple Merchant Table
Complex Type: USP_GetMerchantDetails_Result - Return Type of Stored Procedure, we can use this when we want to return multiple table columns, like M.MerchantName and p.ProductName.
Function Imports: USP_GetProductDetails - execute function
Same in Stored Procedure: USP_GetProductDetails - you will get the option on this procudre to change and add functions

Check your code generated in Day2Model.Designer.cs.

public ObjectResult<USP_GetMerchantDetails_Result> USP_GetMerchantDetails(Nullable<global::System.Int32> merchantID)
{
    ObjectParameter merchantIDParameter;
    if (merchantID.HasValue)
    {
        merchantIDParameter = new ObjectParameter("MerchantID", merchantID);
    }
    else
    {
                merchantIDParameter = new ObjectParameter("MerchantID", typeof(global::System.Int32));
            }

            return base.ExecuteFunction<USP_GetMerchantDetails_Result>("USP_GetMerchantDetails", merchantIDParameter);
        }

So now call the auto-generated procedure function and execute the Stored Procedure.

Complex Return Type

MyModel.MyEntities Obj = new MyModel.MyEntities();
var qq = Obj.USP_GetMerchantDetails(1); //1 is merchantid

Debug result:



Entity Return Type

To add a new function for a Stored Procedure click on:



Now click the Entity dropdown and select respective Entity.



The following is the code to call this function:

MyModel.MyEntities Obj = new MyModel.MyEntities();
var Result = Obj.USP_GetMerchantDetails_Entity(1);

Every time check your generated code in the designer.cs file.

The difference between complex type functions and entity type functions to execute a procedure.

1. Everything is the same except return type

The following is the auto-generated code of the entity type and complex type:

Paging in Entity Framework

Use custom paging to increase performance.

Just mention the current page number, page size and entity to return the current page record.

protected void Page_Load(object sender, EventArgs e)
{
   MyModel.MyEntities Obj = new MyModel.MyEntities();
   ObjectSet<Merchant> MerchantList = Obj.Merchants;
   var CurrentRecords = GetCurrentPageRecords(3, 2, MerchantList);
}
public IEnumerable<Merchant> GetCurrentPageRecords(int CurrentPageNumber, int PageSize, ObjectSet<Merchant> MerchantList)
{
   return MerchantList.OrderBy(s => s.Id).Skip(PageSize * CurrentPageNumber - 1).Take(PageSize).Select(a => a);
}

Sorting in Entity Framework

Just mention the current column Name, order by state, page number, page size and entity to return the current page record with sort.

var CurrentRecordswithsort = GetSortingRecords("name_desc",3, 2, MerchantList);

public IEnumerable<Merchant> GetSortingRecords(string ColumnNameWithAscDesc,int CurrentPageNumber, int PageSize ,ObjectSet<Merchant> MerchantList)
    {
        switch (ColumnNameWithAscDesc.ToLower())
        {
            case "id":
                return MerchantList.OrderBy(s => s.Id).Skip(PageSize * (CurrentPageNumber - 1)).Take(PageSize).Select(a => a);
             case "id_desc":
                return MerchantList.OrderByDescending(s => s.Id).Skip(PageSize * (CurrentPageNumber - 1)).Take(PageSize).Select(a => a);
            case "name":
                return MerchantList.OrderByDescending(s => s.Name).Skip(PageSize * (CurrentPageNumber - 1)).Take(PageSize).Select(a => a);
            case "name_desc":
                return MerchantList.OrderByDescending(s => s.Name).Skip(PageSize * (CurrentPageNumber - 1)).Take(PageSize).Select(a => a);
            default:
                return MerchantList.OrderBy(s => s.Id).Skip(PageSize * CurrentPageNumber - 1).Take(PageSize).Select(a => a);
        }

    }

Please find the attached source code and enjoy Entity Framework.

Thanks for reading!!!