Is it possible to run arbitrary queries with Linq

问题:

How would I write the following query using Linq to SQL?

UPDATE [TB_EXAMPLE] SET [COLUMN1] = 1

(My actual goal is more complex than this)

回答1:

The DataContext class has two query methods, ExecuteCommand, and ExecuteQuery.

The ExecuteQuery method returns LINQ to SQL Entities, so you need to pass a type to it:

[VB.Net]
MyDataContext.ExecuteQuery(Of Product)("SELECT * FROM PRODUCTS")

[C#]
MyDataContext.ExecuteQuery<Product>("SELECT * FROM PRODUCTS");

However, the ExecuteCommand doesn't need a type, you'd use this method for your UPDATE query Because you only need a String to write the query, you can use reflection to make a really generic UPDATE method for your DAL.

MyDataContext.ExecuteCommand("UPDATE Products WHERE ProductID = {0}",1) 

or

MyDataContext.ExecuteCommand("UPDATE Products WHERE ProductID = 1") 

回答2:

Yes. The DataContext has an ExecuteCommand method that will allow you to execute arbitrary (hopefully, parameterized) SQL.

Quoting from the remarks on the DataContext link above:

This method is a pass-through mechanism for cases where LINQ to SQL does not adequately provide for a particular scenario.

The syntax for the command is almost the same as the syntax used to create an ADO.NET DataCommand. The only difference is in how the parameters are specified. Specifically, you specify parameters by enclosing them in braces ({…}) and enumerate them starting from 0. The parameter is associated with the equally numbered object in the parameters array.

回答3:

foreach(var TB_EXAMPLE ex dbDataContext.TB_EXAMPLES)
{
   ex.COLUMN1 = 1;
}

dbDataContext.SubmitChanges();

回答4:

Assuming a DataContext connection, you can use the ExecuteCommand method of a DataContext object to execute SQL commands that do not return objects.

http://msdn.microsoft.com/en-us/library/bb386906.aspx

回答5:

var row = (from t in dataContext.table
            where t.id==1
            select t).Single();
row.columnName = 1;
dataContext.SubmitChanges();

edt, oops to slow ;)

回答6:

You can update the object directly then call save changes.

using (var ctx = new MyDataContext())
{
  var customer = ctx.Customers.First();
  customer.Name = "New Name";
  ctx.SaveChanges();
}

If you're looking to do this is and actual SQL command then Scott Guthrie has a great post that demonstrates using the ExecuteQuery and ExecuteCommand methods at http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx