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
jagguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
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 SotoRetiredCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
Did this work out for you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.