sqlmetal produces error with mysql database

[El]

Hello,

I have a MySQL database with a few tables which have indexes and
identifying as well as non-identifying relationships established (i.e.
primary and foreign keys, constraints). I also have about 20 tables
in this database.

I am trying to create the objects using sqlmetal on Ubuntu Linux. The
command I use is as follows:

sqlmetal /provider:MySQL /server:localhost /database:mydbname /
user:myuser /passwo
rd:mypassword /pluralize /debug /namespace=tpems.Model /
code=TpemsDbModel.cs

I get an number of errors when I run the command (see below for the
debug output). I am able to generate using very simple (1 or 2
tables). What am I doing wrong? Anyone please help! Thank you.

DbLinq Database mapping generator 2008 version 0.20
for Microsoft (R) .NET Framework version 3.5
Distributed under the MIT licence (http://linq.to/db/license)

>>> Reading schema from MySQL database
sqlmetal: System.InvalidOperationException: Operation is not valid due
to the current state of the object
at System.Linq.Enumerable.Single[Column] (IEnumerable`1 source,
System.Func`2 predicate, Fallback fallback) [0x00000] in <filename
unknown>:0
at System.Linq.Enumerable.Single[Column] (IEnumerable`1 source,
System.Func`2 predicate) [0x00000] in <filename unknown>:0
at DbMetal.Generator.Implementation.Processor.ValidateAssociations
(DbLinq.Schema.Dbml.Database database, DbLinq.Schema.Dbml.Table table)
[0x00000] in <filename unknown>:0
at DbMetal.Generator.Implementation.Processor.SchemaIsValid
(DbLinq.Schema.Dbml.Database database) [0x00000] in <filename unknown>:
0
at DbMetal.Generator.Implementation.Processor.ProcessSchema
(DbMetal.Parameters parameters) [0x00000] in <filename unknown>:0

[Jonathan Pryor]

On Dec 10, 2010, at 5:40 PM, El wrote:
> I get an number of errors when I run the command (see below for the
> debug output). I am able to generate using very simple (1 or 2
> tables). What am I doing wrong? Anyone please help! Thank you

sqlmetal doesn't like your tables. The relevant code is:

var otherType = database.Tables.Single(t => t.Type.Name == association.Type).Type;
var otherAssociation = otherType.Associations.Single(a => a.Type == table.Type.Name && a.ThisKey == association.OtherKey);
var otherColumn = otherType.Columns.Single(c => c.Member == association.OtherKey);

if (association.CardinalitySpecified && association.Cardinality == Cardinality.Many && association.IsForeignKey)
{
error = true;
Log.WriteErrorLine("Error DBML1059: The IsForeignKey attribute of the Association element '{0}' of the Type element '{1}' cannnot be '{2}' when the Cardinality attribute is '{3}'.",
association.Name, table.Type.Name, association.IsForeignKey, association.Cardinality);
}

So one of those .Single() calls is failing because either 0 items are present or more than one item is present, thus preventing sqlmetal from further validating your schema (and possibly generating other errors).

To narrow down which line is failing, run your command with MONO_OPTIONS set:

$ MONO_OPTIONS=--debug sqlmetal ...

That should provide line numbers in the exception message.

The intent of the above block of code is to ensure that the table cross-references are "sane" -- that for an association:

1. There is only one matching Type for the association.
2. The matched type (1) has an association to the "current" association, i.e. it's bidirectional
3. The referenced column in the matching type (1) actually exists.

In a well normalized table schema, these should all be valid, but it's fairly easy to break this...

- Jon

[Jo]

I had the same problem. Im my case the violated condition was 3.
The reason for that was that I had a .dbml File created in Visual Studio. In this file the column attributes "Storage" and "Member" were missing - obviously they are not required in VS.
I added these attributes in my .dbml with the same values as "Name" - worked.

Regards,
Jo.

[Jo]

Now I ran into another problem. I am using keys consisting of two fields.

      <Association Name="DBActivity_DBActivityProperty" Member="DBActivity" ThisKey="ActivityUserID,ActivityMobileID" OtherKey="UserID,MobileId" Type="DBActivity" IsForeignKey="true" />

Obviously the check for the 3rd condition will not work for "UserID,MobileId".
Are keys consisting of more than 1 column not supported?