How to Make ComplexType of Dynamic Query in Entity Framework?

By: Mayur
Updated: March 3, 2017 | Technology: Asp.Net MVC
How to Make ComplexType of Dynamic Query in Entity Framework?

Introduction

Problem

Solution

Introduction:

This article applies to those developers who use visual studio tools and Entity Framework for developing an MVC application, Here I am going to explain my problem which I face during the development of an MVC application using Entity Framework.

Problem:

Earlier when I write a simple procedure for my application, I easily make the ComplexType without any problem.
but after sometimes I need to make that procedure dynamic, I have made the change in procedure. So that, it returns the result of what I expect. But problem is that entity framework doesn’t provide me complexType for a dynamic procedure. When I fire click on get column information it just displays the message as shown below.

Problem Screen:

problem_screen

Solution:

After long research and changes, I found the solution. Here below, I listed all the steps for the setup.The steps for solution areas listed below.

  1. First of all, Make the Dynamic procedure in SQL server, execute the procedure, it will display the output as our expectations.
  2. Now, this step is very important.

    Comment the query code which we have written before execute.

    Go to message window that will show the query which returns the result what we want, copy it and paste it inside our procedure, as shown below.

    stp_2
  3. Now, move to visual studio, add/refresh entity data model.
  4. Go to a model browser.
  5. Select the procedure from a function.
  6. Double click or go to edit window.
  7. Click the get column information button it returns the result of a column, as shown below image. The create a new complex type that will add a complex type for the application.
    stp_8
  8. Remove the comment from the procedure which we comment first, put the comment on a query which we use forget result else remove the query code. Picture as shown below.
    stp_9

If you like this simple solution for creating a complex type for a dynamic query, share it on your social media to help other .NET MVC developers, who are facing this type issue. thanks a lot.