none
Mapping SPROC manually as a member in table in LINQ RRS feed

  • Question

  • Hello - I am trying to add my stored procedure manually as a member of one of my class. 
    But it is throwing an error during runtime, the error is "Could not find key . The key may be wrong or the field or property on 'Sample' has changed names."

    To describe more:
    I have usp_Books and I have Student table. 

    The usp_Books is also added in the datacontext. Below is the auto-generated code of "uspBooks":

    public partial class usp_SelectBooksResult
    {
    private string _BookIsdnId;
    private string _Book;
    private string _Author;
    public usp_SelectBookResult()
    {
    }
    [Column(Storage="_BookIsdnId", DbType="Char(10) NOT NULL", CanBeNull=false)]
    public string BookIsdnId
    {
    get
    {
    return this._BookIsdnId;
    }
    set
    {
    if ((this._BookIsdnId != value))
    {
    this._BookIsdnId = value;
    }
    }
    }
    [Column(Storage="_Book", DbType="NVarChar(100) NOT NULL", CanBeNull=false)]
    public string Book
    {
    get
    {
    return this._Book;
    }
    set
    {
    if ((this._Book != value))
    {
    this._Book = value;
    }
    }
    }
    [Column(Storage="_Author", DbType="NVarChar(110) NOT NULL", CanBeNull=false)]
    public string Author
    {
    get
    {
    return this._Author;
    }
    set
    {
    if ((this._Author != value))
    {
    this._Author = value;
    }
    }
    }
    }


    Below is the auto-generated code of "Student":

    [Table(Name="dbo.Students")]
    public partial class Student : INotifyPropertyChanging, INotifyPropertyChanged
    {
    private int _StudentID;
    private string _BookIsdnId;
    private string _Name;

    public Student ()
    {
    OnCreated();
    }

    [Column(Storage="_StudentID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
    public int StudentID
    {
    get
    {
    return this._StudentID;
    }
    set
    {
    if ((this._StudentID != value))
    {
    this.OnStudentIDChanging(value);
    this.SendPropertyChanging();
    this._StudentID = value;
    this.SendPropertyChanged("StudentID");
    this.OnStudentIDChanged();
    }
    }
    }

    [Column(Storage="_BookIsdnId", DbType="VarChar(50) NOT NULL", CanBeNull=false)]
    public string BookIsdnId
    {
    get
    {
    return this._BookIsdnId;
    }
    set
    {
    if ((this._BookIsdnId != value))
    {
    this.OnAssetIDChanging(value);
    this.SendPropertyChanging();
    this._BookIsdnId = value;
    this.SendPropertyChanged("_BookIsdnId");
    this.OnBookIsdnIdChanged();
    }
    }
    }

    [Column(Storage="_Name", DbType="VarChar(50)")]
    public string Name
    {
    get
    {
    return this._Name;
    }
    set
    {
    if ((this._Name != value))
    {
    this.OnNameChanging(value);
    this.SendPropertyChanging();
    this._Name = value;
    this.SendPropertyChanged("Name");
    this.OnNameChanged();
    }
    }
    }

    }

    So, I added another partial class for Student in separate file because I don't want to mess with auto-generated code

    public partial class Student
        {

            private EntityRef<usp_SelectBooksResult> _Books;

            
            [Association(Name = "usp_SelectBooksResult_Student", Storage = "_Books", ThisKey = "BookIsdnId", OtherKey = "BookIsdnId", IsForeignKey = true)]
            public usp_SelectBooksResult Books
            {
                get
                {
                    return this._Books.Entity;
                }
                set
                {
                    if ((this._Books.Entity != value))
                    {
                        this.SendPropertyChanging();
                        this._Books.Entity = value;
                        this.SendPropertyChanged("Books");
                    }
                }
            }
        }


    But during runtime and I am trying to read the Student, it is throwing me an error 
    "Could not find key member 'BookIsdnId' of key 'BookIsdnId' on type 'Student'. The key may be wrong or the field or property on 'Student' has changed names."

    Do you know what is missing? What did I overlook?

    Thursday, October 15, 2009 2:43 PM

Answers

  • Hi,

     

    Thanks for providing me with more detailed information.  

     

    It is not recommended to map such a stored procedure as an association between the LINQ to SQL data classes.  The class usp_SelectBooksResult which is auto-generated is used to hold the return data from the stored procedure usp_SelectedBooks.  It is not mapping to any data tables or data views in the database.  And also it does not contains the primary key column definition.  That’s why we receive the exception. 

     

    Once the stored procedure is dragged into the O/R designer, it is actually built as a function call under the certain DataContext, like the following auto-generated codes: (I assume the stored procedure is getting books based on the ISDN ID)

    ==========================================================================================
    [Function(Name="dbo.usp_SelectBooks")]

    public ISingleResult<usp_SelectBooksResult> usp_SelectBooksResult([Parameter(Name="BookIsdnId", DbType="Char(10)")] string bookIsdnId)

    {

        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), bookIsdnId);

        return ((ISingleResult<usp_SelectBooksResult>)(result.ReturnValue));

    }
    ==========================================================================================

     

    If you need to use such a method in the data class Student, here is one workaround by declaring new data properties:

    ==========================================================================================

        public partial class Student

        {

            private List<usp_SelectBooksResultResult> _Books;

     

            public List<usp_SelectBooksResultResult> Books

            {

                get

                {

                    if (this._Books == null)

                    {

                        using (DataClasses1DataContext db = new DataClasses1DataContext())

                        {

                            this._Books = db.usp_SelectBooksResult(this._BookIsdnId).ToList();

                        }

                    }

                    return this._Books;

                }

            }

     

            partial void OnBookIsdnIdChanged()

            {

                this._Books = null;

            }

        }
    ==========================================================================================

     

    If you need further assistance, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, October 19, 2009 9:07 AM
    Moderator

