Vista - SQLMetal - Problem generating VB from DBML

I have a test DBML file:-

<?xml version="1.0" encoding="utf-8"?>
<Database Name="myDB" EntityNamespace="MyEntNS" ContextNamespace="MyConNs" Class="DBdc" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
<Table Name="dbo.PARENTTABLE" Member="PARENTTABLE">
<Type Name="PARENTTABLE">
<Column Name="keycol1" Member="keycol1" Type="System.Decimal" DbType="Decimal(10,0) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="keycol2" Member="keycol2" Type="System.Decimal" DbType="Decimal(10,0) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="keycol3" Member="keycol3" Type="System.String" DbType="VarChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Association Name="fk_1064" Member="fkCHILDTABLE" ThisKey="keycol3,keycol1,keycol2" OtherKey="fkeycol3,fkeycol1,fkeycol2" Type="CHILDTABLE" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.CHILDTABLE" Member="CHILDTABLE">
<Type Name="CHILDTABLE">
<Column Name="main_id" Member="publish_id" Type="System.String" DbType="VarChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="fkeycol1" Member="fkeycol1" Type="System.Decimal" DbType="Decimal(10,0)" CanBeNull="true" />
<Column Name="fkeycol2" Member="fkeycol2" Type="System.Decimal" DbType="Decimal(10,0)" CanBeNull="true" />
<Column Name="fkeycol3" Member="fkeycol3" Type="System.String" DbType="VarChar(10)" CanBeNull="true" />
<Association Name="fk_1064" Member="pkPARENTTABLE" ThisKey="fkeycol3,fkeycol1,fkeycol2" OtherKey="keycol3,keycol1,keycol2" Type="PARENTTABLE" IsForeignKey="true" />
</Type>
</Table>
</Database>

Note that the order of the columns on the PARENTTABLE are listed correctly as keycol1, keycol2, keycol3, but the promary key on the table is actually keycol3, keycol1, keycol2. The association elements do use the correct primary key sequences. However, when I generate the VB for this DBML, it gets it wrong.. see the text below highlighted in red.

<Association(Name:="fk_1064", Storage:="_pkPARENTTABLE", ThisKey:="fkeycol3,fkeycol1,fkeycol2", IsForeignKey:=true)> _
Public Property pkPARENTTABLE() As PARENTTABLE
Get
Return Me._pkPARENTTABLE.Entity
End Get
Set
Dim previousValue As PARENTTABLE = Me._pkPARENTTABLE.Entity
If ((Object.Equals(previousValue, value) = false) _
OrElse (Me._pkPARENTTABLE.HasLoadedOrAssignedValue = false)) Then
Me.SendPropertyChanging
If ((previousValue Is Nothing) _
= false) Then
Me._pkPARENTTABLE.Entity = Nothing
previousValue.fkCHILDTABLE.Remove(Me)
End If
Me._pkPARENTTABLE.Entity = value
If ((value Is Nothing) _
= false) Then
value.fkCHILDTABLE.Add(Me)
Me._fkeycol3 = value.keycol1
Me._fkeycol1 = value.keycol2
Me._fkeycol2 = value.keycol3
Else
Me._fkeycol3 = CType(Nothing, String)
Me._fkeycol1 = CType(Nothing, Nullable(Of Decimal))
Me._fkeycol2 = CType(Nothing, Nullable(Of Decimal))
End If
Me.SendPropertyChanged("pkPARENTTABLE")
End If
End Set
End Property

Is there something wrong with the DBML or is it a bug in SQLMetal ?

(this is using the RTM (1.00.21022) version of SQLMetal)
--
Adrian

Hi Adrian,

As for the DBML file you provided, how did you generate it?

For general validation, you can use the DBML schemas to validate the DBML
to see whether there is any XML schema specific errors. Here is the MSDN
reference about this:

#How to: Validate DBML and External Mapping Files (LINQ to SQL)
http://msdn2.microsoft.com/en-us/library/bb882675.aspx

