Hello,

I tried to use the nice feature of the designer that allows to add a property mapped to a related entity field. I fetched the entity and the mapped property came up with the default value (it was an integer so it was zero).

I tried prefetching the related entity and it worked properly. So as I understand it, this mapping works only if both entities exist in memory...

I'm pointing this out because at first I had understood that loading such an entity (i.e. one that has a property mapped to a related entity field) would cause the DQE to generate an INNER JOIN and fetch the field from the database, while loading only my entity. I hope this made sense

I can see how the current implementation is interesting, however I think my first understanding would be more interesting, at least in some cases.

So I wanted to ask: First, did I get it right? Second, if I got it right, any chance I can get the implementation I would like?

Thanx in advance, álvaro.-

Alvaro wrote:

Hello,

I tried to use the nice feature of the designer that allows to add a property mapped to a related entity field. I fetched the entity and the mapped property came up with the default value (it was an integer so it was zero).

I tried prefetching the related entity and it worked properly. So as I understand it, this mapping works only if both entities exist in memory...

In adapter, yes. In selfservicing, they're loaded on the fly, which can degrade performance if you bind it to a grid, so prefetch before binding in that case.

I'm pointing this out because at first I had understood that loading such an entity (i.e. one that has a property mapped to a related entity field) would cause the DQE to generate an INNER JOIN and fetch the field from the database, while loading only my entity. I hope this made sense

No that's not done, to offer the flexibility to load when you want to. For example if you want to load the additional entities, you can use a context and prefetch the related entities into the already loaded entities. (add existing entities to context -> fetch the graph again).

INNER JOINs could make sense, but if the related entity contains a blob you don't want that to happen every time you load an entity

I can see how the current implementation is interesting, however I think my first understanding would be more interesting, at least in some cases.

So I wanted to ask: First, did I get it right?

nope, no points

Second, if I got it right, any chance I can get the implementation I would like? Thanx in advance, álvaro.-

No that's not possible. the reason for that is that the select list in your proposal contains multiple entities. So for each row, the object fetcher has to read data into multiple entities, not just one.

Otis wrote:

In adapter, yes. In selfservicing, they're loaded on the fly, which can degrade performance if you bind it to a grid, so prefetch before binding in that case.

INNER JOINs could make sense, but if the related entity contains a blob you don't want that to happen every time you load an entity

Second, if I got it right, any chance I can get the implementation I would like? Thanx in advance, álvaro.-

No that's not possible. the reason for that is that the select list in your proposal contains multiple entities. So for each row, the object fetcher has to read data into multiple entities, not just one.

Well thanks for your response. I was using Adapter btw (I keep forgetting to mention it).

This question was mainly about binding entities to grids. Very often an entity will contain a reference to a configuration table which in turn has a description. When binding the entity to a grid, you want the description to show up, not the identifier. Our first solution was to use typed view, because it's kinda clumsy to bind a bunch of related entities to a grid.

Then when you launched the mapped field feature, I thought oh great now I can add the mapped fields in the designer, fetch the entity, bind it to a grid, and the description will be there for me.

I still think that for this scenario (say, EntityA has fields Id, Name, IdConfig that references EntityConfig which has fields IdConfig, Description) it would be useful to have the DQE generate an sql like this:

SELECT entityA.Id, entityA.Name, entityA.IdConfig, entityConfig.Description FROM EntityA entityA INNER JOIN EntityConfig entityConfig ON entityA.IdConfig = entityConfig.IdConfig

And then just assign this data to EntityA's properties.

I can however understand your reasons for not doing this. I guess we could make do with the mapped fields feature as it is, prefetching the configuration entities. Just wondering then, how worse would it be from a performance point of view (in comparison with the SELECT I wrote)?

cheers álvaro.-

p.s.: I still can't get over how good your support is, you're an example to follow.

Alvaro wrote:

Otis wrote:

In adapter, yes. In selfservicing, they're loaded on the fly, which can degrade performance if you bind it to a grid, so prefetch before binding in that case.

INNER JOINs could make sense, but if the related entity contains a blob you don't want that to happen every time you load an entity