All replies

  • Hi,

     

    Could you please tell me why you need to manually create the Association codes of the two tables?   The exception should be caused by incorrect definition at the usp_SelectBooksResult side, since we need to have association properties in both two data classes.   

     

    Instead, we can add such an association between the two tables in O/R designer very efficiently.  Then the certain source codes will be auto-generated for us.   Even there is no such foreign key at the database side, such association in the .dbml can still work fine. 

     

    For detail, please see How to: Create an Association (Relationship) Between LINQ to SQL Classes (O/R Designer).

     

    Have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, October 16, 2009 3:37 AM
    Moderator
  • Hi Lingzhi,
    The reason why I am doing this manually because I assumed it is not supported. When I tried dragging the sproc to my dbml, it is not showing in my dbml designer. When I tried adding the association between the usp_SelectBooksResult and Student and create association in the association editor, usp_SelectBooksResult is not showing either in parents or child dropdown box.  I checked the datacontext and the usp_SelectBooksResult is there but the attribute is Function. I thought that might be the reason why it is not showing.

    What is wrong?
    Friday, October 16, 2009 1:38 PM
  • Hi,

    Could you please clarify what kind of the sproc are you trying to put into the DataContext?  Also, it the usp_SelectBooksResult a real table or a sproc?  Please provide me with the sproc codes for further investigation.


    Best Regards,
    Lingzhi Sun  


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, October 16, 2009 1:42 PM
    Moderator
  • Hi - usp_SelectBooksResult is a stored procedure.

    code inside usp_SelectBooksResult

    BEGIN

    SELECT 
    A.BookIsdnId, A.Book, A.Author
    FROM BooksTracking.dbo.vw_Books A

    END


    In a nutshell, it is accessing a view in a different database.

    Friday, October 16, 2009 1:47 PM
  • Hi,

     

    Thanks for providing me with more detailed information.  

     

    It is not recommended to map such a stored procedure as an association between the LINQ to SQL data classes.  The class usp_SelectBooksResult which is auto-generated is used to hold the return data from the stored procedure usp_SelectedBooks.  It is not mapping to any data tables or data views in the database.  And also it does not contains the primary key column definition.  That’s why we receive the exception. 

     

    Once the stored procedure is dragged into the O/R designer, it is actually built as a function call under the certain DataContext, like the following auto-generated codes: (I assume the stored procedure is getting books based on the ISDN ID)

    ==========================================================================================
    [Function(Name="dbo.usp_SelectBooks")]

    public ISingleResult<usp_SelectBooksResult> usp_SelectBooksResult([Parameter(Name="BookIsdnId", DbType="Char(10)")] string bookIsdnId)

    {

        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), bookIsdnId);

        return ((ISingleResult<usp_SelectBooksResult>)(result.ReturnValue));

    }
    ==========================================================================================

     

    If you need to use such a method in the data class Student, here is one workaround by declaring new data properties:

    ==========================================================================================

        public partial class Student

        {

            private List<usp_SelectBooksResultResult> _Books;

     

            public List<usp_SelectBooksResultResult> Books

            {

                get

                {

                    if (this._Books == null)

                    {

                        using (DataClasses1DataContext db = new DataClasses1DataContext())

                        {

                            this._Books = db.usp_SelectBooksResult(this._BookIsdnId).ToList();

                        }

                    }

                    return this._Books;

                }

            }

     

            partial void OnBookIsdnIdChanged()

            {

                this._Books = null;

            }

        }
    ==========================================================================================

     

    If you need further assistance, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, October 19, 2009 9:07 AM
    Moderator
  • Thanks Lingzhi! 
    I will try this and will get back to you.
    Wednesday, October 21, 2009 7:41 PM
  • Hi,

    How is the problem now?   Do you need any further assistance?  If so, please feel free to let me know.

    Have a nice day!


    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, October 28, 2009 12:47 AM
    Moderator
  • Yes, it worked. Thanks!
    Wednesday, October 28, 2009 12:59 PM