Model Designer One 2 Many FK association creates incorrect data type in generated many class

 Hi,
  I have been building some LS tests against database used by a current application as I am trying to prove a case for LS against old style hard coded SQL statements pointing at both MySQL 5 and MSSQL 2008 databases.

When I drag 2 MySQL tables from the server explorer that have a FK set up against an INT(3) field in both, the generated data type in the child table is string and not int which causes an issue when trying to perform a LINQ query with a join on this association.

This happens for both the MySQL and MSSQL database.

I manually changed the datatype for the generated asscoiation field to int and all is ok but obviously this isn't ideal.

Thanks,

Jim

Hi Jim,

I've tried to reproduce this but without success.  In our tests an INT(3) comes out as an integer and the association fields are all generated correctly.  So I think I may be misunderstanding your scenario.

Is it possible for you to provide us with the schemas (CREATE TABLE / ALTER TABLE statements) for each of the tables involved please, so we can get this fixed for you?  Thanks!

 Ok I reckon I know what's going on but before I go into that just let me say that this is against a MySQL schema that is slightly "interesting", (so please don't shoot the messenger :-) ), which I have reproduced and attached. 

When you see the schema, it, (for reasons I haven't got down to the bottom of yet), has some mad PK's set up which aren't on the main ID column.  The PK for the Parent table is instead set against the Name column which is a varchar(50) column instead of the ParentID int autoinc column.  My thoughts are that the LS designer uses the datatype of the PK column of the Parent table for the association and not the actual datatype of the FK Column.  Is that correct?

Please before I get loads of "Hey man your schema sucks it should be ...", can I say I really do know that and it is a schema I have inherited and have to work with.  :-)

Thanks.

Yes, your diagnosis that LightSpeed is using the data type of the primary key column is absolutely correct.  However, it should also be mapping the primary key column to its ID property, so I would expect your entities to be showing up with an Identity Type of String, and Identity Column Names of Name and SomeOtherMadId respectively.  This is now problematic because in LightSpeed a foreign key is always to the Id, and therefore we will try to look up child.parentId using the parent.Name column, which will obviously fail.  So the problem is more fundamental than LightSpeed using the data type of the PK -- it's that the foreign key doesn't related to the primary key!

You have a couple of options here.

The first option is to move the primary keys to the autoinc columns, and redefine the existing primary keys as unique indexes.  This is the ideal solution from a technical point of view but I'm guessing it is impractical from a project point of view!

The second option is to manage the association manually: that is, instead of using a 'real' LightSpeed association which always maps to the Id (i.e. the SomeOtherMadId column), write methods to get the parent entity and child collections using a query on the relevant columns.  This isn't too hard if you are in a read-only scenario but it could be laborious if you are creating or modifying associations.

The third option is to tell LightSpeed to map its Id to the ParentId and SomeOddId columns instead of the actual primary keys.  You will need to override what LightSpeed has inferred from the database, and manually set the Identity Type and Identity Column Name (and probably the backreference column name of the association), but once you've done this, everything should fall out pretty naturally.  At run time, LightSpeed doesn't care that the Id columns aren't primary keys: it only cares that they're unique.  The LightSpeed designer does care, though, so you won't be able to easily use the database scheme synchronisation functionality (it will keep trying to put things back to the 'database' way, and you will have to keep remembering to exclude those).  This may not be an issue if you do not expect to extend the database schema.

If you take the third option you may even find it easier to write the classes by hand rather than using the LightSpeed designer.  This will make it a bit easier to consciously think about the mapping from the .NET classes to the database schema, rather than having LightSpeed guess the mapping from the "interesting" schema and you then having to figure out what LightSpeed has guessed and fix it up where it doesn't fit!  It is a little more laborious though.

My recommendation would be the third option (assuming #1 is off the cards).  This gives you a clean class model and works nicely with LightSpeed, at the expense of requiring more attention to the mappings and of making schema sync messy.  Let us know if you need any more guidance on this or if you think one of the other two options would suit you better.

 Thanks for this and yes I would prefer to do #1 but this is a decision I can't make as I am not the product owner and this was only 2 of 50+ tables all with their own "design characteristics" to overcome.  What I will do is let the designer generate the c# in a scratchpad project and copy the generated classes over to the core project.  I can then verify and alter the copies where required.

I have a number of new projects starting from scratch which means that for them I will be always working from the model(s) and generating the DB Schema from them and as we know the schema's will then be best practice :-)

Thanks again for great support.