Second, if I got it right, any chance I can get the implementation I would like? Thanx in advance, álvaro.-

No that's not possible. the reason for that is that the select list in your proposal contains multiple entities. So for each row, the object fetcher has to read data into multiple entities, not just one.

Well thanks for your response. I was using Adapter btw (I keep forgetting to mention it).

This question was mainly about binding entities to grids. Very often an entity will contain a reference to a configuration table which in turn has a description. When binding the entity to a grid, you want the description to show up, not the identifier. Our first solution was to use typed view, because it's kinda clumsy to bind a bunch of related entities to a grid.

Then when you launched the mapped field feature, I thought oh great now I can add the mapped fields in the designer, fetch the entity, bind it to a grid, and the description will be there for me.

I still think that for this scenario (say, EntityA has fields Id, Name, IdConfig that references EntityConfig which has fields IdConfig, Description) it would be useful to have the DQE generate an sql like this:

SELECT entityA.Id, entityA.Name, entityA.IdConfig, entityConfig.Description FROM EntityA entityA INNER JOIN EntityConfig entityConfig ON entityA.IdConfig = entityConfig.IdConfig

And then just assign this data to EntityA's properties.

I can however understand your reasons for not doing this. I guess we could make do with the mapped fields feature as it is, prefetching the configuration entities. Just wondering then, how worse would it be from a performance point of view (in comparison with the SELECT I wrote)?

It's a trade-off: if you want lists which are build from combined entity fields, use a typed list, when you want entities, use entities. Typed lists were added for the purpose you describe, as often you need a list of data which is hard(er) to do with plain objects.

Typed lists have the disadvantage that they're not entities, so they're read-only.

Prefetching is pretty fast, it performs one query per graph node, so 2 queries in your situation. I didn't make it 'automatic' to fetch the related entities whenever you fetch a root entity, as 'automatic' would be limiting you in situations where you don't need the related entities.

There are other possibilities as well, to show the description for example. You can fetch the lookup entities (or fetch them in a typedlist/dyn. list) and bind them to a dropdown. Then bind the collection of entities (which have an ID column for the lookup value) to a grid and also bind the id column to the dropdown, so that when you switch rows in the grid, the dropdown changes value as well, namely the description is shown for the id value in the root entity currently selected in the grid. Popular grids can even do this in teh grid themselves.

This has the advantage that you fetch only the entities you want to work with, and you only have to fetch the lookup's once (at the start of the program for example, if they never change/hardly change).

p.s.: I still can't get over how good your support is, you're an example to follow.

thanks!

I support Alvaro's request, it would be really nice to have those fields returned when fetching an Entity, using Inner Joins to fetch them from the related tables.

Otis: u said "INNER JOINs could make sense, but if the related entity contains a blob you don't want that to happen every time you load an entity"

But you forgot that the developer/designer chooses the fields to be mapped and returned. SO if he wants to retrieve a BLOB objects it's his problem.

But most of the times we want to retrieve names or descriptions from LOOKUP tables.

Anyway it will be the developer choice to map whatever fields he needs.

Have a nice day.

Walaa wrote:

I support Alvaro's request, it would be really nice to have those fields returned when fetching an Entity, using Inner Joins to fetch them from the related tables.

Otis: u said "INNER JOINs could make sense, but if the related entity contains a blob you don't want that to happen every time you load an entity"

But you forgot that the developer/designer chooses the fields to be mapped and returned. SO if he wants to retrieve a BLOB objects it's his problem.

Though, it should IMHO be a matter of what the context is in which you need the data: that's also why lazy loading is as flexible as it is now: you can decide in your code what to do in that particular situation.

Also, the related fields are not stored inside the entity, they're in the entity they're actually in. So if I map 'Customer.CompanyName' in Order, the value is in Order.Customer, not in Order. If I'd do that, I'd create an entity mapped on several tables. that might seem tempting, but you mostly only need that for readonly situations, in which case you can use a typedlist.

But most of the times we want to retrieve names or descriptions from LOOKUP tables. Anyway it will be the developer choice to map whatever fields he needs.

