Multi-table entity one to many

[Paul]

Okay, I'm extremely new to OpenAccess so I'm fairly sure I'm doing something stupid here - or unsupported - but I can't figure it out, and I can't find anyone else talking about it.

I think I've stripped the somewhat complicated reality down to a basic explanation that fails in the same way.

I have two tables in a legacy database that I can't modify in any way - not the schema, nor the data. I am attempting to create an application that queries this database in a more meaningful way. I'm going to use Customer and Address as the examples.

For each Customer, you may have multiple Address records - but only one will be current, which will be indicated via a "0" in a Version field. The older records are kept in the table but given alternate version numbers when a new record is added (and takes over version 0).

In an ideal world, I'd filter those non Version 0 records out before they even got to my objects, resulting in a nice, simple 1:1 relationship. Unfortunately, I can't see any way to do this.

So in my testing, I've been trying to retrieve all the data then I planned to filter them before I display it. Here's the mapping I'm using for the Customer class that exemplifies the problem:

configuration.HasProperty(x => x.Cust_ID).IsIdentity(KeyGenerator.Autoinc).HasFieldName("_cust_ID").WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("Cust_ID").ToColumn("Cust_ID", "Address");
configuration.HasProperty(x => x.Last_Name).HasFieldName("_last_Name").WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("Last_Name").HasColumnType("varchar").HasLength(40);
configuration.HasProperty(x => x.First_Name).HasFieldName("_first_Name").WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("First_Name").HasColumnType("varchar").HasLength(40);

configuration.HasProperty(x => x.Addr_ID).HasFieldName("_addr_ID").WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("Addr_ID", "Address");
configuration.HasProperty(x => x.Version).HasFieldName("_version").WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("Version", "Address");
configuration.HasProperty(x => x.Zip).HasFieldName("_zip").WithDataAccessKind(DataAccessKind.ReadOnly).ToColumn("Zip", "Address");

(Note the inclusion of the version and addr_id fields are purely for testing/debugging, and the Address table does contain a Cust_ID field that maps to Customer)

When I retrieve data and ask my grid control to display the results, the SQL code generated (discovered via a SQL profile trace) is exactly as I'd expect, and returns the exact results I'd expect - information in the Customer table is repeated once for each record in the Address table, and the three Address fields contain correct information for each Address record.

However, my grid displays differently - it does show me the correct number of rows per Customer as the raw SQL data, but all the rows display identical data the whole way across - and whichever Address row happens to be chosen for each Customer seems to be chosen at random. 

I just have a nasty feeling that I'm trying to do something that the multi-table mapping isn't equipped to handle. However, I really want these results in a single class - I want to create non-mapped properties that combine multiple properties and allow the user to search on them, for example...and if I can crack this problem, it'll make everything else so much easier.

Can anyone shed any light?

Thanks in advance,

Paul

[Boris Georgiev]

Hello Paul,

Unfortunately you cannot achieve this behavior, because the multi-table entities are intended to be used in one-to-one relationship. In your case you have one-to-many relationship and it is not possible to use multi-table entities.

Bearing in mind that you have a legacy database, there are no many options to achieve this behavior and in the following cases you should use Addresses as navigation property:
1) You can create a wrapper class for the customer entities, where you will have two reference properties - Customer and Address, load them in the constructor. Also you will have another properties for CustomerName, AddressZip and etc. which will take their values from the references.
2) You can extend the Customer class with wrapper properties only for the required address properties, take the required Address entity from the navigation property collection and set the values in the new properties. You should have in mind, that the value in this properties will be loaded on demand unless you use a fetch strategy to load the navigation property for all Addresses, which is not recommended, if you need only one Address.

I hope that helps. If you need additional guidance and help to implement any of these cases or if you need any further assistance with OpenAccess ORM, do not hesitate to contact us again.

Regards,
Boris Georgiev
Telerik

[Paul]

public virtual string Zip
{
    get
    {
        string query = (
            from address in this.Addresses
            where address.Version==0
            select address.ZIP).First();

        return query;
    }
}

Yep, option #2 works perfectly - and I'll trust that the ORM optimizations will allow this to scale up without too much additional DB overhead! From what I'm seeing, it's only hitting the database just before the objects are actually retrieved to be displayed on screen - a very nice touch.

Many thanks for the pointer, Boris. I look forward to a productive relationship.