[dblinq] Oracle + IsDbGenerated -> How to set correct Sequence name?

[mabu]

Hi,

i already got the code where the sequence is queried for the current
inserted value, but how do i set the correct
sequence name to get the correct value?
at the moment the sequence name is empty - so the sql breaks ... Can
you give me a hint please?


Thanks, Martin

--
You received this message because you are subscribed to the Google Groups "DbLinq" group.
To post to this group, send email to dbl...@googlegroups.com.
To unsubscribe from this group, send email to dblinq+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/dblinq?hl=en.

[mabu]

Ok - i think i got it:
I Added to the DBML definition of the Column:
Expression="<SEQUENCE_NAME>.CURRVAL"

The generated Identifier is the unique key and used to associate
children - do i have to update the children manually before the got
inserted into the database?

[Jonathan Pryor]

OK, I *thought* I understood the original question...

On Tue, 2010-04-20 at 07:44 -0700, mabu wrote:
> i already got the code where the sequence is queried for the current
> inserted value, but how do i set the correct
> sequence name to get the correct value?
> at the moment the sequence name is empty - so the sql breaks ... Can
> you give me a hint please?

mostly because I saw "Oracle" in the subject and thought "I have no idea
about Oracle..."

But now I'm really confused. I thought you were referring to getting
the primary keys from inserted rows, but you're referring to "sequence
names." What's a sequence name? Does this have anything to do with
e.g. Firebird's CREATE SEQUENCE statement?

On Tue, 2010-04-20 at 22:25 -0700, mabu wrote:
> I Added to the DBML definition of the Column:
> Expression="<SEQUENCE_NAME>.CURRVAL"

This just seems terribly wrong. You're editing the .dbml file to add
a //Column/@Expression attribute that references the sequence name?