There's another way to use lookup table values. Lookup table values (like CountryName from the Countries lookup table) are used at runtime in GUI's, not BL. (most of the time). You don't need to fetch the values from the lookup table with each entity you fetch, you can also use databinding at runtime to replace the ID (fk) in the entity, with the lookup table value. For example, if you have a list of customers bound to a grid, you can replace the CountryCodeID with a dropdownbox with the Country entities and bind Customer.CountryCodeID to the ID field of teh entities in Country. The user can then use the dropdown box to set the CountryCodeID while seeing Country names, not ID's.

The fun thing is, you can thus fetch the lookup table values once, and re-use them every time.

For example:

[Test]
public void DataBindingTest()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        EntityCollection employees = new EntityCollection(new EmployeeEntityFactory());
        EntityCollection regions = new EntityCollection(new RegionEntityFactory());

        adapter.FetchEntityCollection(employees, null);
        adapter.FetchEntityCollection(regions, null);

        Databindingtester dbtester = new Databindingtester(employees, regions);
        dbtester.ShowDialog(null);
    }
}

Simple form with a grid and a dropdownbox:

public Databindingtester(EntityCollection employees, EntityCollection regions)
{
    InitializeComponent();

    _regionCombo.DataSource = regions;

    _regionCombo.DisplayMember = "RegionDescription";
    _regionCombo.ValueMember = "RegionId";

    _employeeGrid.DataSource = employees;
    _regionCombo.DataBindings.Add("SelectedValue", employees, "RegionId");
}

In my form I have the _regionCombo below the grid, but lots of grids allow you to place the dropdown at the spot of the ID column, hiding the ID completely.

When I change the selected row, the value in the combo changes, and when I change the value in the combo, the ID FK (regionID in this case) in the selected entity changes. No need to fetch the RegionEntities for each employee.

You mentioned a special type of LOOKUPs "Countries" which rarely changes and can be maintained in the GUI not the BL.

But check this situation:

I have an Account Entity, with related lookups as: AccountIndustry, AccountStatus, AccountType, AccountRating, AccountOwnership, and many more...

And I want to view a read-only report of the Account data.

From a performance aspect, it would be better just to have a Join to return the Fields I want from those lookups which will be the "name" field in my situation. Than having to load all the lookups records in controls such as dropdown lists to have the name displayed.

I know this can be achieved by Typed Lists or PrefetchPathes But PrefetchPathes fetch all the related Entity's fields no selected ones, and Typed Lists is a read only view, while in my situation I may need to update the Account Entity data while having the Related Entities' data as read-only.

Walaa wrote:

You mentioned a special type of LOOKUPs "Countries" which rarely changes and can be maintained in the GUI not the BL.

Isn't that what lookups are for? If they change often, it's a related entity, not a lookup. Sorry for the word-game, but otherwise IMHO we'll end up talking about two different things

But check this situation:

I have an Account Entity, with related lookups as: AccountIndustry, AccountStatus, AccountType, AccountRating, AccountOwnership, and many more...

And I want to view a read-only report of the Account data.

then create a typed list, that's why they're there for. Working with objects, has one disadvantage: creating a new select list build from attributes of different, related, entities is not possible as the resulting list doesn't fit in an object, it has more/less/different fields. In fact it's a new entity.

From a performance aspect, it would be better just to have a Join to return the Fields I want from those lookups which will be the "name" field in my situation. Than having to load all the lookups records in controls such as dropdown lists to have the name displayed.

This is what the typedlist does, it joins the entities together and selects the fields you specified.

The performance gain of using dropdowns with lookup data, is that you can fetch them less often (the lookups), and just fetch 'Account', not the lookups.

I know this can be achieved by Typed Lists or PrefetchPathes But PrefetchPathes fetch all the related Entity's fields no selected ones, and Typed Lists is a read only view, while in my situation I may need to update the Account Entity data while having the Related Entities' data as read-only.

Ok, though in the situation where you need to edit / update Account entity data, you're not viewing it in a readonly fashion as you explained earlier in your posting.

I agree that with a set of joins, similar to typed lists, it's of course possible to fetch the data in 1 go, if you can do it in a typedlist, you can do it in an entity. The problem is: where to store the field data of related entities, like the Name field data? In Account? If that's done, what will happen when you assign a new related entity to account so 'name' is now located there. You then have 2 places where 'name' is stored, one in the Account entity (readonly) and one in the related entity (writable).

