none
Help with relational table with Stored Procedure. RRS feed

  • Question

  • Two tables, which have a 1 to many relationship (customer and orders).

    I have a DBML file which has a table. I write a query to get the data from the customers table into a List<Customer>. When i iterate through the results i usually find the related table and access the property via that method i.e. 

    foreach (customer cu in customers)
    {
    ....
    foreach (order or in cu.orders)
    {
    .....
    }
    }
    I decided to change this to a Stored Procedure, converted the ISingleResult to Customers by adding each row from the SP into the customer object and returning that as a list. Now when i write the same query as above the related tables are always null? What did i miss?
    Thursday, July 28, 2016 3:51 PM

Answers

  • Hi Pure Deal,

    If you want to one-to-many relationship, I would suggest that you could use EntitySet attribute instead of stored procedure. the following article provide a demo about linq to SQL one-to-many relationship for your reference.

    [Table(Name = "Customer")]
    public class clsCustomerWithAddresses
    {
        private int _CustomerId;
        private string _CustomerCode;
        private string _CustomerName;
        private EntitySet<clsAddresses> _CustomerAddresses;
    
        [Column(DbType="int",IsPrimaryKey=true)]
        public int CustomerId
        {
            set
            {
                _CustomerId = value;
            }
            get
            {
                return _CustomerId;
            }
        }
    
        [Column(DbType = "nvarchar(50)")]
        public string CustomerCode
        {
            set
            {
                _CustomerCode = value;
            }
            get
            {
                return _CustomerCode;
            }
        }
    
        [Column(DbType = "nvarchar(50)")]
        public string CustomerName
        {
            set
            {
                _CustomerName = value;
            }
            get
            {
                return _CustomerName;
            }
        }
    
        [Association(Storage = "_CustomerAddresses", 
          ThisKey="CustomerId", OtherKey = "CustomerId")]
        public EntitySet<clsAddresses> Addresses
        {
            set
            {
                _CustomerAddresses = value;
            }
            get
            {
                return _CustomerAddresses;
            }
        }
    }

    public class clsAddresses
    {
        private int _Customerid;
        private int _AddressId;
        private string _Address1;
        private EntityRef<clsPhone> _Phone;
        [Column(DbType="int")]
        public int CustomerId
        {
            set
            {
                _Customerid = value;
            }
            get
            {
                return _Customerid;
            }
        }
        [Column(DbType = "int", IsPrimaryKey = true)]
        public int AddressId
        {
            set
            {
                _AddressId = value;
            }
            get
            {
                return _AddressId;
            }
        }
        [Column(DbType = "nvarchar(50)")]
        public string Address1
        {
            set
            {
                _Address1 = value;
            }
            get
            {
                return _Address1;
            }
        }
        [Association(Storage = "_Phone", 
        ThisKey = "AddressId", OtherKey = "AddressId")]
        public clsPhone Phone
        {
            set
            {
                _Phone.Entity = value;
            }
            get
            {
                return _Phone.Entity;
            }
        }
    }

    http://www.codeproject.com/Articles/37784/One-Many-and-One-One-relationship-using-LINQ-to-SQ

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, July 29, 2016 3:31 AM
    Moderator