I have no idea what this would do. (I'm not even sure
what //Column/@Expression is supposed to do...)

> The generated Identifier is the unique key and used to associate
> children - do i have to update the children manually before the got
> inserted into the database?

Some code might be needed so I can fully understand your scenario, but
if you have something like:

Parent p = new Parent () {...};
db.Parents.InsertOnSubmit(p);
Child c1 = new Child() { Parent = p };
Child c2 = new Child() { Parent = p };
db.Children.InsertOnSubmit(c1);
db.Children.InsertOnSubmit(c2);
db.SubmitChanges();

Once the Parent `p` is inserted within `db.SubmitChanges()`, it'll have
a primary key. The children don't need to be updated, as they're
already referring to the `p` entity, and thus will have the appropriate
foreign key id inserted.

(Note, though, that `db.Parents.InsertOnSubmit()` MUST occur before the
`db.Children.InsertOnSubmit()`, so that the parent exists before
attempting to insert the children, as db.SubmitChanges() does the
inserts/removes in the same order.)

Now, this may have ~nothing to do with your question, as (again) I'm not
entirely sure what your question IS.

Thanks,
- Jon

[mabu]

On 21 Apr., 16:12, Jonathan Pryor <jonpr...@vt.edu> wrote:
> OK, I *thought* I understood the original question...
>
> On Tue, 2010-04-20 at 07:44 -0700, mabu wrote:
> > i already got the code where the sequence is queried for the current
> > inserted value, but how do i set the correct
> > sequence name to get the correct value?
> > at the moment the sequence name is empty - so the sql breaks ... Can
> > you give me a hint please?
>
> mostly because I saw "Oracle" in the subject and thought "I have no idea
> about Oracle..."
>
> But now I'm really confused.  I thought you were referring to getting
> the primary keys from inserted rows, but you're referring to "sequence
> names."  What's a sequence name?  Does this have anything to do with
> e.g. Firebird's CREATE SEQUENCE statement?

Well the Sequence is an Entity that counts from x to n (normally 1
to .... ) and you can use this entity
to get an unique identifier in your tables - at sql server you create
an auto incremented int field for this purpose.

> On Tue, 2010-04-20 at 22:25 -0700, mabu wrote:
> > I Added to the DBML definition of the Column:
> > Expression="<SEQUENCE_NAME>.CURRVAL"

Just to note:
That statement is actually not correct - the correct has to be
<SEQUENCE_NAME>.NextVal ... You can have a look into
DbLinq.Oracle.OracleSqlProvider::GetInsertIds()

> This just seems terribly wrong.  You're editing the .dbml file to add
> a //Column/@Expression attribute that references the sequence name?

Yes .. So the correct ID is used at the Insert statement and the
correct ID is read from database after the insert and to update the
entity

> I have no idea what this would do.  (I'm not even sure
> what //Column/@Expression is supposed to do...)

It was just a try & error how i got the fieldname ... i do not know
exactly how i got it

> > The generated Identifier is the unique key and used to associate
> > children - do i have to update the children manually before the got
> > inserted into the database?

yes and no .. i observe a funny behaviour at the moment. i have the
following structure:
Report [1] -> [n] Product [1] -> [n] Component [1] -> [n] Interface

Each Entity has a column ID that references at the specific child
entity.
The ID of report is automatically used at the insert of Product, but
all other IDs are not automatically used at their children to update
them. So the data breaks ...

Each ID-Column in the DBML is specified like:
<Column Name="ID" Type="System.Int32" IsDbGenerated="true"
IsPrimaryKey="true" AutoSync="OnInsert" CanBeNull="false"
UpdateCheck="Always" Expression="S_INTERFACE.NextVal" />

> Some code might be needed so I can fully understand your scenario, but
> if you have something like:
>
>         Parent p = new Parent () {...};
>         db.Parents.InsertOnSubmit(p);
>         Child c1 = new Child() { Parent = p };
>         Child c2 = new Child() { Parent = p };
>         db.Children.InsertOnSubmit(c1);
>         db.Children.InsertOnSubmit(c2);
>         db.SubmitChanges();
>
> Once the Parent `p` is inserted within `db.SubmitChanges()`, it'll have
> a primary key.  The children don't need to be updated, as they're
> already referring to the `p` entity, and thus will have the appropriate
> foreign key id inserted.

Ok got it, but i obeserve following behaviour: i created 1 report with
150 products, 500 components and 900 interfaces.
they are created like in your example ... but when i call
Report.Products.Count the number 1 ... -> what?
I am still debugging but i am really confused at the moment...

> (Note, though, that `db.Parents.InsertOnSubmit()` MUST occur before the
> `db.Children.InsertOnSubmit()`, so that the parent exists before
> attempting to insert the children, as db.SubmitChanges() does the
> inserts/removes in the same order.)

yeah i already got this at debugging sessions - i am using an template
event to add the entities to the datacontext
and i had to synchronize these events ...


At the moment i do not know you to describe my error or how to debug
it ... i am really confused now


Martin

[mabu]

Some Code may help:

Data.IMDataContext context = monitor.Context;
Data.Report report = new Data.Report() { SuiteID = 1,
Rundate = DateTime.Now };
context.Reports.InsertOnSubmit(report);
Data.Product p = new Data.Product() { Report = report,
Name = "1" };
context.Products.InsertOnSubmit(p);
Data.Component c = new Data.Component() { Product = p,
Name = "1-1" };
context.Components.InsertOnSubmit(c);
Data.Component c2 = new Data.Component() { Product =
p, Name = "1-2" };
context.Components.InsertOnSubmit(c2);
context.SubmitChanges();
Console.WriteLine("report.ID=" + report.ID);
Console.WriteLine("p.ID=" + p.ID);
Console.WriteLine("c.ID=" + c.ID);
Console.WriteLine("c2.ID=" + c2.ID);


In the database i following:
* p is inserted twice with correct reportid
* c is inserted three times with the first product-id
* c2 is inserted once but without a product id

When i add an SubmitChanges() after each insert the database
representation is ok, but at the amount of data i have to insert
i would prever to generate the datacontext and push the result
afterwards into the database ...



The examples generates following output:
INSERT INTO Report ( Rundate , SuiteID , ID ) VALUES
(:Rundate, :SuiteID, S_REPORT.NextVal)
-- :Rundate: Input DateTime (Size = 0; Prec = 0; Scale = 0)
[22.04.2010 12:44:06]
-- :SuiteID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

SELECT S_REPORT.CurrVal INTO :ID FROM DUAL
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

INSERT INTO Product ( Name , ReportID , ID , Keywords ,
ModelID , QCID ) VALUES (:Name, :ReportID,
S_PRODUCT.NextVal, :Keywords, :ModelID, :QCID)
-- :Name: Input AnsiString (Size = 1; Prec = 0; Scale = 0) [1]
-- :ReportID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [77]
-- :Keywords: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- :ModelID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- :QCID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

SELECT S_PRODUCT.CurrVal INTO :ID FROM DUAL
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

INSERT INTO Component ( Name , ID , ProductID , ModelID , QCID )
VALUES (:Name, S_COMPONENT.NextVal, :ProductID, :ModelID, :QCID)
-- :Name: Input AnsiString (Size = 3; Prec = 0; Scale = 0) [1-1]
-- :ProductID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4054]
-- :ModelID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- :QCID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

SELECT S_COMPONENT.CurrVal INTO :ID FROM DUAL
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

INSERT INTO Product ( Name , ReportID , ID , Keywords ,
ModelID , QCID ) VALUES
(:Name, :ReportID, :ID, :Keywords, :ModelID, :QCID)
-- :Name: Input AnsiString (Size = 1; Prec = 0; Scale = 0) [1]
-- :ReportID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [77]
-- :ID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4054]
-- :Keywords: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- :ModelID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- :QCID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

