Designer does not allow multiple associations from entity1 class to entity2 class

A process has multiple tasks that each can have mutliple connections to other tasks.

A connection entity thus has a source task and a destination task (eithter of which can also be null).

I can add the first association but designer does not allow the second to be added/shown.

Is this a designer bug or must I add foreign key relationships or siome such ?

(I dont like loading the tables with a relationship that is actually application specific).

Hello Don,

Perhaps I am not understanding the problem, but the designer certainly allows you to create multiple associations between the same pair of entity types.  I've attached an example screenshot representing my understanding of your model -- please correct me if I've got it wrong.

The designer *does* require the associations to be given distinctive names (at each end).  (This is because these names will be turned into class properties and duplicate names would not compile.)  The default names are got from the entity names and will therefore not be unique -- you will need to rename them.

If you are dragging tables to create the model, the designer will not *infer* the associations unless there is a foreign key present.  You can still add the associations manually (you will need to delete the inferred XxxId property in this case).

Yes Ivan,

I was using a one to one association when it should have been one to many.

But associations are still a bit of an issue becuase we have old poorly designed schemas .. where we store the parents code (a string) instead of the unique Id. Plus some tables don't have a Unique Id field , and some tables have no foreign keys defined despite having an index that is used like one.

The associations seem to require the parent Unique Id in the child and named as "parentId". This is sensible and readable but not achivevable for us in the immediate.

So how do we navigate via associations that use string keys for the link back to the parent. ? The deisgner keeps wanting to such delete associations unless a foreign key is defined in the child.

A redesign is not straight forward as there are customers with exisitng very large (100s of gigabyte) databases who can't afford the downtime to restucture tables.

In recent nightly builds we have added designer support for a custom column name, so that you can say, "Okay, I want the property to be called SourceTask, but the identifying column in the database is SOURCE_TASK_REF."  You can get the latest nightly from http://www.mindscape.co.nz/Products/LightSpeed/nightlybuilds.aspx.

Regarding the designer keeping wanting to delete the associations: yes, currently the designer uses the foreign key as a marker of the association within the database.  We already have a feature request in the backlog to relax the FK requirement, because some DB engines (e.g. SQLite) don't have first-class support for integrity constraints; when we implement this, we should be able to design it to also support databases where it is not practical (or users do not want) to have foreign keys.  In the meantime, unfortunately, you will have to keep deselecting the "delete association" in the Update From Source dialog, or you will have to maintain your model manually.  (How frequently is your schema changing?  It sounds like the database is well established.)

However, I think there may be a more fundamental issue if your reference column in the Connections table is pointing to a field in the Tasks table other than the primary key (ID column), or if there are tables with no column that is usable as an ID.  LightSpeed adopts a "convention over configuration" approach and I suspect this may be beyond the scope of our convention-driven mapping.  I'm going to ask one of my colleagues to have a look at this and comment on whether LightSpeed is a feasible solution given the database structure you describe and the impracticality of refactoring the database.

I coded this join to child records as a simple query from a customer to find the customer orders, quotes, addresses etc. But I then saw major performance issues in getting the order entity and quote entity objects back from the context.

Our order and quotes table have over a hundred columns and quite a few of our other tables are similar. Yes some poor normalisation but this is our inherited design and there is a lot of complexity in our data.

The Sql trace shows that the resords were reurned in 30-40 milliseconds but it takes 2-10 seconds for the data to get parsed into the entity objects. ? Not at all what I was expecting as usaully ORMs do a faster job.

I recoded this with SqlLink anonymous types to return only a limited number of fields and this was much faster. Seeming to highlight the time hit as occuring within the parsing to entity properties.

But the proper model would often need to use the model and fetch the proper entity objects so this performance is an issue.

The performance hit is the sort of thing I have seen in .NET when too many exceptions are thrown in formating and conversion routines (instead of testing the data for nulls etc before trying to convert). I turned on all exceptions in the debugger but it did not signal any to have occurred in the UnitOfWork query.

Do you have any ideas ?

I am happy to send through (offline) our schema, the model and the queries if that will help.

Hi Don,

Yep, that performance shouldn't be happening. If you could send through the repro I will take a look immediately.

Cheers,

Andrew.