• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

linq ,multi table

How do i get records from 2 tales in master detail  setup using linq eg what are linq commands?

I need the id number from master file if i know a 'name '

in detail file i need all records with the id that i get from master file
0
jagguy
Asked:
jagguy
  • 3
  • 2
1 Solution
 
Fernando SotoCommented:
Hi jagguy;

In order to help you out here we need to know how the two tables are constructed and the Primary Key and Foreign Key of each so that we can construct the query correctly.

Fernando
0
 
jagguyAuthor Commented:
as an example subject table has
PK sub-id , sub-name, FK student-id

the student gable has
PK student-id , student-name

I want a list of students names subjects so i need to join 2 tables with this set up

0
 
jagguyAuthor Commented:
sorry that should have read

as an example subject table has
PK sub-id , sub-name, FK student-id

the student table has
PK (student-id , student-name) all because whole row is unique

I want a list of students names subjects so i need to join 2 tables with this set up
0
 
Fernando SotoCommented:
Hi jagguy;

If I understand you correctly you have a composite Primary key defined for both tables but the Foreign Key only defines one of the unique constraint of the Primary Key which I believe is not allowed. I tried to create the tables in that way and it gave me an error due to the fact that I was not referencing both columns of the Primary Key. So for the following example I just placed the PK on the student-id and the sub-id columns.

You do not need to join the two table if there is a relationship between the two tables. For example lets say I have the following tables:

Table: Students
Student-Id         // Primary Key for the table
Student-Name
...

Table: Subjects
Sub-Id              // Primary Key for the table
Sub-Name
Student-Id       // Foreign Key to same field name in Student table
...

Then I can access each student and what subjects they have by doing what I did in the code snippet.

Fernando


private void button1_Click(object sender, EventArgs e)
{
    StudentSubjectDataContext studSub = new StudentSubjectDataContext();
 
    IEnumerable<Student> students = from student in studSub.Student
                                    orderby student.student_name
                                    select student;
 
    // Access each student record returned
    foreach (Student student in students)
    {
        Console.WriteLine("Student Name = {0}\tID = {1}", student.student_name, student.student_id);
        // Access each related record in the Subject table belonging to this student
        foreach (Subject subject in student.Subject)
        {
            Console.WriteLine("\tSubject ID = {0}\tSubject Name = {1}\tCredits = {2}",
                subject.sub_id, subject.sub_name, subject.credits);
        }
    }
}

Open in new window

0
 
Fernando SotoCommented:
Did this work out for you.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now