Practical Introduction To Entity Framework: Day 2

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

Creating the stored procedure

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

Adding entity model to project

To add an entity model to your project check Entity Framework on 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 an edmx diagram screen. Right-click on that screen and select the 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 the addition of the procedure

Now your model browser looks like

1. Entity type: Merchant - considered as simple Merchant Table
2. 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.
3. Function imports: USP_GetProductDetails - execute function
4. Same in Stored procedure: USP_GetProductDetails - you will get the option on this procedure 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);
}

Calling the auto-generated procedure function

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

Everything is the same except the 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!!!

Go to part 3