INSERT INTO Component ( Name , ID , ProductID , ModelID , QCID )
VALUES (:Name, :ID, :ProductID, :ModelID, :QCID)
-- :Name: Input AnsiString (Size = 3; Prec = 0; Scale = 0) [1-1]
-- :ID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [25157]
-- :ProductID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4054]
-- :ModelID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- :QCID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

INSERT INTO Component ( Name , ID , ProductID , ModelID , QCID )
VALUES (:Name, :ID, :ProductID, :ModelID, :QCID)
-- :Name: Input AnsiString (Size = 3; Prec = 0; Scale = 0) [1-1]
-- :ID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [25157]
-- :ProductID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4054]
-- :ModelID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- :QCID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

INSERT INTO Component ( Name , ID , ProductID , ModelID , QCID )
VALUES (:Name, S_COMPONENT.NextVal, :ProductID, :ModelID, :QCID)
-- :Name: Input AnsiString (Size = 3; Prec = 0; Scale = 0) [1-2]
-- :ProductID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0]
-- :ModelID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- :QCID: Input AnsiString (Size = 0; Prec = 0; Scale = 0) []
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

SELECT S_COMPONENT.CurrVal INTO :ID FROM DUAL
-- Context: Oracle Model: AttributedMetaModel Build: 0.20.0.0

report.ID=77
p.ID=4054
c.ID=25157
c2.ID=25161

[Anders]

On Apr 22, 11:21 am, mabu <mabuni...@gmail.com> wrote:
> On 21 Apr., 16:12, Jonathan Pryor <jonpr...@vt.edu> wrote:
> > I thought you were referring to getting
> > the primary keys from inserted rows, but you're referring to "sequence
> > names." What's a sequence name? Does this have anything to do with
> > e.g. Firebird's CREATE SEQUENCE statement?

Yep, they seem similar if not identical.

> > I have no idea what this would do. (I'm not even sure
> > what //Column/@Expression is supposed to do...)
>
> It was just a try & error how i got the fieldname ... i do not know
> exactly how i got it

Looking a the documentation, this seems this field is meant for
storing the expression used for creating a calculated column:
http://msdn.microsoft.com/en-us/library/system.data.linq.mapping.columnattribute.expression%28v=VS.100%29.aspx
It seems that dblinq bends this definition a bit, and uses the field
to store the sequnece increment expression (e.g.
"employees_seq.nextval"). Since sequences are not declaratively
associated with the column, they are used to populate, this expression
is retrived (rather crudely) by searching for the reg exp "SELECT\s+(\S
+.*)\s+INTO\s+\:new.(\S+)\s+FROM\s+DUAL" in any before-insert trigger,
assuming to find something like "SELECT Employees_seq.NEXTVAL
INTO :new."EmployeeID" FROM DUAL" (from
OracleSchemaLoader.Constraints.cs). To see an example of the
formatting of the trigger see (the script in the tests folder seem be
missing the ":new." to match the pattern):
http://dblinq2007.googlecode.com/svn/trunk/examples/DbLinq.Ora.Example/sql/create_Northwind_ora.sql

I guess the job of quering the sequence and using the value for the
insert, could be handle three places:

1) In the application: Neither dblinq nor the db would need to do
anything, so this should work, but isn't very nice to use.

