Auto Through Entities - schema bug?

I either am not understanding/using this feature correctly, or I may have found a small bug in the handling of Auto Through Entities.  I am using the nightly build from 20090829, and SQL 2005 as the backend database.

 I have two entities, for example let's say an "Order" entity, and "Product" entity.  Both have the Schema property set to, "MyStore".  I am using a through association to represent the many-to-many relationship between orders and products.  On the through association, I use the "Auto Through Entity" property to define the map between the two entities, called "__MapOrderProduct", with collection names of "Orders" and "Products".

When I gen the database from this model, I get what I would expect (Pluralized names = true):

MyStore.Orders

MyStore.Products

MyStore.__MapOrderProducts

I then load an Order from the database the normal LS way, from a UoW.FindOne call.  But I get a SQL Exception error loading the "Orders.Products" collection, complaining that database object "__MapOrderProducts" does not exist.  So I turn on the TraceLogger, and I see that the SQL used to select out the Order references the right table with "FROM MyStore.Orders".  However, the SQL generated to load the Products within that order via the through association DOES NOT USE the schema name where the Auto Through Entity table exists - it says simply "FROM __MapOrderProducts", which rightly fails I believe since that table does not exist in the default database schema, but instead exists in the schema where the other entities are defined - "MyStore".  I see no property to set a schema name on the through association.

Is this a bug, or am I missing some other property/option to get the proper SQL generated?

Thanks,

Chris

I definitely think I am onto something here.  I now notice in my Designer model, that after having added these "Auto Through Entity" objects to my model, when I go to update my database, it thinks that those Map tables do NOT exist (I think it is checking WITHOUT specifying the schema name).  I hit OK, but then I get an error complaining that those objects already exist in the database, because it tries to create those tables using the correct schema name.  A check of the SQL log confirms that it is trying to create those same items over again.

I think some part of the code is not checking those through auto-through-entity properties correctly.

Thanks,

Chris

Auto Through Entities are, by design, very simple-minded and offer very few configuration options (in order to avoid having to clutter the Through Association arrow with all possible entity configuration options).  If you need to configure settings on the through entity which aren't surfaced on the association arrow, such as the schema, you need to define an explicit through entity.  You can quickly do this by right clicking the through association arrow and choosing Convert to Explicit Through Entity.  Then you can set the schema name on the explicit through entity.

You are right that there appears to be an inconsistency here where the Update Database command is creating the table into the MyStore schema while the code generator is not emitting the schema attribute onto the generated entity.  And clearly the behaviour where it keeps trying (and failing) to recreate the auto through entities is a bug -- and I agree with your diagnosis that it is probably checking in the default schema rather than the schema where it generated the entity.  We will investigate this but please be warned that the result may be that Update Database generates auto through entities into the default schema, in order to stick with the philosophy of "avoid overloading the association arrow;" that is, you may still end up needing to create an explicit through entity in order to set the schema.

By the way, if all your tables are in the MyStore schema, you might want to set the LightSpeedContext.Schema so as to make this the default schema -- this will avoid issues if the schema is not specified on the entity in the designer.

Thanks for reporting these issues!

Ivan - on point and prompt, as always.  Thank you.

Setting the schema property on the context object worked just fine, and the Auto Through Entities work as expected now.  This will satisfy my use case. 

I am curious as to what you will eventually decide to do with the schema handling of the object (keeping it in the default schema and fixing the bug, or allowing a user to specify a schema explicitly for that object).  Perhaps a property on the model itself could expose a schema attribute, that all object will follow unless overridden by an entity-level schema property.  Defaulting to creating objects in the user's default schema may not always desirable.

Thanks again,

Chris