if there doesn't exist such error. I think there may have some certain
database related things cause potential problems.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@xxxxxx.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------Class="DBdc" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">DbType="Decimal(10,0) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />DbType="Decimal(10,0) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />DbType="VarChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />ThisKey="keycol3,keycol1,keycol2" OtherKey="fkeycol3,fkeycol1,fkeycol2"
Type="CHILDTABLE" DeleteRule="NO ACTION" />DbType="VarChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />DbType="Decimal(10,0)" CanBeNull="true" />DbType="Decimal(10,0)" CanBeNull="true" />DbType="VarChar(10)" CanBeNull="true" />ThisKey="fkeycol3,fkeycol1,fkeycol2" OtherKey="keycol3,keycol1,keycol2"
Type="PARENTTABLE" IsForeignKey="true" />as keycol1, keycol2, keycol3, but the promary key on the table is actually
keycol3, keycol1, keycol2. The association elements do use the correct
primary key sequences. However, when I generate the VB for this DBML, it
gets it wrong.. see the text below highlighted in red.ThisKey:="fkeycol3,fkeycol1,fkeycol2", IsForeignKey:=true)> _

Steven,

The dbml file is one we 'adjust' from the normal one generated by SQLMetal..
we have to 'fix' some of the Member entries as they're badly named by
sqlmetal.

Followd your link and tried to validate, but when I go to the properties
window in VS, it doesn't give me a 'Schema' property to select.. it just
gives..

Access: Public
Base Class: System.Data.Linq.DataContext
+Connection
Context Namespace: MyConNS
Entity Namespace: MyEntNs
Inheritance Modifier: (None)
Name: DBdc
Serialization Mode: None


Anyway, thinking about it, it does generate the VB, it just gets some
columns switched around. As I said, it seems like it's not using the order
of the ThisKey and OtherKey when creating the code, but is taking the order
of the columns in the <column> elements.




""Steven Cheng"" <stcheng@xxxxxx> wrote in message
news:SBISRf$hIHA.4672@xxxxxx
| Hi Adrian,
|
| As for the DBML file you provided, how did you generate it?
|
| For general validation, you can use the DBML schemas to validate the DBML
| to see whether there is any XML schema specific errors. Here is the MSDN
| reference about this:
|
| #How to: Validate DBML and External Mapping Files (LINQ to SQL)
| http://msdn2.microsoft.com/en-us/library/bb882675.aspx
|
| if there doesn't exist such error. I think there may have some certain
| database related things cause potential problems.
|
| Sincerely,
|
| Steven Cheng
|
| Microsoft MSDN Online Support Lead
|
|
| Delighting our customers is our #1 priority. We welcome your comments and
| suggestions about how we can improve the support we provide to you. Please
| feel free to let my manager know what you think of the level of service
| provided. You can send feedback directly to my manager at:
| msdnmg@xxxxxx.
|
| ==================================================
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
| --------------------
| >Reply-To: "Adrian" <apparker@xxxxxx>
| >From: "Adrian" <apparker@xxxxxx>
| >Subject: SQLM
|
| >I have a test DBML file:-
| ><?xml version="1.0" encoding="utf-8"?>
| ><Database Name="myDB" EntityNamespace="MyEntNS"
ContextNamespace="MyConNs"
| Class="DBdc" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
| > <Table Name="dbo.PARENTTABLE" Member="PARENTTABLE">
| > <Type Name="PARENTTABLE">
| > <Column Name="keycol1" Member="keycol1" Type="System.Decimal"
| DbType="Decimal(10,0) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
| > <Column Name="keycol2" Member="keycol2" Type="System.Decimal"
| DbType="Decimal(10,0) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
| > <Column Name="keycol3" Member="keycol3" Type="System.String"
| DbType="VarChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
| > <Association Name="fk_1064" Member="fkCHILDTABLE"
| ThisKey="keycol3,keycol1,keycol2" OtherKey="fkeycol3,fkeycol1,fkeycol2"
| Type="CHILDTABLE" DeleteRule="NO ACTION" />
| > </Type>
| > </Table>
| > <Table Name="dbo.CHILDTABLE" Member="CHILDTABLE">
| > <Type Name="CHILDTABLE">
| > <Column Name="main_id" Member="publish_id" Type="System.String"
| DbType="VarChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
| > <Column Name="fkeycol1" Member="fkeycol1" Type="System.Decimal"
| DbType="Decimal(10,0)" CanBeNull="true" />
| > <Column Name="fkeycol2" Member="fkeycol2" Type="System.Decimal"
| DbType="Decimal(10,0)" CanBeNull="true" />
| > <Column Name="fkeycol3" Member="fkeycol3" Type="System.String"
| DbType="VarChar(10)" CanBeNull="true" />
| > <Association Name="fk_1064" Member="pkPARENTTABLE"
| ThisKey="fkeycol3,fkeycol1,fkeycol2" OtherKey="keycol3,keycol1,keycol2"
| Type="PARENTTABLE" IsForeignKey="true" />
| > </Type>
| > </Table>
| ></Database>
| >Note that the order of the columns on the PARENTTABLE are listed
correctly
| as keycol1, keycol2, keycol3, but the promary key on the table is actually
| keycol3, keycol1, keycol2. The association elements do use the correct
| primary key sequences. However, when I generate the VB for this DBML, it
| gets it wrong.. see the text below highlighted in red.
| > <Association(Name:="fk_1064", Storage:="_pkPARENTTABLE",
| ThisKey:="fkeycol3,fkeycol1,fkeycol2", IsForeignKey:=true)> _
| > Public Property pkPARENTTABLE() As PARENTTABLE
| > Get
| > Return Me._pkPARENTTABLE.Entity
| > End Get
| > Set
| > Dim previousValue As PARENTTABLE = Me._pkPARENTTABLE.Entity
| > If ((Object.Equals(previousValue, value) = false) _
| > OrElse (Me._pkPARENTTABLE.HasLoadedOrAssignedValue = false)) Then
| > Me.SendPropertyChanging
| > If ((previousValue Is Nothing) _
| > = false) Then
| > Me._pkPARENTTABLE.Entity = Nothing
| > previousValue.fkCHILDTABLE.Remove(Me)
| > End If
| > Me._pkPARENTTABLE.Entity = value
| > If ((value Is Nothing) _
| > = false) Then
| > value.fkCHILDTABLE.Add(Me)
| > Me._fkeycol3 = value.keycol1
| > Me._fkeycol1 = value.keycol2
| > Me._fkeycol2 = value.keycol3
| > Else
| > Me._fkeycol3 = CType(Nothing, String)
| > Me._fkeycol1 = CType(Nothing, Nullable(Of Decimal))
| > Me._fkeycol2 = CType(Nothing, Nullable(Of Decimal))
| > End If
| > Me.SendPropertyChanged("pkPARENTTABLE")
| > End If
| > End Set
| > End Property
| >Is there something wrong with the DBML or is it a bug in SQLMetal ?
| >(this is using the RTM (1.00.21022) version of SQLMetal)
| >--
| >Adrian
| >
|