2) In dblinq: To me it seems like to "pretties" way, but then dblinq
would need no know what sequnece to use. You might think, that the
value in the "Expression" field could be used. But this expression is
what the db useses to popoulate the column and if the db is population
the column, dblinq shouldn't (and vice versa). So dblinq can't (?).

3) In the db: The db can populate the autonumbered fields via a before-
insert trigger. This might require you to add the (specially
formatted) triggers to the db, but dblinq only need to when that the
value is set by the database and retrive it afterwards. So why would
dblinq attemt to parse the triggers to retrive the sequence name if it
doesn't need it? Well, it also need to find out what columns are db-
generated and the regexp also does this. But it also uses the sequence
name to retrive the inserted value (by replacing the ".nextval" with
".currval" in OracleSqlProvider.cs), although it seems this could also
be accomplished by quering the column or by using the "returning"
construct as to ellliminate a polible race condition (it was new in
oracle 10g, which is perhaps why it wasn't used in dblinq previously).

So I guess dblinq only supports 1 and 3.

> > > The generated Identifier is the unique key and used to associate
> > > children - do i have to update the children manually before the got
> > > inserted into the database?

You shouldn't need to update the foreign keys in the child table, but
dblinq needs to be able to find the primary key in the parent table
(either because you assigned it, as in (1) above, or because it knows
the name of the sequence to query for the currval).

So who quries the sequences for the nextval in your system?

> yes and no .. i observe a funny behaviour at the moment. i have the
> following structure:
> Report [1] -> [n] Product [1] -> [n] Component [1] -> [n] Interface
>
> Each Entity has a column ID that references at the specific child
> entity.
> The ID of report is automatically used at the insert of Product, but
> all other IDs are not automatically used at their children to update
> them. So the data breaks ...

So it seems to be because the children either didn't get an id or
dblinq couldn't retrieve the id of the parent.

> Each ID-Column in the DBML is specified like:
> <Column Name="ID" Type="System.Int32" IsDbGenerated="true"
> IsPrimaryKey="true" AutoSync="OnInsert" CanBeNull="false"
> UpdateCheck="Always" Expression="S_INTERFACE.NextVal" />

Okay, so the expression seems to be corrent if you are using a trigger
to set column value.

> Ok got it, but i obeserve following behaviour: i created 1 report with
> 150 products, 500 components and 900 interfaces.
> they are created like in your example ... but when i call
> Report.Products.Count the number 1 ... -> what?

So you did get the products inserted? Was this products that you
inserted manually?
If you have a linq-query that returns a wrong result, I guess that is
a different matter. Could you give the complete linq-query and perhaps
the query being sent to the database?

--
Anders

[mabu]

On 22 Apr., 12:56, mabu <mabuni...@gmail.com> wrote:
> Some Code may help:
>
>                 Data.IMDataContext context = monitor.Context;
>                 Data.Report report = new Data.Report() { SuiteID = 1,
> Rundate = DateTime.Now };
>                 context.Reports.InsertOnSubmit(report);
>                 Data.Product p = new Data.Product() { Report = report,
> Name = "1" };
>                 context.Products.InsertOnSubmit(p);
>                 Data.Component c = new Data.Component() { Product = p,
> Name = "1-1" };
>                 context.Components.InsertOnSubmit(c);
>                 Data.Component c2 = new Data.Component() { Product =
> p, Name = "1-2" };
>                 context.Components.InsertOnSubmit(c2);
>                 context.SubmitChanges();
>                 Console.WriteLine("report.ID=" + report.ID);
>                 Console.WriteLine("p.ID=" + p.ID);
>                 Console.WriteLine("c.ID=" + c.ID);
>                 Console.WriteLine("c2.ID=" + c2.ID);

DataContext.CurrentTransactionEntities contains 4 entity tracks in
the first step when GetReferencedObjects(entityTrack.Entity) is
called.

In the first loop run are already inserted 3 items ( objects report,
p, c ) based on reports object ... they are inserted in the correct
order and the ID in the objects is updated.

At the next loop run ( object p ) trigges itself again and c again,
net loop run triggers c again and fourth loop run triggers c2

So it seems that the lookup of the entityTracking does not work as I
expected ... because the primary keys are set after inserting into
database

[Anders]

On Apr 22, 12:56 pm, mabu <mabuni...@gmail.com> wrote:
> Some Code may help:

> [...]

> The examples generates following output:
> INSERT INTO  Report  ( Rundate ,  SuiteID ,  ID ) VALUES
> (:Rundate, :SuiteID, S_REPORT.NextVal)

So you proved me wrong. dblinq does in fact use the expression to
populate the field. Thank you. So you can use it without a trigger, if
you are willing to write (or edit) the dbml yourself.

I can't see what is the real problem, so I will try to reproduce.
--
Anders

[mabu]

On 22 Apr., 14:06, Anders <anders...@gmail.com> wrote:

> > > > The generated Identifier is the unique key and used to associate
> > > > children - do i have to update the children manually before the got
> > > > inserted into the database?
>
> You shouldn't need to update the foreign keys in the child table, but
> dblinq needs to be able to find the primary key in the parent table
> (either because you assigned it, as in (1) above, or because it knows
> the name of the sequence to query for the currval).

The primary key is found and set, but only - at my upper example - in
one of the 2 children because
there is another product object in the entitytrack ...

> So who quries the sequences for the nextval in your system?
>
> > yes  and no .. i observe a funny behaviour at the moment. i have the
> > following structure:
> > Report [1] -> [n] Product [1] -> [n] Component [1] -> [n] Interface
>
> > Each Entity has a column ID that references at the specific child
> > entity.
> > The ID of report is automatically used at the insert of Product, but
> > all other IDs are not automatically used at their children to update
> > them. So the data breaks ...
>
> So it seems to be because the children either didn't get an id or
> dblinq couldn't retrieve the id of the parent.

well both happens .. the id values are set in database and the
objects,
but the tracking seems to make a mistake ( or my data structure has an
error ... )

> > Each ID-Column in the DBML is specified like:
> > <Column Name="ID" Type="System.Int32" IsDbGenerated="true"
> > IsPrimaryKey="true" AutoSync="OnInsert" CanBeNull="false"
> > UpdateCheck="Always"  Expression="S_INTERFACE.NextVal" />
>
> Okay, so the expression seems to be corrent if you are using a trigger
> to set column value.

yes - before insert trigger ...

> > Ok got it, but i obeserve following behaviour: i created 1 report with
> > 150 products, 500 components and 900 interfaces.
> > they are created like in your example ... but when i call
> > Report.Products.Count the number 1 ... -> what?
>
> So you did get the products inserted? Was this products that you
> inserted manually?
> If you have a linq-query that returns a wrong result, I guess that is
> a different matter. Could you give the complete linq-query and perhaps
> the query being sent to the database?

i did not insert something manually ... which queries do you want to
get?
i can mail you the dbml or cs code if it may help ...

[mabu]

when i have look at the objects of the CurrentTransactionEntities at
this time the
inserted items are correct updated ( ID and Parent-IDs ),
but second Component ( object c2 ) has not set the correct parent
id...

altough the Parent objects points to the product which got inserted
and has a correct ID set.

Seems object tracking has some bug anywhere?

Martin

[mabu]

So i made a quick-hack: i update the EntityState if a referenced
object is inserted ...
now the correct number of records appear in the database and all of
them are correct inserted ( Id, parent id, ... )

But there has to be a better solution for this?

[Anders]

On Apr 22, 12:56 pm, mabu <mabuni...@gmail.com> wrote:
> Some Code may help:

>[...]

> In the database i following:
> * p is inserted twice with correct reportid
> * c is inserted three times with the first product-id
> * c2 is inserted once but without a product id

Just noticed: How did p get inserted twice? Don't you have a primary
(or unique) key on the column? And if you don't, do you have foreign
keys?

I created a simplified test, that uses the northwind schema.
using (var context = new ReadTest().CreateDB()) {
var c = new nwind.Category { CategoryName = "cat1" };
context.Categories.InsertOnSubmit(c);
var p = new nwind.Product { Category = c, ProductName = "prod1" };
context.Products.InsertOnSubmit(p);
context.SubmitChanges();
}

This fails with a primary key violation on both Oracle and SQLite. In
the below lines starting with " Call ", ".. " and "-- Context: " have
been removed and non primary-/foreign-key or name columns have been
removed for readability. Some things look odd to me:
- The selects from OrderDetails and Categories. Putting a breakpoint
in WriteLog(IDbCommand) shows that they come from the loop over
members in UpdateReferencedObjects(object). Is it really necessary to
go to the database (on insert)?
- The same selects uses literal values rather than parameters in the
where-clause.
- The second insert into Products. When
SubmitChangesImpl(ConflictMode) is called CurrentTransactionEntities
holds two items. The first holds the Category and the second the
Product. But while running InsertEntity(object, QueryContext) for the
category, UpdateReferencedObjects(entity) will insert the product. So
when the second EntityTrack is processed, the Product is inserted
again.

---- Oracle ----
INSERT INTO "NORTHWIND"."Categories" ("CategoryID",
"CategoryName", ...) VALUES
(Categories_seq.NEXTVAL, :CategoryName, ...)
-- :CategoryName: Input AnsiString (Size = 4; Prec = 0; Scale = 0)
[cat1]

SELECT Categories_seq.CurrVal INTO :CategoryID FROM DUAL

INSERT INTO "NORTHWIND"."Products" ("CategoryID", ..., "ProductID",
"ProductName", ...) VALUES (:CategoryID, ...,
Products_seq.NEXTVAL, :ProductName, ...)
-- :CategoryID: Input Decimal (Size = 0; Prec = 0; Scale = 0) [7]
-- :ProductName: Input AnsiString (Size = 5; Prec = 0; Scale = 0)
[prod1]

SELECT Products_seq.CurrVal INTO :ProductID FROM DUAL

SELECT "Discount", "OrderID", "ProductID", "Quantity", "UnitPrice"
FROM "NORTHWIND"."OrderDetails" WHERE ("ProductID" = 10)

SELECT "CategoryID", "CategoryName", "Description", "Picture" FROM
"NORTHWIND"."Categories" WHERE ("CategoryID" = 7)

INSERT INTO "NORTHWIND"."Products" ("CategoryID", ..., "ProductID",
"ProductName", ...) VALUES
(:CategoryID, ..., :ProductID, :ProductName, ...)
-- :CategoryID: Input Decimal (Size = 0; Prec = 0; Scale = 0) [7]
-- :ProductID: Input Decimal (Size = 0; Prec = 0; Scale = 0) [10]
-- :ProductName: Input AnsiString (Size = 5; Prec = 0; Scale = 0)
[prod1]

---- SQLite ----
INSERT INTO "main"."Categories" ("CategoryName", "Description",
"Picture") VALUES (:CategoryName, ...)
-- :CategoryName: Input String (Size = 0; Prec = 0; Scale = 0) [cat1]

SELECT last_insert_rowid()

INSERT INTO "main"."Products" ("CategoryID", ..., "ProductName", ...)
VALUES (:CategoryID, ..., :ProductName, ...)
-- :CategoryID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [9]
-- :ProductName: Input String (Size = 0; Prec = 0; Scale = 0) [prod1]

SELECT last_insert_rowid()

SELECT "Discount", "OrderID", "ProductID", "Quantity", "UnitPrice"
FROM "main"."Order Details" WHERE ("ProductID" = 78)

SELECT "CategoryID", "CategoryName", "Description", "Picture" FROM
"main"."Categories" WHERE ("CategoryID" = 9)

INSERT INTO "main"."Products" ("CategoryID", ..., "ProductID",
"ProductName", ...) VALUES
(:CategoryID, ..., :ProductID, :ProductName, ...)
-- :CategoryID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [9]
-- :ProductID: Input Int32 (Size = 0; Prec = 0; Scale = 0) [78]
-- :ProductName: Input String (Size = 0; Prec = 0; Scale = 0) [prod1]

--
Anders

[Jonathan Pryor]

Looks like a DbLinq bug. Can you please file it with the test case?

Even better, can you fix it? :-)

