Executing Queries in LINQ to SQL

Question

How do I execute a query using LINQ to SQL? a query that something goes like this.

Let's say I have this table

CREATE TABLE dbo.Students
(
  StudentID INT IDENTITY(1,1) PRIMARY KEY,
  Name SYSNAME
);

CREATE TABLE dbo.StudentLoans
(
  LoanID INT IDENTITY(1,1) PRIMARY KEY,
  StudentID INT FOREIGN KEY REFERENCES dbo.Students(StudentID),
  Amount BIGINT -- just being funny
);

Then I wanted to execute this query.

DECLARE 
  @Name       SYSNAME = N'user962206', 
  @LoanAmount BIGINT = 50000,
  @StudentID  INT;

INSERT dbo.Students(Name) 
  SELECT @Name;

SELECT @StudentID = SCOPE_IDENTITY();

INSERT dbo.StudentLoans(StudentID, Amount)
  SELECT @StudentID, @LoanAmount;

Is that possible? even if your Rows and Columns are mapped? how can I execute that query with LINQ to SQL ?

spender · Accepted Answer

It's been a while, but wouldn't it be something like this?

Assuming you've dragged all your tables onto the Linq2Sql designer, simply create a Student object, add a StudentLoan to its StudentLoans collection, and add the Student to the Context.Students collection with myContextInstance.Students.InsertOnSubmit(newStudent) and write out the changes with a call to myContextInstance.SubmitChanges.

So, putting it all together:

using(var myContextInstance=new YourContext())
{
    var student = new Student(){Name = "user962206"};
    var studentLoan = new StudentLoan(){Amount = 50000};
    student.StudentLoans.Add(studentLoan);
    myContextInstance.Students.InsertOnSubmit(student);
    myContextInstance.SubmitChanges();
}

The code snippet works if your DataContext looks like this:

enter image description here

This is the result of just dragging your tables to the design surface of the dbml file.

Executing Queries in LINQ to SQL

Answers (2)

Related Questions