Using LLBLGen 2.6

I've got a database that looks like this:

WorkOrder - PK (int) - Other fields

FileStore - PK (int) - ParentID (varchar(25)) - Tablename (varchar)

What links the filestore to a work order is the combination of tablename and parentID. This is a production setup so I cannot change the database field types. The queries "work" that LLBLGen produces (as in if I copy paste the generated query from SQL Profiler it returns the data I want) but due to the type mismatch between ParentID and PK even though the query returns records LLBLGen isn't able to add them to the parent Entity.

What do I need to do in LLBLGen to make this work?

Just to be clear, please answer these questions:

• Do you have a relation in DB for these two tables?
• How the relation looks like? or How it should be?
• As you said the code works but no entities are added to the parent I assume you create the relation at code or something like that but you can't use PrefetchPaths. Please show us your code snippet to understand this better.

I actually manually created the relation in the LLBL UI. It was more by accident then anything. I started to create the relationship and it wouldn't let me (types didn't match) so I switched the type in the database, refreshed, and created the relationship.

Testing revealed that while this was "technically" correct it wouldn't actually work against the full database so I switched back and refreshed. To my surprise the relationship stayed and even more surprising actually creates SQL that correctly grabs the entities that I desire.

The only problem is somewhere in the core of LLBL it isn't relinking the retrieved entities back to the parent entity due to the string/int comparison issue.

While technically a hack, I'm just looking to layer another hack on top to make the whole thing function smoothly. I need to pass a sizable object graph to a client and multiple methods to retrieve the object graph (through WCF) is not the preferred method.

The relation as it currently is is WorkOrder.PK (int) <- Filestore.ParentID (string). The way I would like LLBL to imagine it as (aka internally convert to) WorkOrder.PK (int) <- Filestore.ParentID (int) The data is stored in that way in the database, it just happens to be stupidly placed in a varchar(25) field instead of an int.

No special relationship code created, and the hackyness above actually makes a prefetch path that creates working SQL, its just the translation from SQL into an Entity that isn't quite 100%.

Maybe what I'm asking is too low level or complex.

How about: how do I make the Entity use an int field when the database backing field is a varchar? Obviously there is the potential of mapping errors but that is being managed by putting ints into the varchar field.

Would that solve my relationship issues? If so, how do I implement that?

The ideal solution is fix this from your DB. If you can't do that, in the meantime you can write your own TypeConverter that fix the data type and the data (for instance remove the trailing spaces and convert it to int). Then you create a custom relation in the Designer, now the fields are of the same type.

Yeah, I wish I could do the ideal solution. Sadly I am not the only project that uses the database and most importantly my project isn't even sort of close to being able to define the database, I just have to deal with whatever crap others come up with. Which is why LLBL is so awesome, it manages a huge percentage of the crap that I used to have to hand code.

Thanks for the article, I figure that will fix my problems (until the next one at least).