- Jon

[mabu]

On 22 Apr., 17:52, Anders <anders...@gmail.com> wrote:
> On Apr 22, 12:56 pm, mabu <mabuni...@gmail.com> wrote:
>
> > Some Code may help:
> >[...]
> > In the database i following:
> > * p is inserted twice with correct reportid
> > * c is inserted three times with the first product-id
> > * c2 is inserted once but without a product id
>
> Just noticed: How did p get inserted twice? Don't you have a primary
> (or unique) key on the column? And if you don't, do you have foreign
> keys?

i set primary keys in the database and also all foreign keys - and
also in the dbml.

Now i started dbmetal to create the dbml at the database definition
and afterwards the code file.

in my example i get an error now because of a foreign key violation ->
the second component is inserted without a productid ( 0 )
the second component is still not tracked as reference of the
product ... seems to be another bug?


Martin

[mabu]

On 22 Apr., 19:06, Jonathan Pryor <jonpr...@vt.edu> wrote:
> Looks like a DbLinq bug.  Can you please file it with the test case?
>
> Even better, can you fix it? :-)

I already suggested fix of this at my last post yesterday ;)
May be GetReferencedObjects() should also return EntityTrack objects
to avoid the update procedure
and have cleaner code?

Martin

[mabu]

On 23 Apr., 08:38, mabu <mabuni...@gmail.com> wrote:
>
> I already suggested fix of this at my last post yesterday ;)
> May be GetReferencedObjects() should also return EntityTrack objects
> to avoid the update procedure
> and have cleaner code?

