Linq 2 MySQL

Linq 2 MySQL

Postby nilssonm » Wed 03 Dec 2008 14:47

Hi,

I am trying to update entities in DB which has lived longer than the context where they where fetched from. For this I am trying to use the Attach method on the Table object with the boolean flag modified set to true. The SubmitChanges method is not generating an update statement to the DB. What am I doing wrong or is this not implemented yet?

Regards,
Mats
nilssonm
 
Posts: 23
Joined: Mon 25 Aug 2008 15:08

Postby AndreyR » Thu 04 Dec 2008 15:08

Would you please post a short code sample illustrating the issue?
Or send me (andreyr * devart * com ) a small test project to reproduce the problem.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Example

Postby nilssonm » Thu 04 Dec 2008 16:06

Here is a small example of how you normally not do. The example simulates a fetch of entities and stored in session in one request and a modification and save in another request in an ASP.NET application.

Code: Select all
            // Fetch and print the employees
            IList employeeList = null;
            using (EmployeesDataContext db = new EmployeesDataContext())
            {
                var employees = from e in db.Employees
                                where e.FirstName == "Nathan" && e.LastName.StartsWith("Pa")
                                select e;
                employeeList = employees.ToList();
                foreach (var employee in employeeList)
                {
                    Console.WriteLine("Employee: {0} {1}, DOB: {2}",
                        employee.FirstName, employee.LastName, employee.BirthDate);
                }
            }
            Console.WriteLine();

            // Modify the list out of a context
            foreach (var employee in employeeList)
            {
                employee.BirthDate = employee.BirthDate.AddYears(10);
                Console.WriteLine("Employee: {0} {1}, DOB: {2}",
                    employee.FirstName, employee.LastName, employee.BirthDate);
            }

            // Re-attach and save the mofifications
            using (EmployeesDataContext db = new EmployeesDataContext())
            {
                db.Employees.AttachAll(employeeList, true);
                db.SubmitChanges();
            }


/Mats
nilssonm
 
Posts: 23
Joined: Mon 25 Aug 2008 15:08

Postby AndreyR » Fri 05 Dec 2008 11:17

The better way to attach and update entities is described in the following code example:
Code: Select all
      using (DataContext.DataContext db = new DataContext.DataContext()) {
        db.EMPs.AttachAll(employeeList, false);
        foreach (var employee in employeeList) {
          employee.HIREDATE = employee.HIREDATE.Value.AddYears(1);
          Console.WriteLine("Employee: {0} {1}, DOB: {2}",
              employee.ENAME, employee.EMPNO, employee.HIREDATE.Value);
        }

        db.SubmitChanges();
      }
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby nilssonm » Fri 05 Dec 2008 11:49

Yes that maybe a better way to do it, but as I wrote before, my code is a simulation of how it could work in a stateless ASP.NET application. The fetch could have been done in the first request, the modification in a second and the submitting to database in a third.

The only way to write down any entities of all from a detached state is to use the Attach methods for now. The Refresh method seems to be unimplemented. I am also wondering why the third overload of the Attach method is missing. The one that should take both the original and the modified entity as parameters.

/Mats
nilssonm
 
Posts: 23
Joined: Mon 25 Aug 2008 15:08

Postby AndreyR » Fri 05 Dec 2008 13:44

The method you have mentioned will be implemented. But the fix will not be available in the nearest build,
so I recommend you to use a workaround like the following one:
Code: Select all
     // Fetch and print the employees
      IList employeeList = new List();
      IList origEmployeeList = null;
      using (DataContext.DataContext db = new DataContext.DataContext()) {
        var employees = from emp in db.EMPs
                        where emp.ENAME == "SMITH"
                        select emp;
        origEmployeeList = employees.ToList();
        foreach (var employee in origEmployeeList) {
          Console.WriteLine("Employee: {0} {1}, DOB: {2}",
              employee.ENAME, employee.EMPNO, employee.HIREDATE.Value);
          employeeList.Add(new EMP
          {
            COMM = employee.COMM,
            DEPTNO = employee.DEPTNO,
            EMPNO = employee.EMPNO,
            ENAME = employee.ENAME,
            HIREDATE = employee.HIREDATE,
            JOB = employee.JOB,
            MGR = employee.MGR,
            SAL = employee.SAL
          });
        }
      }
      Console.WriteLine();

      // Modify the list out of a context
      foreach (var employee in employeeList) {
        employee.HIREDATE = employee.HIREDATE.Value.AddYears(100);
        Console.WriteLine("Employee: {0} {1}, DOB: {2}",
            employee.ENAME, employee.EMPNO, employee.HIREDATE.Value);
      }

      // Re-attach and save the mofifications
      using (DataContext.DataContext db = new DataContext.DataContext()) {
        db.EMPs.AttachAll(origEmployeeList, false);
        for (int i = 0; i < origEmployeeList.Count; i++)
          if (origEmployeeList[i] != employeeList[i]) {
            origEmployeeList[i].COMM = employeeList[i].COMM;
            origEmployeeList[i].DEPTNO = employeeList[i].DEPTNO;
            origEmployeeList[i].EMPNO = employeeList[i].EMPNO;
            origEmployeeList[i].ENAME = employeeList[i].ENAME;
            origEmployeeList[i].HIREDATE = employeeList[i].HIREDATE;
            origEmployeeList[i].JOB = employeeList[i].JOB;
            origEmployeeList[i].MGR = employeeList[i].MGR;
            origEmployeeList[i].SAL = employeeList[i].SAL;
          }

          db.SubmitChanges();
      }
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby nilssonm » Fri 05 Dec 2008 19:36

OK, thanks for your quick responses, but this seems to be a too long way of work around for me. I will look at and try using EF instead even though it has given me some headache before regarding the same problem.

/Mats
nilssonm
 
Posts: 23
Joined: Mon 25 Aug 2008 15:08


Return to LinqConnect (LINQ to SQL support)