I need to convert the result of a stored procedure in a dbml file to IQueryable to view a list in an MVC view

Question

I have a MVC project that has a Linq to SQL dbml class. It is a table called Clients that houses client information. I can easily get the information to display in a View using the code I followed in Nerd Dinner but I have added a stored procedure to the dbml and it's result set is of IQueryable, not IQueryable. I need to convert IQueryable to IQueryable so I can display it in the same View. The reason for the sproc is so I can pass a search string tothe sproc and return the same information as a full list but filtered on the search. I know I can use Linq to filter the whole list but I don't want the whole list so I am using the sproc.

Here is the code in my ClientRepository with a comment where I need to convert. What code goes in the commented spot.

        public IQueryable<Client> SelectClientsBySearch(String search)
    {
        IQueryable<SelectClientsBySearchResult> spClientList = (from p in db.SelectClientsBySearch(search) select p).AsQueryable();

        //what is the code to convert IQueryable<SelectClientsBySearchResult> to IQueryable<Client>

        return clientList;
    }

Answer 1

If your result type and your client type match (IOW, your sproc returns Select * from Client and you haven't modified the Client entity in the DBML since adding it), you can change the stored proc to automtically return IQueryable of Client instead of IQueryable of SelectClientsBySearchResult (see instructions below). Then no conversion is necessary.

If they are different, there are a few different approaches. You can modify the partial class for your entities to add conversion methods to them, or you can use extension methods. Both of these approaches avoid overwriting when the code behind the DBML is regenerated.

To change the stored proc return type:

The DBML designer created the SelectClientsBySearchResult type for your stored proc when you added the stored proc to the DBML. This is the default behavior if you drag the proc from the server explorer to the procedures pane. Instead, drag the stored proc from the server explorer and drop it on the Client table. Now its return type is a collection of Client instead.

I believe you can also change the return type after the fact (as it is now), but I don't have it installed right now and can't guide you through it.