sh*t - that's may be not the real problem of the failure - the
entityset does not add the second child
EntitySet.cs # 168 -> validates to true because Component.Equals()
uses the ID of the objects to compare them.

Because the ID is generated by the DB at SubmitChanges the items are
equal!

[mabu]

On 23 Apr., 09:28, mabu <mabuni...@gmail.com> wrote:

> sh*t - that's may be not the real problem of the failure - the
> entityset does not add the second child
> EntitySet.cs # 168 -> validates to true because Component.Equals()
> uses the ID of the objects to compare them.
>
> Because the ID is generated by the DB at SubmitChanges the items are
> equal!

and that was because i generated the code using "--generate-equals-
hash" option ...

man - replying yourself is annouying ;)

[Anders]

On Apr 22, 7:06 pm, Jonathan Pryor <jonpr...@vt.edu> wrote:
> Looks like a DbLinq bug.  Can you please file it with the test case?

Definitely and definitely.
http://code.google.com/p/dblinq2007/issues/detail?id=245
http://code.google.com/p/dblinq2007/issues/detail?id=246

> Even better, can you fix it? :-)

De... well, that would need some looking into. Something like the
below, while solving the problem, feels like a hack (shouldn't the
existing tracking flags and data-structures be sufficient and what if
the same entity appears twice in the entityTracks list).

