Updating multiple rows using LINQ

Question

hi!

 

I am new to LINQ, can anybody tell me how can i update multiple records using LINQ without having to iterating using loop.

 

 

DataClassesDataContext ds = new DataClassesDataContext();

 

var employees = ds.Employees.Where(em => em.City.StartsWith("B"));

 

foreach (var emp in employees)

{

emp.City = "ABC";

}

 

ds.SubmitChanges();

 

 

Thanks.

Answer 1

 

Hi Shabih!

 

Do you mean somethign like this:

 

Code Snippet

using (NorthwindDataContext data = new NorthwindDataContext())

{

       var employees = data.Employees.Where(em => em.City.StartsWith("B")).ToList();

       employees.ForEach(em => em.City = "ABC");

}

 

 

 

But this is sitll loop... As we are on C# side and not on SQL ForEach() method is executed with loop internaly.

 

Yuo can use also this line but the effect is same...

Code Snippet

employees.Where(em => { em.City = "ABC"; return true; });

 

 

 

Hope this helps

Answer 2

 

Thanks Galin!

 

but what kind of SQL will this generate.

I mean will it generate multiple update statments for each employee

UPDATE Employee Set FirstName = 'ABC' WHERE EmployeeID = ..

.

.

 

or

 

a single one. like

 

UPDATE Employee Set FirstName = 'ABC' WHERE FirstName Like 'B%'

Answer 3

I mean will it generate multiple update statments for each employee UPDATE Employee Set FirstName = 'ABC' WHERE EmployeeID = ..

By examples in my previous post will cause generating multiple queries like this for each updated employee when

SubmitChanges() method is called

 

 

you can always execute custom query like this...This definetely will be executed faster...

Code Snippet

using (NorthwindDataContext data = new NorthwindDataContext())

{

     data.ExecuteCommand("UPDATE Employee Set FirstName = '{0}' WHERE FirstName Like '{1}%'", "ABC","B")

}

 

 

 

Hope this helps

 

Answer 4

 

LINQ to SQL does not give you a way to specify a set-based update against the database.  The only way to update multiple entities/rows is to retrieve, modify and update them via individual SQL UPDATE statements when you call SubmitChanges.

 

You can, however, use SQL directly to do this.

 

 

 

Answer 5

So to call a SPROC that updates multiple records I have to switch back to good old ADO.net.

Is Linq to SQL useless in this case.

Answer 6

How about?

 

Code Snippet

dc.Update<Employee>(

(where => where.City.StartsWith("B")),

(set => set.City == "ABC")

);

 

 

 

...resulting in a single update statement:

 

Code Snippet

update dbo.Employees  set [City] = @p0 where EmployeeID in (SELECT [t0].[EmployeeID]
FROM [dbo].[Employees] AS [t0]
WHERE [t0].[City] LIKE @p1)

 

 

 

A code sample showing an implementation of this is available here:

http://blog.huagati.com/res/index.php/2008/11/05/architecture-linq-to-sql-and-set-based-operations-update-statements/

Answer 7

Entity Framework is not LINQ... there is no Update method in LINQ

Answer 8

This is possible to update multiple records at a single time. Just assign a binding source to datagridview and update values in datagrid . Now call SubmitChanges() and its done. 

 

Answer 9

I would like to ask how this code become when i have to join some tables and in WHERE statement put more than one equals for example a have this code: 

OrdersDataContext contex = new OrdersDataContext();

             var query = from d in contex.Discounts
                        join r in contex.Registrations on d.id_reg equals r.id
                        join pr in contex.Products on d.id_prod equals pr.id
                        join p in contex.pTs on pr.pId equals p.id
                        join dim in contex.dimTs on p.dimId equals dim.id
                        join t in contex.typeTs on dim.typeId equals t.id
                        join c in contex.codeTs on t.codeId equals c.id
                        where (r.fullname == txtCompName.Text && r.password == txtpass.Text && c.code == TextBox1.Text && t.type == TextBox2.Text && dim.dimension == TextBox3.Text)
                        select new
                        {
                            CompanyName = r.fullname,
                            Article = pr.article,
                            Description = pr.description,
                            Price = pr.price,
                            Discount = d.discounts
                        };

             foreach (var disc in query)
             {
                 contex.ExecuteCommand("UPDATE Employee Set discounts = '{0}' WHERE discounts Like '%{1}%'", txtnewdiscount.Text, ....????????????????);
             }

i am selecting some rows in gridview and i want to change the discounts in this rows at once with the value in txtnewdiscount text box. 

Can anyone please help me????