How to Make ComplexType of Dynamic Query in Entity Framework?

March 3, 2017 2 min read

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 the problem that I faced 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 needed to make that procedure dynamic, so I made the change in procedure.

So that, it returns the result of what I expect. But the problem is that the 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

Entity Framework edit function import dialog showing the stored procedure returns no columns message

Also Read: How to Implement jQuery Ajax Error handling in ASP.NET MVC Project

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 are listed below.

First of all, Make the Dynamic procedure in the SQL server, and execute the procedure, it will display the output as our expectations.
Now, this step is very important. Comment on the query code that we have written before executing. Go to the message window that will show the query that returns the result that we want, copy it, and paste it inside our procedure, as shown below.
Now, move to Visual Studio, and add/refresh the entity data model.
Go to a model browser.
Select the procedure from a function.
Double-click or go to the edit window.
Click the get column information button it returns the result of a column, as shown below image. They create a new complex type that will add a complex type to the application.
Remove the comment from the procedure in which we comment first, put the comment on a query which we use forget result else remove the query code. Picture as shown below.

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