Hi Adrian,

For the DBML document you mentioned, I've also tested it. Yes, if we keep
the three primary columns and their order always as 1, 2, 3 in all places,
it will generate code as expected. However, if we change the order as
below(in your customized DBML):

ThisKey="keycol3,keycol1,keycol2" OtherKey="fkeycol3,fkeycol1,fkeycol2"

the code it generate does seem different between the columns order in
DBML. I'll do some further research to see whether there is anything which
can control this.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@xxxxxx.

This posting is provided "AS IS" with no warranties, and confers no rights.



--------------------<SBISRf$hIHA.4672@xxxxxx>SQLMetal..orderorderPleaseactuallyit

Hi Adrian,

I've performed some further research on this issue and consult some other
experts on this. Some engineers from dev team told me that this is an by
designed behavior of the LINQ DBML generation. currently, it will require
you to keep the column orders(in your "ThisKey" and "OtherKey" ) the same
as the column sequence in the table schema. Changing the order will make
the generated code become mismatch on the column/fields's assignment.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@xxxxxx.

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------places,DBMLandThen

Hi Steven,

I've now written a routine to correct the column sequence in the xml to
match the actual primary key. The code generates fine now.

"Steven Cheng [MSFT]" <stcheng@xxxxxx> wrote in message
newsQdzKgwjIHA.5204@xxxxxx
| Hi Adrian,
|
| I've performed some further research on this issue and consult some other
| experts on this. Some engineers from dev team told me that this is an by
| designed behavior of the LINQ DBML generation. currently, it will require
| you to keep the column orders(in your "ThisKey" and "OtherKey" ) the same
| as the column sequence in the table schema. Changing the order will make
| the generated code become mismatch on the column/fields's assignment.
|
| Sincerely,
|
| Steven Cheng
|
| Microsoft MSDN Online Support Lead
|
|
| Delighting our customers is our #1 priority. We welcome your comments and
| suggestions about how we can improve the support we provide to you. Please
| feel free to let my manager know what you think of the level of service
| provided. You can send feedback directly to my manager at:
| msdnmg@xxxxxx.
|
| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
|
| --------------------
| >From: stcheng@xxxxxx ("Steven Cheng")
| >Organization: Microsoft
| >Date: Mon, 24 Mar 2008 09:34:34 GMT
| >Subject: Re: SQLMetal - Problem generating VB from DBML
|
| >
| >Hi Adrian,
| >
| >For the DBML document you mentioned, I've also tested it. Yes, if we keep
| >the three primary columns and their order always as 1, 2, 3 in all
| places,
| >it will generate code as expected. However, if we change the order as
| >below(in your customized DBML):
| >
| >ThisKey="keycol3,keycol1,keycol2" OtherKey="fkeycol3,fkeycol1,fkeycol2"
| >
| >the code it generate does seem different between the columns order in
| >DBML. I'll do some further research to see whether there is anything
which
| >can control this.
| >
| >Sincerely,
| >
| >Steven Cheng
| >
| >Microsoft MSDN Online Support Lead
| >
| >
| >Delighting our customers is our #1 priority. We welcome your comments and
| >suggestions about how we can improve the support we provide to you.
Please
| >feel free to let my manager know what you think of the level of service
| >provided. You can send feedback directly to my manager at:
| >msdnmg@xxxxxx
| >
| >This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| >
| >--------------------
| >>Reply-To: "Adrian" <apparker@xxxxxx>
| >>From: "Adrian" <apparker@xxxxxx>
| >>References: <uPcn4ddhIHA.748@xxxxxx>
| ><SBISRf$hIHA.4672@xxxxxx>
| >>Subject: Re: SQLMetal - Problem generating VB from DBML
| >>Date: Mon, 17 Mar 2008 10:50:34 -0000
| >>Steven,
| >>
| >>The dbml file is one we 'adjust' from the normal one generated by
| >SQLMetal..
| >>we have to 'fix' some of the Member entries as they're badly named by
| >>sqlmetal.
| >>
| >>Followd your link and tried to validate, but when I go to the properties
| >>window in VS, it doesn't give me a 'Schema' property to select.. it just
| >>gives..
| >>
| >>Access: Public
| >>Base Class: System.Data.Linq.DataContext
| >>+Connection
| >>Context Namespace: MyConNS
| >>Entity Namespace: MyEntNs
| >>Inheritance Modifier: (None)
| >>Name: DBdc
| >>Serialization Mode: None
| >>
| >>
| >>Anyway, thinking about it, it does generate the VB, it just gets some
| >>columns switched around. As I said, it seems like it's not using the
| >order
| >>of the ThisKey and OtherKey when creating the code, but is taking the
| >order
| >>of the columns in the <column> elements.
| >>
| >>
| >>
| >>
| >>""Steven Cheng"" <stcheng@xxxxxx> wrote in message
| >>news:SBISRf$hIHA.4672@xxxxxx
| >>| Hi Adrian,
| >>|
| >>| As for the DBML file you provided, how did you generate it?
| >>|
| >>| For general validation, you can use the DBML schemas to validate the
| DBML
| >>| to see whether there is any XML schema specific errors. Here is the
MSDN
| >>| reference about this:
| >>|
| >>| #How to: Validate DBML and External Mapping Files (LINQ to SQL)
| >>| http://msdn2.microsoft.com/en-us/library/bb882675.aspx
| >>|
| >>| if there doesn't exist such error. I think there may have some certain
| >>| database related things cause potential problems.
| >>|
| >>| Sincerely,
| >>|
| >>| Steven Cheng
| >>|
| >>| Microsoft MSDN Online Support Lead
| >>|
| >>|
| >>| Delighting our customers is our #1 priority. We welcome your comments
| and
| >>| suggestions about how we can improve the support we provide to you.
| >Please
| >>| feel free to let my manager know what you think of the level of
service
| >>| provided. You can send feedback directly to my manager at:
| >>| msdnmg@xxxxxx.
| >>|
| >>| ==================================================
| >>| This posting is provided "AS IS" with no warranties, and confers no
| >>rights.
| >>|
| >>| --------------------
| >>| >Reply-To: "Adrian" <apparker@xxxxxx>
| >>| >From: "Adrian" <apparker@xxxxxx>
| >>| >Subject: SQLM
| >>|
| >>| >I have a test DBML file:-
| >>| ><?xml version="1.0" encoding="utf-8"?>
| >>| ><Database Name="myDB" EntityNamespace="MyEntNS"
| >>ContextNamespace="MyConNs"
| >>| Class="DBdc" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
| >>| > <Table Name="dbo.PARENTTABLE" Member="PARENTTABLE">
| >>| > <Type Name="PARENTTABLE">
| >>| > <Column Name="keycol1" Member="keycol1" Type="System.Decimal"
| >>| DbType="Decimal(10,0) NOT NULL" IsPrimaryKey="true" CanBeNull="false"
/>
| >>| > <Column Name="keycol2" Member="keycol2" Type="System.Decimal"
| >>| DbType="Decimal(10,0) NOT NULL" IsPrimaryKey="true" CanBeNull="false"
/>
| >>| > <Column Name="keycol3" Member="keycol3" Type="System.String"
| >>| DbType="VarChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
| >>| > <Association Name="fk_1064" Member="fkCHILDTABLE"
| >>| ThisKey="keycol3,keycol1,keycol2"
OtherKey="fkeycol3,fkeycol1,fkeycol2"
| >>| Type="CHILDTABLE" DeleteRule="NO ACTION" />
| >>| > </Type>
| >>| > </Table>
| >>| > <Table Name="dbo.CHILDTABLE" Member="CHILDTABLE">
| >>| > <Type Name="CHILDTABLE">
| >>| > <Column Name="main_id" Member="publish_id" Type="System.String"
| >>| DbType="VarChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
| >>| > <Column Name="fkeycol1" Member="fkeycol1" Type="System.Decimal"
| >>| DbType="Decimal(10,0)" CanBeNull="true" />
| >>| > <Column Name="fkeycol2" Member="fkeycol2" Type="System.Decimal"
| >>| DbType="Decimal(10,0)" CanBeNull="true" />
| >>| > <Column Name="fkeycol3" Member="fkeycol3" Type="System.String"
| >>| DbType="VarChar(10)" CanBeNull="true" />
| >>| > <Association Name="fk_1064" Member="pkPARENTTABLE"
| >>| ThisKey="fkeycol3,fkeycol1,fkeycol2"
OtherKey="keycol3,keycol1,keycol2"
| >>| Type="PARENTTABLE" IsForeignKey="true" />
| >>| > </Type>
| >>| > </Table>
| >>| ></Database>
| >>| >Note that the order of the columns on the PARENTTABLE are listed
| >>correctly
| >>| as keycol1, keycol2, keycol3, but the promary key on the table is
| >actually
| >>| keycol3, keycol1, keycol2. The association elements do use the
correct
| >>| primary key sequences. However, when I generate the VB for this
DBML,
| >it
| >>| gets it wrong.. see the text below highlighted in red.
| >>| > <Association(Name:="fk_1064", Storage:="_pkPARENTTABLE",
| >>| ThisKey:="fkeycol3,fkeycol1,fkeycol2", IsForeignKey:=true)> _
| >>| > Public Property pkPARENTTABLE() As PARENTTABLE
| >>| > Get
| >>| > Return Me._pkPARENTTABLE.Entity
| >>| > End Get
| >>| > Set
| >>| > Dim previousValue As PARENTTABLE = Me._pkPARENTTABLE.Entity
| >>| > If ((Object.Equals(previousValue, value) = false) _
| >>| > OrElse (Me._pkPARENTTABLE.HasLoadedOrAssignedValue = false))
| Then
| >>| > Me.SendPropertyChanging
| >>| > If ((previousValue Is Nothing) _
| >>| > = false) Then
| >>| > Me._pkPARENTTABLE.Entity = Nothing
| >>| > previousValue.fkCHILDTABLE.Remove(Me)
| >>| > End If
| >>| > Me._pkPARENTTABLE.Entity = value
| >>| > If ((value Is Nothing) _
| >>| > = false) Then
| >>| > value.fkCHILDTABLE.Add(Me)
| >>| > Me._fkeycol3 = value.keycol1
| >>| > Me._fkeycol1 = value.keycol2
| >>| > Me._fkeycol2 = value.keycol3
| >>| > Else
| >>| > Me._fkeycol3 = CType(Nothing, String)
| >>| > Me._fkeycol1 = CType(Nothing, Nullable(Of Decimal))
| >>| > Me._fkeycol2 = CType(Nothing, Nullable(Of Decimal))
| >>| > End If
| >>| > Me.SendPropertyChanged("pkPARENTTABLE")
| >>| > End If
| >>| > End Set
| >>| > End Property
| >>| >Is there something wrong with the DBML or is it a bug in SQLMetal ?
| >>| >(this is using the RTM (1.00.21022) version of SQLMetal)
| >>| >--
| >>| >Adrian
| >>| >
| >>|
| >>
| >>
| >>
| >
| >
|

Hi Adrian,

Thanks for your reply and inform us of the result.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

--------------------Pleasekeepandpropertiesjustcertainxmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">CanBeNull="false"CanBeNull="false"/>Type="System.String"/>Type="System.Decimal"Type="System.Decimal"