You must Sign In to post a response.
  • Category: LINQ

    Class Hierarchy using LINQ

    Hi,
    i need to bind relational data from sqlserver table into class hierarchy.
    providing below example for more clarification.

    tbl_Company
    Company_ID Com_Name
    1 Com1
    2 Com2

    tbl_Employee
    Employee_Id Emp_Name Company_Id
    1 Emp1 1
    2 Emp2 2
    3 Emp2 1

    tbl_Invoice
    Invoice_Id Invoice_Name Employee_Id
    1 Invoice1 1
    2 Invoice2 1
    3 Invoice3 2

    below are just add values into class using loop(Just for reference)
    for (int i = 0; i < 10; i++)
    {
    Company company = new Company("CN " + i.ToString());
    Employee emp1 = new Employee("Employee1 " + i.ToString(), i = i++);
    Employee emp2 = new Employee("Employee2 " + i.ToString(), i = i++);
    Invoice empCou = new Invoice("INV " + i.ToString(), i = i++);
    Invoice empCou1 = new Invoice("INV2 " + i.ToString(), i = i++);
    emp1.EmployeeCou.Add(empCou);
    emp1.EmployeeCou.Add(empCou1);
    emp2.EmployeeCou.Add(empCou1);
    company.Employees.Add(emp1);
    companies.Add(company);
    }

    how can i achieve the above scenario from sqlserver using LINQ(company class should contain hierarchical data based on foreign key relation)


    Thanks
  • #764745
    If you are using entity framework, you can do it easily. But In ADO.NET, there is no direct way to do this.

    You can follow logic as shown bellow

    1. Inner join the three tables order by companyid,invoiceid and empid and get the result together.

    2. Create Main Company generic collection list
    3. iterate the record set. and retrieve first record.
    4. Create the object for company,Emp,invoice
    5. Store the primary value of each object in different variable.
    6. Check the next record if already stored values equals the current record do not create the new object. It the values differs create new object.
    7. Add the object into the main company list.

    By Nathan
    Direction is important than speed

  • #764898
    Hai Santosh,
    You need to join these objects using join condition in the Linq. Once you join the tables, you can retrieve the records from the collection object.
    Below is the sample link for the join using Linq:

    http://www.dotnetperls.com/join
    https://msdn.microsoft.com/en-us/library/bb311040.aspx

    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com


Sign In to post your comments