locked
LinQ to SQL, Attach() method

    Question

  •  

    I tested following Code using Northwind Database and Attach() method.

     

    Northwind db2 = new Northwind(…);

    // Create a new entity for applying changes
    Customer C2 = new Customer();
    C2.CustomerID ="New";

    // Set other properties needed for optimistic concurrency check
    C2.CompanyName = "New Company Name Co.";

    C2.ContactName="Arnold";

    // Tell LINQ to SQL to track this object for an update; that is, not for insertion
    db2.Customers.Attach(C2);

    // Now apply the changes
    C2.ContactName = "Mary Anders";

    // DataContext now knows how to update the customer
    db2.SubmitChanges();

     

    Then i run the Sql profiler and then i got following Sql-statement.

     

       UPDATE [dbo].[Customers]
       SET [ContactName] = @p4
       WHERE ([CustomerID] = @p0) AND ([CompanyName] = @p1) AND ([ContactName] = @p2)
       AND ([ContactTitle] IS NULL) AND ([Address] = @p3)
       AND ([City] IS NULL) AND ([Region] IS NULL) AND ([PostalCode] IS NULL) AND
       ([Country] IS NULL) AND ([Phone] IS NULL) AND ([Fax] IS NULL)',
       N'@p0 nchar(5),@p1 nvarchar(10),@p2 nvarchar(3),@p3 nvarchar(5),
       @p4 nvarchar(11)',@p0=N'nCust',@p1=N'NewCompany',@p2=N'Arnold',
       @p3=N'seoul',@p4=N'Mary Anders'

     

    When the program updates customers table, in the where clause contains

    every columns in the customers table. is there anyway, i can query like this?

     

    Update ~

    Set ~

    Where CustomerID = @P1      // only key columns needed to query customers table.

     

    Thanks.


     

     

     

     

     

     

     

    Tuesday, November 20, 2007 4:16 AM

Answers

  • If you inspect your model in the designer more carefully, you'll notice that each property of your entity in the designer has a property named UpdateCheck which is set to Always by default. Change it to Never for all properties except CustomerID and you'll get your expected result.
    Tuesday, November 20, 2007 6:45 AM
  • Unless you have a technical reason to turn off update checks, leave them on.  These are for the optimistic concurrency checks, which prevent two simultaneous processes from clobbering each other's data accidentally.

     

    If you can guarantee there will never be two different processes attempting to update data, then you can turn them off, but there's not much reason to do so even then (what do you gain?).

    Tuesday, November 20, 2007 7:01 AM

All replies



  • Hi

    I test your written code but i got "Row not found or changed." exception and a update statement in profiler.
    I think because your record not exist in database and you attach your object (instead of db2.GetTable<Customer>().Add(C2)) it supposes that the record with id 'NewCustId' exist and try to update this, and because the Origin in C2 is empty , all fileds participate in update statement, and after that no record updated and because of concurrency check you get this error.


    Tuesday, November 20, 2007 5:13 AM
  •  

     

    Before execute the code , i executed following Sql Query. (sorry the customer id was 'New', not 'NewCustId')

     

     

    insert into customers(customerid,companyName,contactname)
    values('New','New Company Name Co.','Arnold')

    Tuesday, November 20, 2007 5:45 AM
  • If you inspect your model in the designer more carefully, you'll notice that each property of your entity in the designer has a property named UpdateCheck which is set to Always by default. Change it to Never for all properties except CustomerID and you'll get your expected result.
    Tuesday, November 20, 2007 6:45 AM
  • Unless you have a technical reason to turn off update checks, leave them on.  These are for the optimistic concurrency checks, which prevent two simultaneous processes from clobbering each other's data accidentally.

     

    If you can guarantee there will never be two different processes attempting to update data, then you can turn them off, but there's not much reason to do so even then (what do you gain?).

    Tuesday, November 20, 2007 7:01 AM
  •  

    Thanks..
    Tuesday, November 20, 2007 7:22 AM