locked
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.

    Thursday, January 31, 2008 9:18 AM

Answers

  •  

    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.

     

     

     

    Thursday, January 31, 2008 4:01 PM

All replies

  •  

    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
    Thursday, January 31, 2008 10:41 AM
  •  

    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%'

    Thursday, January 31, 2008 11:32 AM
  • 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

     

    Thursday, January 31, 2008 11:39 AM
  •  

    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.

     

     

     

    Thursday, January 31, 2008 4:01 PM
  • 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.
    Tuesday, September 02, 2008 3:24 PM
  • 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/

    Wednesday, November 05, 2008 10:42 AM
  • Entity Framework is not LINQ... there is no Update method in LINQ
    Friday, June 11, 2010 8:18 AM
  • 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. 

     

    Wednesday, September 21, 2011 7:19 PM
  • 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????

    Monday, July 02, 2012 7:14 AM