Index: DataContext.cs
===================================================================
--- DataContext.cs (revision 1404)
+++ DataContext.cs (working copy)
@@ -439,20 +439,23 @@
break;
}
}
- foreach (var entityTrack in
CurrentTransactionEntities.EnumerateAll()
+ var entityTracks =
CurrentTransactionEntities.EnumerateAll()
.Concat(AllTrackedEntities.EnumerateAll())
- .ToList())
+ .ToList();
+ var entities = new HashSet<object>(entityTracks.Select(et
=> et.Entity).ToList());
+ foreach (var entityTrack in entityTracks)
{
+ entities.Remove(entityTrack.Entity);
switch (entityTrack.EntityState)
{
case EntityState.ToInsert:
- foreach (var toInsert in
GetReferencedObjects(entityTrack.Entity))
+ foreach (var toInsert in
GetReferencedObjects(entityTrack.Entity, entities))
{
InsertEntity(toInsert, queryContext);
}
break;
case EntityState.ToWatch:
- foreach (var toUpdate in
GetReferencedObjects(entityTrack.Entity))
+ foreach (var toUpdate in
GetReferencedObjects(entityTrack.Entity, entities))
{
UpdateEntity(toUpdate, queryContext);
}
@@ -463,15 +466,15 @@
}
}

- private IEnumerable<object> GetReferencedObjects(object
value)
+ private IEnumerable<object> GetReferencedObjects(object
value, HashSet<object> exclude)
{
var values = new EntitySet<object>();
- FillReferencedObjects(value, values);
+ FillReferencedObjects(value, values, exclude);
return values;
}

// Breadth-first traversal of an object graph
- private void FillReferencedObjects(object parent,
EntitySet<object> values)
+ private void FillReferencedObjects(object parent,
EntitySet<object> values, HashSet<object> exclude)
{
if (parent == null)
return;
@@ -480,6 +483,7 @@
while (children.Count > 0)
{
object value = children.Dequeue();
+ if (exclude.Contains(value)) continue;
values.Add(value);
IEnumerable<MetaAssociation> associationList =
Mapping.GetMetaType(value.GetType()).Associations.Where(a => !
a.IsForeignKey);
if (associationList.Any())