Use Linq to Generate Direct Update Without Select

Use linq to generate direct update without select

No, neither LINQ nor LINQ to SQL has set-based update capabilities.

In LINQ to SQL, you must query for the object you wish to update, update the fields/properties as necessary, then call SubmitChanges(). For example:

var qry = from product in dc.Products where Product.Name=='Foobar' select product;
var item = qry.Single();
item.Count = 0;
dc.SubmitChanges();

If you wish to do batching:

var qry = from product in dc.Products where Product.Type==1 select product;
foreach(var item in qry)
{
item.Count = 0;
}
dc.SubmitChanges();

Alternatively, you could write the query yourself:

dc.ExecuteCommand("update Product set Count=0 where Type=1", null);

Update without first selecting data in LINQ 2 SQL?

I haven't tried it, but it looks as if you can do this with Attach:

Table(TEntity).Attach Method (TEntity, Boolean)

You set up the object in its updated state and pass it in, setting the boolean param to true. Of course, then you have to set all the fields, not just the ones you want to change.

If you only want to change a field or two, you can call SQL directly with the ExecuteCommand and ExecuteQuery methods on a DataContext object. You have to pass in the SQL as a string, and one argument for each parameter in the SQL. Note in particular how the SQL string has to be constructed:

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 1. The parameter is
associated with the equally numbered
object in the parameters array.

Update using Linq query not working properly

You just try updating a projection when you do select new.
Instead, you should go for something like this:

var query = (from p in dbcontext.Users
where p.UserId == person.UserId
select p).SingleOrDefault();

And rest should be ok.

*Edit: For more info on Projection Operations (C#) and a more clear idea you can refer to:

https://msdn.microsoft.com/en-us/library/mt693038.aspx

LINQ combine select and update query

Is there a way to combine this operation in just one query?

Not in Linq - you could execute one SQL statement directly if there's a pattern to the updates, but querying and updating are two separate methods.

SubmitChanges not updating, but inserts new record. LINQ to SQL

You cannot do that. You need to get the record out of the database and then update that record. Then save it back. Like this:

else
{
// first get it
var query =
from ord in DataContext.Tenant_Cost_TBLs
where ord.lineToUpdateID = 636154619329526649
select ord;

// then update it
// Most likely you will have one record here
foreach (Tenant_Cost_TBLs ord in query)
{
ord.Cost_Amount = sDselectedRow.Cost_Amount;
// ... and the rest
// Insert any additional changes to column values.
}

}
try
{
DataContext.SubmitChanges();
}
catch (Exception ex)
{
Alert.Error("Did not save", "Error", ex);
}

Here is an example you can follow.

Or you can use a direct query if you do not want to select first.

DataContext.ExecuteCommand("update Tenant_Cost_TBLs set Cost_Amount =0 where ...", null);

Update Item without touching Values

Sure, use the Attach method. In your case probably this overload is appropriate.

This link gives some neat information.

Optimizing Repository’s SubmitChanges Method

If I understand your question, you are being passed an entity that you need to save to the database without knowing what the original values were, or which of the columns have actually changed.

If that is the case, then you have four options

  1. You need to go back to the database to see the original values ie perform the select, as you code is doing. This allows you to set all your entity values and Linq2Sql will take care of which columns are actually changed. So if none of your columns are actually changed, then no update statement is triggered.

  2. You need to avoid the select and just update the columns. You already know how to do (but for others see this question and answer). Since you don't know which columns have changed you have no option but set them all. This will produce an update statement even if no columns are actually changed and this can trigger any database triggers. Apart from disabling the triggers, about the only thing you can do here is make sure that the triggers are written to check the old and new columns values to avoid any further unnecessary updates.

  3. You need to change your requirements/program so that you require both old and new entities values, so you can determine which columns have changed without going back to the database.

  4. Don't use LINQ for your updates. LINQ stands for Language Integrated QUERY and it is (IMHO) brilliant at query, but I always looked on the updating/deleting features as an extra bonus, but not something which it was designed for. Also, if timing/performance is critical, then there is no way that LINQ will match properly hand-crafted SQL.



Related Topics



Leave a reply



Submit