Update relations

[lc4pro]

Hi,

I'm trying to update a relation between two database tables.

My first approach was

Code: Select all

current.Variety.Clear()
For Each item In Varieties
     current.Variety.Add(New Variety With {.VarietyId = item.VarietyId, .Percent = item.Percent})
Next

db.SubmitChanges()

On SubmitChanges I'll get foreign key errors. The next step was a SubmitChanges after Clear(). Then I'll get "Row not found or changed."

Is there a easy way to manage Relations without a long living context and not generating a new db context after every commit?

thanks!

[StanislavK]

Could you please describe the scenario you are trying to implement in more details? For example, please specify the definitions of the database objects used and the differences between the initial 'Variety' set and the one you are trying to create (e.g., whether the entities in these sets have the same or different primary keys).

Also, I will send you a small test project (please check that it is not blocked by your mail filter). Please specify what should be changed in this sample or, if possible, send us your test project, so that we are able to reproduce and analyze the issue.

[lc4pro]

Hi there,

sorry for the late reply but the week was full of work

so here it comes.

I changed your original setup a little bit to fit mine

Code: Select all

CREATE TABLE `dept` (
  `DEPTNO` int(4) NOT NULL,
  `DNAME` varchar(14) DEFAULT NULL,
  `LOC` varchar(13) DEFAULT NULL,
  PRIMARY KEY (`DEPTNO`)
)

CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` int(7) DEFAULT NULL,
  `COMM` int(7) DEFAULT NULL,
  `DEPTNO` int(4) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY `key` (`DEPTNO`)
)

CREATE TABLE `new_table` (
  `dept_id` int(4) NOT NULL,
  `emp_id` int(4) NOT NULL,
  `percent` int(11) DEFAULT NULL,
  PRIMARY KEY (`dept_id`,`emp_id`),
  KEY `dept` (`dept_id`),
  KEY `emp` (`emp_id`),
  CONSTRAINT `dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`DEPTNO`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `emp` FOREIGN KEY (`emp_id`) REFERENCES `emp` (`EMPNO`) ON DELETE NO ACTION ON UPDATE NO ACTION
) 

Code: Select all

  Sub TestUpdateRelation()

        Dim updateNewTable() As NewTable = New NewTable() {
            New NewTable With {.EmpId = 1001, .Percent = 10},
            New NewTable With {.EmpId = 1002, .Percent = 55}
            }

        Dim dc As New ScottDataContext() With {.Log = Console.Out}

        Dim dept = dc.Depts _
            .Where(Function(d) d.Deptno = 30) _
            .Single()

        ' ''dept.NewTables.Clear()
        ' ''dept.NewTables.Assign(updateNewTable)

        ''dept.NewTables.Clear()
        ''For Each item In updateNewTable
        ''    dept.NewTables.Add(New NewTable With {.EmpId = item.EmpId, .Percent = item.Percent})
        ''Next

        'dept.NewTables.Clear()
        'dc.SubmitChanges() ' Row not found or changed.
        'For Each item In updateNewTable
        '    dept.NewTables.Add(New NewTable With {.EmpId = item.EmpId, .Percent = item.Percent})
        'Next

        dc.NewTables.DeleteAllOnSubmit(dept.NewTables)
        For Each item In updateNewTable
            dept.NewTables.Add(New NewTable With {.EmpId = item.EmpId, .Percent = item.Percent})
        Next

        dc.SubmitChanges()
    End Sub

    Sub Main()

        'TestClearRelation()
        TestUpdateRelation()

    End Sub

As you can see on the count of the comments at the dept.NewTables* that was the way i got it to work.

The first time everything will work like it should but if you fire it up again then there will be dupplicate key errors or rows not found or changed.

btw. I like this approach the most

Code: Select all

dept.NewTables.Clear()
dept.NewTables.Assign(updateNewTable)

Another thing i noticed. You have the nice

Code: Select all

 {.Log = Console.Out}

in your context initialisation. But there is no single Line of SQL-Code in the output! That's anoying if you are looking for errors

I'm useing VB.net on Visual Studio 2010 SP1

Hope that helps!

Thanks in advance and for the great support Team!!

[MariiaI]

We couldn't reproduce the issues with 'duplicate key' or 'rows not found or changed' errors using the tables and the code that you had described.
Please send us the changed sample project and specify the data, that should be stored in those tables.

As for the logging, could you please specify what exactly goes wrong - whether no SQL is displayed in the Console window or you are not satisfied with the output format?
You can also try using the dbMonitor tool for logging SQL commands outside your application.
Please refer to:
http://www.devart.com/linqconnect/docs/dbMonitor.html
http://www.devart.com/dbmonitor

[lc4pro]

Hi

A packet with the testproject and the testdata is on the way to you.

As for the log output; I'd expected the output in the Visual Studio output window. But I got the output in the console window. So it was my missunderstanding

greetings

[MariiaI]

Thank you for the sample project.
We have reproduced the issue with 'duplicate key' and 'rows not found or changed' errors when working with these functions:

Code: Select all

dept.NewTables.Clear()
dept.NewTables.Assign(updateNewTable)

We will investigate it and notify you about the results as soon as possible.

Please specify whether you are getting the same errors when using

Code: Select all

dc.NewTables.DeleteAllOnSubmit(dept.NewTables)
For Each item In updateNewTable
    dept.NewTables.Add(New NewTable With {.EmpId = item.EmpId, .Percent = item.Percent})
dc.SubmitChanges()

because with such a code, we couldn't reproduce the issues with LinqConnect 4.

[lc4pro]

dc.NewTables.DeleteAllOnSubmit(dept.NewTables)
For Each item In updateNewTable
    dept.NewTables.Add(New NewTable With {.EmpId = item.EmpId, .Percent = item.Percent})
dc.SubmitChanges()

Yes this is the only working solution but in my opinion it's a little bit unconvenient

[MariiaI]

Thank you for the reply. We will notify you as soon as the issue with the Clear() and Assign() functions is fixed.

[MariiaI]

We have fixed the bug related to problems when working with Clear() and Assign() functions. The corresponding build of LinqConnect will be available for download next week.

In the situation you described we recommend you to set the "Delete on null" option to 'true' for the associations (select the necessary association->Properties->Delete On Null). In this case, all dependent records will be marked for deletion after calling the Clear method.

Otherwise, the foreign keys of dependent records will be set to 0, thus causing the exception (if no parent row with a zero primary key is available in the database).
Also, since in the sample foreign keys form the primary key, the exception will be thrown, as the primary key should not be changed.

[MariiaI]

New build of LinqConnect 4.0.63 is available for download now!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=24669

[lc4pro]

Back again!

If I do this

Code: Select all

current.Varieties.Clear()
current.Varieties.Add(db.Variety.Single(Function(x) x.id = requestId))
db.SubmitChanges()

and the selected variety has already been in the varieties table I'll get a dupplicat Key-Exception.

Devart.Data.MySql.MySqlException: Duplicate entry '17-2' for key 'PRIMARY'

[MariiaI]

Could you please specify the following:
- whether your foreign keys form the primary key?
- whether the "Delete on null" option is set to 'false' or to 'true' for your associations?
- the definitions of the used database objects;

Also, if possible, please send us the sample project with which this error could be reproduced.