在LINQ到SQL中执行查询

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

如何使用LINQ到SQL执行查询?这样的查询。

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 ?

这有可能吗?即使您的行和列被映射了?如何使用LINQ到SQL执行查询?

2 个解决方案

#1

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.

假设您已经将所有表拖到Linq2Sql设计器上，只需创建一个Student对象，将一个StudentLoan添加到它的StudentLoans集合，并将这个Student添加到上下文。学生使用myContextInstance.Students.InsertOnSubmit(newStudent)并通过调用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:

如果您的DataContext如下所示，则该代码段可以工作:

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

这是将表拖到dbml文件的设计表面的结果。

#2

If by your question you mean "how do a execute a raw SQL query with LINQ to SQL," then look at the ExecuteCommand and ExecuteQuery methods:

如果您的问题是“如何使用LINQ to SQL执行原始SQL查询，”然后查看ExecuteCommand和ExecuteQuery方法:

• ExecuteCommand - http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx
• ExecuteCommand——http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx
• ExecuteQuery (Overload 1) - http://msdn.microsoft.com/en-us/library/bb534292.aspx
• ExecuteQuery(重载1)- http://msdn.microsoft.com/en-us/library/bb534292.aspx
• ExecuteQuery (Overload 2) - http://msdn.microsoft.com/en-us/library/bb361109.aspx
• ExecuteQuery(重载2)- http://msdn.microsoft.com/en-us/library/bb361109.aspx

All these methods take a raw SQL query, like yours, and runs it against the database

所有这些方法都使用原始的SQL查询，比如您的SQL查询，并在数据库上运行它。

If you meant by your question "how do I write this SQL query as a LINQ query" then please clarify your question.

如果你的问题是“我如何把这个SQL查询写成LINQ查询”，那么请澄清你的问题。