IMultipleResults using LINQ

There may be situation where you want to retrieve multiple result from a single stored procedure, this was easy process using previous dataset and SQLConnection and SQLParameters, but in LINQ we need to do some coding to get it worked.

The LINQ only support SingleResult by default so inorder for your LINQ.dbml to support multipleresults you need to do a manual coding to get it done.

Below are the following steps to get it done..

Step 1:  First of all you need to retrieve the singleresultset class template.. Usually when you drag and drop a particular storedprocedure into the dbml file it generates a singleresultset class template in the form of partial class. Make a copy of each partial resultset. The singleresultset class template usually is available in the SluiceDb.designer.cs.

The sample of singleresultset template

public partial class AssetsProject_SelectByAssetSearchResult
    {

        private int? _ID;

        private int? _AssetsID;

        private string _Title;
       
        private string _FileName;

        private string _URL;

        private string _CreatedBy;

        private string _UploadedDate;

        private string _FileSize;

        public AssetsProject_SelectByAssetSearchResult()
        {
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_ID", DbType = "Int")]
        public int? ID
        {
            get
            {
                return this._ID;
            }
            set
            {
                if ((this._ID != value))
                {
                    this._ID = value;
                }
            }
        }


        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_AssetsID", DbType = "Int")]
        public int? AssetsID
        {
            get
            {
                return this._AssetsID;
            }
            set
            {
                if ((this._AssetsID != value))
                {
                    this._AssetsID = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_Title", DbType = "NVarChar(4000)")]
        public string Title
        {
            get
            {
                return this._Title;
            }
            set
            {
                if ((this._Title != value))
                {
                    this._Title = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_FileName", DbType = "NVarChar(350)")]
        public string FileName
        {
            get
            {
                return this._FileName;
            }
            set
            {
                if ((this._FileName != value))
                {
                    this._FileName = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_URL", DbType = "NVarChar(350)")]
        public string URL
        {
            get
            {
                return this._URL;
            }
            set
            {
                if ((this._URL != value))
                {
                    this._URL = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_CreatedBy", DbType = "NVarChar(101)")]
        public string CreatedBy
        {
            get
            {
                return this._CreatedBy;
            }
            set
            {
                if ((this._CreatedBy != value))
                {
                    this._CreatedBy = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_UploadedDate", DbType = "NVarChar(30)")]
        public string UploadedDate
        {
            get
            {
                return this._UploadedDate;
            }
            set
            {
                if ((this._UploadedDate != value))
                {
                    this._UploadedDate = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_FileSize", DbType = "NVarChar(33)")]
        public string FileSize
        {
            get
            {
                return this._FileSize;
            }
            set
            {
                if ((this._FileSize != value))
                {
                    this._FileSize = value;
                }
            }
        }
    }

Step 2: After you are done with creating the partial resulset for each resultset available in the stored procedure of multiple resultset, delete the stored procedure from the dbml which was helpful for creating the resultset class template. Since you are going to create the class template and the way to manipulate it manually you can always delete those copy of the stored procedure which was helpful to create resultset class template  rrom the dbml which otherwise will create a replicated copy which we dont need.

Step 3:  Copy and paste the copy of each singleresultset class template in SluiceDb.cs.

Step 4: Having known the template of each resultset available in the stored procedure you can create your own function to retrieving those multiple resultsets.

 [FunctionAttribute(Name = "dbo.Requirements_SelectByAssetSearch")]
        [ResultType(typeof(AssetsProject_SelectByAssetSearchResult))]
        [ResultType(typeof(AssetsRequirement_SelectByAssetSearchResult))]
        public IMultipleResults AssetsProjectRequirement(int projectId,int? requirementId, int? Id, string title,int owner)
        {
                IExecuteResult result = ExecuteMethodCall(this, ((MethodInfo)(MethodBase.GetCurrentMethod())), projectId, requirementId, Id, title, owner);
            if (result != null) return (IMultipleResults)(result.ReturnValue);
            return null;
        }

Step 5: Having we are done with manual invocation of Multiple resultset itz time to know how to invoke from the front end aspx page.

  using (SluiceDbDataContext sluiceDc = new SluiceDbDataContext())

                        {

                            IMultipleResults projectRequirement = sluiceDc.AssetsProjectRequirement(searchInfo.ProjectId, searchInfo.RequirementId, searchInfo.AssetId, searchInfo.Title, searchInfo.MemberId);

                            List projects =
                            projectRequirement.GetResult().ToList();

                            List requirements =
                            projectRequirement.GetResult().ToList();


                            rdgListAssetsProject.DataSource = projects;
                        }

For Refrence

http://beyondrelational.com/blogs/ibhadelia/archive/2010/07/17/how-to-get-multiple-result-set-of-procedure-using-linq-to-sql.aspx

For any queries you can very well email me...