none
Stored Procedure return type in Linq to Sql RRS feed

  • Question

  • Hi,

    I have used interface repository architecture with Linq to SQL.So i have my own entity, interface & repository ( now DBML file also generate their own entities ).


    Now when i use stored procedure, it will return me as ISingleResult<uspSearchResult> like
    List<usp_Search_PublishersResult> lstPublishersSearchResult = this._db.usp_Search_Publishers(RowIndex, MaxRows, sortExpression, PublisherName).ToList();


    but i do not have access of usp_Search_PublishersResult at UI level as i am using my own autogenerated entities and i do not want to add data access layer(DBML) reference at UI.
    so i have choose like below option

    1. Convert list to my own custom list in which i am using my generated entities and then access that in UI

                List<usp_Search_PublishersResult> lstPublishersSearchResult = this._db.usp_Search_Publishers(RowIndex, MaxRows, sortExpression, PublisherName).ToList();

                List<Publisher> lstPublisher = new List<Publisher>();
                Publisher objPublisher = null;

                foreach (usp_Search_PublishersResult item in lstPublishersSearchResult)
                {
                    objPublisher = new Publisher();

                    objPublisher.PublisherID = item.PublisherID;
                    objPublisher.PublisherName = item.PublisherName;
                    objPublisher.ContactName = item.ContactName;
                    objPublisher.ContactEmail = item.ContactEmail;
                    objPublisher.PhoneNumber = item.PhoneNumber;
                    objPublisher.Address = item.Address;
                    TotalRowCount = item.MaxNum;

                    lstPublisher.Add(objPublisher);
                }

                return lstPublisher;

    but this will  be overhead as i need to do foreach now second option is

    2. Convert to custom list using linq query

                var lstPublishersSearchResult = this._db.usp_Search_Publishers(RowIndex, MaxRows, sortExpression, PublisherName).ToList();

                List<Publisher> lstPublisher = (from rs in lstPublishersSearchResult
                                                select new Publisher { PublisherID = rs.PublisherID }).ToList();

    My Question is

    1. what's performance impact of 1 & 2 ( is the 2 will also do iteration like foreach ).
    2. Can't we have different return type rather than only ( ISingleResult ).
    3. is thr any way by which we can copy one list to another ( of different type ) without any iteration?

    Thursday, April 15, 2010 4:27 AM

All replies

  • Whyt don't you simply change the return type of the procedure to Customer? As far as we can see from your question, you have this entity mapped and the procedure returns a full set of properties.
    This scenario will not need any conversion.
    If one is creating a new Customer entity in a stored procedure query, he needs to attach it to context then, and there are some problems with creating a set of instantiated strongly typed entities from a LINQ to SQL query.
    Devart Team
    http://www.devart.com/dotconnect
    ADO.NET data providers for Oracle, MySQL, PostgreSQL, SQLite with Entity Framework and LINQ to SQL support
    Thursday, April 15, 2010 2:27 PM