none
Trying to create a GetList method that calls a stored proc C# (EF6 DB first) RRS feed

  • Question

  • I am using EF6 DB first with a class library that has a DataModel.edmx in it. I have a customers table and created a stored proc in the database and updated model from database.

    I have another class library that references my data library but this class library will have all methods for each entity. I was able to create methods like GetByID with no problem but can't figure out the best way to return a list with my stored proc

    below is my code can someone please show me the proper way to do this because it errors out.

            public DataLibrary.Customers_GetList_Result GetList() {
                using (var context = new DataLibrary.DataEntities()) {
                    //Execute stored procedure as a function
                    var cList = context.Customers_GetList().ToList<DataLibrary.Customers_GetList_Result>();
                    return cList;
                }
            }

    Monday, May 19, 2014 3:36 AM

Answers

  • Not sure where you work but GetLists become extremely complex.

    You can do the same thing with Entity-SQL. And I have worked across the US using MS SQL Server sprocs and Oracle packages, which I have learned to hate over the years. Those little rabbits that keep growing, growing and growing in abundance -- non stop until no one knows what 3/4 of them are doing anymore.

    Try using Linq to do several joins and all kinds of other things.

    I don't, and I would go to Entity-SQL over T-SQL that allows one to stay within the EF realm with querying the model and do everything T-SQL does when it comes to querying  and not use Linq.

    Also for security reasons some companies use stored procs.

    Yeah, DBA(s) out of control is what that's about. Yeah, I have encountered that too - a bunch of nonsense.

    Tuesday, May 20, 2014 3:42 AM

All replies

  • Why would you use a sproc to do this? What's wong with Linq? Why would you use EF if all you are doing is calling sprocs? This can be done wihout using EF with just using stright-up ADO.NET, SQL Commnad Objects calling the sproc, ADO.NET Datareader, populaing DTO(s) and retuning the DTO(s) in a List<T>.

    Monday, May 19, 2014 7:52 PM
  • What's the error? And does Customers_GetList() return a DataLibrary.Customers_GetList_Result? If not you will need to poject DataLibrary.Customers_GetList_Result instances out of the returned data.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, May 19, 2014 8:30 PM
  • Not sure where you work but GetLists become extremely complex. Try using Linq to do several joins and all kinds of other things. Also for security reasons some companies use stored procs.
    Tuesday, May 20, 2014 12:35 AM
  • Not sure where you work but GetLists become extremely complex.

    You can do the same thing with Entity-SQL. And I have worked across the US using MS SQL Server sprocs and Oracle packages, which I have learned to hate over the years. Those little rabbits that keep growing, growing and growing in abundance -- non stop until no one knows what 3/4 of them are doing anymore.

    Try using Linq to do several joins and all kinds of other things.

    I don't, and I would go to Entity-SQL over T-SQL that allows one to stay within the EF realm with querying the model and do everything T-SQL does when it comes to querying  and not use Linq.

    Also for security reasons some companies use stored procs.

    Yeah, DBA(s) out of control is what that's about. Yeah, I have encountered that too - a bunch of nonsense.

    Tuesday, May 20, 2014 3:42 AM
  • When you have entity framework in your project, best solution is to use LINQ for fetching results something like this

    var output= from  cust in context.Customers where <your condition and joins> select new {cust.colun-names};

    Tuesday, May 20, 2014 4:19 AM