On Apr 22, 11:21 am, mabu <[email protected]> wrote: > On 21 Apr., 16:12, Jonathan Pryor <[email protected]> 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 -- You received this message because you are subscribed to the Google Groups "DbLinq" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/dblinq?hl=en.