Otis,

Your last point makes a lot of sense.

This makes me wonder why in the first place you provided "Fields on Related Fields" feature. !!!

Coz when I first read the documentation I got it like Alvaro did. I didn't realize I'm wronge until I tried to use it in practical.

Walaa wrote:

Otis,

Your last point makes a lot of sense.

This makes me wonder why in the first place you provided "Fields on Related Fields" feature. !!!

Well, to overcome the readonly-problem of the typedlist , and to help developers add properties which reflected fields in related entities. Initially it was designed to become expression fields, where you could define field = relatedentity.field2 * otherrelatedentity.field3 etc. though that took too much time, so it was cut. Still developers often added convenience properties to entities, and had to do that in the generated code. To help them I added these to the designer. This feature will be extended in the future with expressions.

With a lot of related entities, the fetches per entity can become a bit slower, (though with a join it still will be slower), I hope to address that in v2.0

Is there an easy way to construct prefetch paths for fields mapped on related fields (databinding to grid scenario)? Or do I have to manualy construct prefetch paths for all fields mapped on related fields for all entities?

Dynamic Lists are a great solution in places like this, where the data needs to be displayed as an aggregation of multiple entities. The problem, of course, is that it is late-bound and not strongly typed.

The new ability to create multiple "versions" of an entity combined with mapped fields, I think, holds much promise, but, like Frans said, you're now blurring the definition of an entity. But, again, IMHO, entities make less sense in the presentation layer than in the lower layers: users don't care about entities; they care about the data they need to see! So, "deconstructing" a data entity into a presentation object or aggregating one from the same using the Designer makes a lot of sense.

I do agree it would be nice to be able to easily construct a prefetch path/relations for those entities upon which mapped fields are mapped.

Jeff...

jeffreygg wrote:

Dynamic Lists are a great solution in places like this, where the data needs to be displayed as an aggregation of multiple entities. The problem, of course, is that it is late-bound and not strongly typed.

The problem with dynamic lists is that they need a lot of programming, they are late-bound, not strongly typed and read-only. I can not afford such drawbacks. Nor can i afford typed-lists.

Entities with fields mapped on related fields were perfect in my scenario. That was until I found out that they use lazy loading instead of inner joins. I personaly do not care about bluring the definition of entity. When I have a RoomEntity, it is perfectly ok for me to have a RoomTypeName (field mapped on RoomTypeEntity.Name) in it.

I hope this will be addressed some way or another. I'd prefer an option that would turn on the using of inner joins for fields mapped on related fields.

If you want to avoid lazy loading, use a prefetch path up front. The fields aren't loaded into the entity which holds the field that's mapped onto the related field, because what should happen if you set order.CompanyName (which is mapped on order.Customer.CompanyName) to a different value and save order?

Otis wrote:

The fields aren't loaded into the entity which holds the field that's mapped onto the related field, because what should happen if you set order.CompanyName (which is mapped on order.Customer.CompanyName) to a different value and save order?

You have a valid point here, Otis. But I always make fields mapped on related entity fields read-only (I think it's a bad practice to change Customer.CompanyName by accessing order.CompanyName). So this situation would never occur.

Hint: option that turns on inner joins automaticaly turns on read-only access of fields on related entity fields.

Deivis wrote:

Otis wrote:

The fields aren't loaded into the entity which holds the field that's mapped onto the related field, because what should happen if you set order.CompanyName (which is mapped on order.Customer.CompanyName) to a different value and save order?

You have a valid point here, Otis. But I always make fields mapped on related entity fields read-only (I think it's a bad practice to change Customer.CompanyName by accessing order.CompanyName). So this situation would never occur.

But that's a special case, because what will happen if you have 10 read/only fields and 1 writable field? Then the fields aren't stored inside the order, but inside the customer object. And what if you reset the FK CustomerID in order? It's getting messy then.

Hint: option that turns on inner joins automaticaly turns on read-only access of fields on related entity fields.

That's not likely going to happen I'm afraid.