CODEWITHSUNDEEP
c#.netlinq-to-sql
Jader Dias
Jader Dias

Reputation: 90465

Is it possible to run arbitrary queries with Linq to SQL? (C#)

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)

Upvotes: 2

Views: 2877

Answers (6)

andy
andy

Reputation: 8875

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")

Upvotes: 8

bendewey
bendewey

Reputation: 40235

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

Upvotes: 0

Bj&#246;rn
Bj&#246;rn

Reputation: 29381

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

edt, oops to slow ;)

Upvotes: 1

tvanfosson
tvanfosson

Reputation: 532435

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.

Upvotes: 2

Mcbeev
Mcbeev

Reputation: 1529

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

Upvotes: 1

BFree
BFree

Reputation: 103740

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

dbDataContext.SubmitChanges();

Upvotes: 1

Related Questions