I doubt it but I thought I'd ask.

Is there any way to get EntityView2 to filter on a derived entity type AND have that derived type be the returned type rather than the base type?

Could you give an example? (you can define an in-memory filter based on a predicate which filters on an entity property value)

An entity view is typed, e.g. EntityView2<CustomerEntity> so you can filter it, but the type doesn't change of course.

I have Policy (and others) deriving from Asset

EntityCollection<AssetEntity> is the collection. What I would have liked is to be able to add a view onto this showing just Policy entities but them as being typed as Policy.

policyView = new EntityView2<AssetEntity>(OwnedAssets, entity => entity is PolicyEntity); does the filtering fine but is always going to return AssetEntity which is annoying when I just want to assign it to my grid's datasource.

Actually, now I've really got into the derived entity thing this might be useful in a number of places - if you don't already have something I've missed, I might have a go at writing this - just delegating to EntityView2<AssetEntity> and casting might work.

Cheers Simon

simmotech wrote:

I have Policy (and others) deriving from Asset

EntityCollection<AssetEntity> is the collection. What I would have liked is to be able to add a view onto this showing just Policy entities but them as being typed as Policy.

policyView = new EntityView2<AssetEntity>(OwnedAssets, entity => entity is PolicyEntity); does the filtering fine but is always going to return AssetEntity which is annoying when I just want to assign it to my grid's datasource.

The main reason is that the view doesn't contain any elements, it simply provides an enumerable view on the underlying collection. So it simply contains a list of indices into the collection. Having a view EntityView2<T2> on an EntityCollection<T1> where T1 is a supertype of T2, is not reliable: it relies on the correctness of the filter to provide the proper elements which all have to be T2. While this can be built-in of course, we didn't go there for this reason.

Actually, now I've really got into the derived entity thing this might be useful in a number of places - if you don't already have something I've missed, I might have a go at writing this - just delegating to EntityView2<AssetEntity> and casting might work.

Be careful with inheritance: inheritance in the DB always comes at a cost with respect to performance. The alternative, optional 1:1 relationships can in some cases be more efficient.

Otis wrote:

Having a view EntityView2<T2> on an EntityCollection<T1> where T1 is a supertype of T2, is not reliable: it relies on the correctness of the filter to provide the proper elements which all have to be T2. While this can be built-in of course, we didn't go there for this reason.

So as far as you are aware, the main issue is to ensure the filter is always applied (it is fairly trivial to create).

The other thing I know I need to ensure is that the PropertyDescriptors returned are for the inherited type and not the base type - shouldn't be a problem.

AddNew in EntityView2: Am I correct in thinking this could fail where a hierarchy is involved because the base type could be abstract? Or is that abstract just in the designer?

RaisesItemChangedEvents: Not sure what this is. It is set to false but allows the user to set it to true but there is no difference in handling whether true or false??

Otis wrote:

Be careful with inheritance: inheritance in the DB always comes at a cost with respect to performance. The alternative, optional 1:1 relationships can in some cases be more efficient.

I'd like to understand more about optional 1:1 relationships and see it they would be useful here but I'm not sure they would for my scenarios.

From the example above, Asset is the parent of Policy, NonPolicyAsset, ClientProperty. There may be more added in future. Since it is a hierarchy, bringing back all Assets for an 'owner' is very easy. Sure it is going to query each table but what is the alternative?

Now 'owner' is also a hierarchy in that it could be a Person, a Company, a Trust etc. etc. How else could we make each of these types an 'owner' of any of the Asset types? What we have done is create a LegalBody abstract entity at the root of this hierarchy - it doesn't have much itself, just an ID and a DisplayName, and then derive Person, Company, Trust from it. Person also has User and Client derived with their relevant fields. Again, I can see that a query accesses more tables but the flexibility of being able to treat all these types at a lower inheritance level is fantastic. Without it, we would need a table to map Person to Policy, Company to Policy, Client to Policy and so on. We would then have to stitch these ownerships back again in memory somehow. Can you think of an alternative to this?

One feature that might mitigate a slightly slower query is that we can use one query to replace lots of smaller ones. For example, a Person might be related with any number of 'Roles'. Say "Accountant" and "Solicitor". In the app, we want a dropdown showing all the Accountants and another showing all the Solicitors. This could be repeated for any number of Roles of course but also we need a list of All Users; All Advisers, All Users by their DisplayNames (sortable), All Users with their formal names, All Users where duplicates are made unique by adding their ID or postcode. You get the idea.

What we are actually currently doing is fetching all Person/descendants (and their roles) in one query into a DataTable. We then add additional columns as required - eg. from FirstNames and Surname fields we can generate a SortableName, a FormalName, Initials, UniqueName. This single datatable then has DataViews applied and can support 20/30 different dropdown datasources throughout the application. The table is periodically refetched and those custom columns refreshed on a background thread, it is then transferred onto the GUI thread and each DataView gets 'assigned' the new DataTable and bingo, every dropdown on every form in the whole app gets the latest data automatically without duplication of instances.

Speedwise it is very fast on my dev PC, 6400+ Persons plus formatting is a fraction of a second. I expect it will still be very reasonable over a network especially on a background thread.

I really like this concept of a single EntityCollection/DataTable with many EntityView2/DataView attached to it so if I can create a TypedEntityView view then I think it will be very useful.

My 2 cents: if entities have fixed types, and no way one could need to change it's type, then use an Inheritance hierarchy.

They won't change type across or up the hierarchy but they might move down the hierarchy. For example, a Person might become a Client where Client inherits from Person.

I assume this is just a case of adding a Client record using the Person existing PK?

simmotech wrote:

Otis wrote:

Having a view EntityView2<T2> on an EntityCollection<T1> where T1 is a supertype of T2, is not reliable: it relies on the correctness of the filter to provide the proper elements which all have to be T2. While this can be built-in of course, we didn't go there for this reason.

So as far as you are aware, the main issue is to ensure the filter is always applied (it is fairly trivial to create).

The other thing I know I need to ensure is that the PropertyDescriptors returned are for the inherited type and not the base type - shouldn't be a problem.

Do you already have these type filters working?

simmotech wrote:

AddNew in EntityView2: Am I correct in thinking this could fail where a hierarchy is involved because the base type could be abstract? Or is that abstract just in the designer?

EntityView(s) doesn't have any AddNew method, as the view is just a representation (list of indexes) of the underlying collection. Abstract supertypes don't have the keyboard "abstract" in code, but their ctor's are internals. Anyway, you can Add T1, T2, T3, on a EntityCollection<T> where T1, T2, T3 are subtypes of T.

simmotech wrote:

RaisesItemChangedEvents: Not sure what this is. It is set to false but allows the user to set it to true but there is no difference in handling whether true or false??

This comes from 'System.ComponentModel.IRaiseItemChangedEvents' and according to the docs, it indicates whether a class converts property change events to System.ComponentModel.IBindingList.ListChanged events. So it gives you eventhandler listening.

simmotech wrote:

Otis wrote:

Be careful with inheritance: inheritance in the DB always comes at a cost with respect to performance. The alternative, optional 1:1 relationships can in some cases be more efficient.

I'd like to understand more about optional 1:1 relationships and see it they would be useful here but I'm not sure they would for my scenarios.

Basically, it's a matter of performance. If you don't get hurt by your inheritance scenario, then stick to it. But in other cases would be wise to use just 1:1 rels, so instead of fetching a subtype, you can fetch it separately as a related entity of the supertype (or viceversa).

simmotech wrote:

They won't change type across or up the hierarchy but they might move down the hierarchy. For example, a Person might become a Client where Client inherits from Person.

I assume this is just a case of adding a Client record using the Person existing PK?

It's not that easy. Creating a Client (which is a subtype of Person) will create both Person and Client records on DB, as this is how inheritance works. If you will down-promote a type, you should first delete the parent (Person) and then create the Client. Another option is to map another entity (Client2) which is not part of the inheritance, you use this entity just to create raw Client records on DB. There are some threads on the forums pointing how to do this and it's disadvantages.

Well, I have enough working to prove the concept and give me enough for my current requirement - grid binding.

    public class TypedEntityView<TEntity, TBaseEntity>: IBindingListView, ITypedList, IEnumerable<TEntity>, IRaiseItemChangedEvents, IEntityView2, IDisposable where TEntity: TBaseEntity, IEntity2, new() where TBaseEntity: EntityBase2
    {
        readonly EntityView2<TBaseEntity> source;
        readonly TEntity dummyInstance;

I basically implement all the interfaces and delegate most of them to the source field. For GetItemProperties, I use base.GetPropertyDescriptors but pass it the dummy instance. I don't support list accessors because I don't need them for simple grid binding.

The filter is

new PredicateExpression(new DelegatePredicate<TBaseEntity>(baseEntity => baseEntity is TEntity))

I tend not to use Filtering and Sorting on a View because I let the grid do it. If I need additional filtering, I will just need to ensure this predicate gets anded with whatever is passed.

EntityViewBase does have an AddNew() method to support IBindingList and IList. It just calls AddNew on the collection however which won't necessarily work for hierarchies. Nothing else that EntityView2 could do about that but my View knows exactly what type of new entity to create.

IRauseItemChangedEvents. I understand what it does but I don't understand why it is in EntityView since setting the flag doesn't change what events are fired (as far as I can see) so whatever is listening is likely to fail/not work correctly if this flag is set to true.

It's not that easy. Creating a Client (which is a subtype of Person) will create both Person and Client records on DB, as this is how inheritance works. If you will down-promote a type, you should first delete the parent (Person) and then create the Client.

Why delete the Person just to create a Client? That wouldn't be possible in our scenario BTW since the Person's ID could be referenced in many other places. I don't see why I can't just create a Client record using the ID of the existing Person. Surely LLBLGen will then, on the next fetch, bring the entity back as a Client - it can't know that the Person and Client rows weren't created at the same time. Your're worrying me now , I've gotta go and promote some Persons to Client to check this out.

Just a note: You can't change types at runtime, if you need that, use a 'role' like construct instead. This debate has been held a lot in the past, I'm not going to repeat myself but it comes down to the fact that 1) you can't change a type of an object in .net at runtime either (casting isn't changing types, if a cast invokes op_implicit it performs a convert) and 2) inheritance is based on interpreting the data, manipulating parts of the data to 'promote' (i.e. change a type) at runtime will in 100% of the cases bite you in the future, hence we don't allow it. It's also a sign your model is actually wrong and needs a role like construct instead of inheritance.

IRaiseItemChangedEvents is an interface for BindingSource. So it's used by the BindingSource control in winforms, if you bind an EntityView(2)<T> via a BindingSource to a control, the BindingSource control will honor the flag value RaiseItemChangedEvents, it's not used by our code.

Otis wrote:

Just a note: You can't change types at runtime, if you need that, use a 'role' like construct instead. This debate has been held a lot in the past, I'm not going to repeat myself but it comes down to the fact that 1) you can't change a type of an object in .net at runtime either (casting isn't changing types, if a cast invokes op_implicit it performs a convert) and 2) inheritance is based on interpreting the data, manipulating parts of the data to 'promote' (i.e. change a type) at runtime will in 100% of the cases bite you in the future, hence we don't allow it. It's also a sign your model is actually wrong and needs a role like construct instead of inheritance.

Ah, once again we (sort of) disagree

I will agree that you can't change a dotnet type at runtime but that is not what I need.

The hierarchy in question is LegalBody<-Person-<-Client

If I fetch LegalPersonEntitys with an ID of 100, I will get back a PersonEntity (because that's how I created it) because of the polymorphic fetch.

I now want to 'promote' that Person to be a Client.

I do some process, which I describe below, and then I refetch LegalPersonEntitys with an ID of 100 and this time I will get a ClientEntity. No casting, no implicit ops Job done!

The process should be quite simple: All I need to do is insert a row into the Client table using ID=100 and setting up all the fields that are specific to Client. LLBL will then fetch this as part of its polymorphic fetch.

LLBLGen won't do this out of the box of course, it will always assume that If I create a Client with an ID of 100 then it must add a Person row and a LegalBody row first using that ID and since they are already there, will fail.

So I can either finger-poke the Client row in myself or be a bit more sophisticated and add an option to DataAccessAdapter to do it.

My first try (as a proof of concept) was to override GetFieldsPersistenceInfos and then filter out the 35 infos it normally gets to remove the ones where the SourceObjectName != "Client". So I now return the 27 Client infos instead.

Failed unfortunately because EntityFieldPersistenceInfoList (thanks for supplying the source code BTW, it is great to be able to step through to see how things are working!) matches the wrong 27 fields with the info. Not its fault of course, there's no reason for it to expect someone to have filtered the infos. It did result in an attempt to only insert a Client row though so removing infos for non-Client is definitely the right way to go.

So what I need to do is leave the 35 infos unfiltered, let EntityFieldPersistenceInfoList match them with the 35 fields and then do the filtering. LLBLGen will then insert just the Client row with the Client fields and bingo, I have my 'conversion'.

(Actually it would seem perfectly possible to 'change' an entity type up a hierarchy by removing rows from lower down too once any relations have been dealt with)

So now I have explained exactly what I need and how I think it would work, any chance of a hand helping me find the perfect point to do the filtering? I think I need a new method on my inherited DataAccessAdapter which looks like the existing SaveEntity but you may know of a better way.

Cheers Simon

I may be wrong on this but in DynamicQueryEngineBase.CreateInsertDQ there is a line to decide whether to use a BatchActionQuery or not

if(targetFieldInfos.Count > 0)

Isn't this always going to create a BatchActionQuery and should be > 1??

Update: The code below works for me! But it depends on the issue above - I am manually telling it to create a non-BatchActionQuery at the moment by breakpointing at the above code.

        public bool Promote<TEntity>(TEntity entityToPromote) where TEntity: EntityBase2
        {
            // Get the fields and fields persistence info
            var fieldsPersistenceInfo = GetFieldPersistenceInfos(entityToPromote.Fields);
            var fields = entityToPromote.Fields.GetAsEntityFieldCoreArray();

            // Sort them by inheritance level
            var targetFieldInfos = new EntityFieldPersistenceInfoList(fields, fieldsPersistenceInfo);

            // Just take the last set 
            var targetFieldInfosForEntity = targetFieldInfos[targetFieldInfos.Count - 1];

            // Create the insert query
            var engine = CreateDynamicQueryEngine();
            var insertQuery = engine.CreateInsertDQ(targetFieldInfosForEntity.Fields, targetFieldInfosForEntity.FieldsPersistenceInfo, GetActiveConnection());

            // Execute it and return true if one insert was successful
            return ExecuteActionQuery(insertQuery) == 1;
        }

I explained my PoV about inheritance and what you shouldn't do. If you want to shoot yourself in the foot, by all means, do it, but trust me, it's not wise to do it that way.

Isn't this always going to create a BatchActionQuery and should be > 1??

No. Count is 0 if there's just 1 target in the list of targets. In that situation it creates a normal action query, otherwise it creates a batch action query, which is a set of action queries, one for each target.

Otis wrote:

I explained my PoV about inheritance and what you shouldn't do. If you want to shoot yourself in the foot, by all means, do it, but trust me, it's not wise to do it that way.

Isn't this always going to create a BatchActionQuery and should be > 1??

No. Count is 0 if there's just 1 target in the list of targets. In that situation it creates a normal action query, otherwise it creates a batch action query, which is a set of action queries, one for each target.

No Frans, you talked about debates you had had previously (not with me) about changing types at runtime which is not what I wanted anyway and I agreed that it is not possible to perform a magic cast.

I explained exactly what I wanted and I have achieved exactly that in a perfectly safe transaction generated by LLBLGen (more or less).

That is not shooting myself in the foot because I achieved what I wanted and the model is reflecting real life absolutely - a known Person now becomes a Client. If you really feel it is not wise then explain why instead of just asking me to trust you.

My code is extracting the last of the three buckets and passing it back into CreateInsertDQ which processes it again and returns a set of 1 not 0. Maybe I missed something or the code has changed but you might want to check it.

simmotech wrote:

Otis wrote:

I explained my PoV about inheritance and what you shouldn't do. If you want to shoot yourself in the foot, by all means, do it, but trust me, it's not wise to do it that way.

Isn't this always going to create a BatchActionQuery and should be > 1??

No. Count is 0 if there's just 1 target in the list of targets. In that situation it creates a normal action query, otherwise it creates a batch action query, which is a set of action queries, one for each target.

No Frans, you talked about debates you had had previously (not with me) about changing types at runtime which is not what I wanted anyway and I agreed that it is not possible to perform a magic cast.

I do think you are changing types (Person -> Client), exactly as I described: you trick the interpretation of how inheritance works in the DB and magically, a type X becomes a type Y. Exactly not what you should want as it's a sign you need a role-like system, not inheritance. Either create a new client entity, insert it and remove the 'Person' entity, OR don't use inheritance. It's equal to changing the discriminator value in a TPEH table to make a row look like another type. That's not how it works. Next up is sharing a row in Person between two Client rows, because... hey, they're the same, right?

I do this work now for a couple of years ( ) and yes I've held these debates with others before, not with you, but that doesn't mean I want to re-hash old debates with you. I just wanted to help you avoiding problems. Like I tried with the sequence issue. You opted to ignore me, which is fine, but you have to realize, it's just advice which isn't given because I have time to kill, it's given because we see that you will make a mistake which is common among inheritance usage and I clearly said what you should do instead.

That is not shooting myself in the foot because I achieved what I wanted and the model is reflecting real life absolutely - a known Person now becomes a Client. If you really feel it is not wise then explain why instead of just asking me to trust you.

I already explained why, and I won't rehash things again. It's a clear sign your inheritance hierarchy is better off with a role instead of your type changing code. What happens if thread X wants to delete Person 1, thread Y (another website user) updates Person 1 to become a Client, however X reads Person 1 first (directly deletes don't work with TPE), then Y changes the type, then X deletes 1: boom, as a dangling row in Client prevents X to proceed. Just an example of what might happen.

Now, this might drag on with you coming up with another example which makes this look like something that won't happen, I really don't care. I told you exactly what you should do instead, and frankly, just as with the sequences stuff, I really don't understand why you want to waste so much time over this. Just add a role instead of inheritance and you 1) can save yourself the time writing long posts here 2) you save me the time to respond to them (or bringing me to the point where I ignore them, as what's the point of re-hashing an advice if it's not followed ) and 3) you save yourself the time writing custom code which isn't necessary (and leads to more complexity in your own code base which is IMHO never a good sign, as people who have to maintain the code have to understand the reasoning behind it too.)

But alas, suit yourself. I tried to give you proper advice, like we do every time we see someone on the edge of making a mistake. You don't have to trust me, I just wanted to tell you that you might not want to do that, even if you perfectly can.

My code is extracting the last of the three buckets and passing it back into CreateInsertDQ which processes it again and returns a set of 1 not 0. Maybe I missed something or the code has changed but you might want to check it.

I ran a test and it indeed results in always a bucket in the list. I tracked down the code and it has been this way since at least 2005. So is the if-statement justified?

I changed the if-statement to 'if(targetFieldInfos.Count > 1)' and this failed on 1 test: a test where a developer has expanded the fields of an entity with another field:

public class ExtendedCustomerSeEntityFactory : CustomerEntityFactory
{
    /// <summary>
    /// Create the fields for the customer entity and adjust the fields collection
    /// with the entity field object which contains the scalar expression
    /// </summary>
    /// <returns></returns>
    public override IEntityFields2 CreateFields()
    {
        IEntityFields2 toReturn = base.CreateFields();
        toReturn.Expand(1);
        IEntityField2 scalarField = new EntityField2("NumberOfOrders",
                new ScalarQueryExpression(OrderFields.OrderId.SetAggregateFunction(
                    AggregateFunction.Count),
                (CustomerFields.CustomerId == OrderFields.CustomerId)));
        toReturn.DefineField(scalarField, toReturn.Count - 1);
        return toReturn;
    }
}


public class CustomerEntityExSe : CustomerEntity
{
    public CustomerEntityExSe() : base() { }

    public CustomerEntityExSe(int customerID)
        : base(customerID)
    {
    }

    protected override IEntityFactory2 CreateEntityFactory()
    {
        return new ExtendedCustomerSeEntityFactory();
    }

    protected override IEntityFields2 CreateFields()
    {
        return CreateEntityFactory().CreateFields();
    }


    public Nullable<int> NumberOfOrders
    {
        // read the last field in the fieldslist, as we've added our scalar
        // expression field to the end of the fields list.
        get
        {
            object value = this.Fields[this.Fields.Count - 1].CurrentValue;
            if(value != null)
            {
                return Convert.ToInt32(value);
            }
            else
            {
                return null;
            }
        }
    }
}


//...
[Test]
public void InsertCustomerTestWithExtendedFields()
{
    DataAccessAdapter adapter = new DataAccessAdapter();

    try
    {
        CustomerEntityExSe newCustomer = new CustomerEntityExSe();
        newCustomer.CompanyName = ConstantsEnums.Customer1CompanyName;
        newCustomer.CustomerSince = new DateTime(2000, 1, 1);
        newCustomer.ContactPerson = ConstantsEnums.Customer1ContactPerson;
        newCustomer.CompanyEmailAddress = ConstantsEnums.Customer1CompanyEmailAddress;
        newCustomer.TestRunId = _testRunID;
        AddressEntity newAddress = EntityCreator.CreateNewAddress(1);
        newAddress.TestRunId = _testRunID;
        newCustomer.VisitingAddress = newAddress;
        newCustomer.BillingAddress = newAddress;
        OrderEntity o = new OrderEntity();
        o.OrderDate = DateTime.Now;
        newCustomer.Orders.Add(o);
        o.TestRunId = _testRunID;
        bool result = adapter.SaveEntity(newCustomer, true);
        Assert.AreEqual(true, result);
        Assert.AreEqual(EntityState.Fetched, newAddress.Fields.State);
        Assert.AreEqual(EntityState.Fetched, newCustomer.Fields.State);
        Assert.IsTrue((((int)newAddress.Fields[(int)AddressFieldIndex.AddressId].CurrentValue) > 0));
        Assert.IsTrue((((int)newCustomer.Fields[(int)CustomerFieldIndex.CustomerId].CurrentValue) > 0));
        int addressID = newAddress.AddressId;
        int customerID = newCustomer.CustomerId;
        Assert.IsTrue((newCustomer.VisitingAddressId == addressID));
        Assert.AreEqual(newAddress, newCustomer.BillingAddress);
        Assert.IsTrue((newAddress == newCustomer.BillingAddress));
        Assert.AreEqual(((IEntityCore)newAddress).ObjectID, ((IEntityCore)newCustomer.VisitingAddress).ObjectID);
        Assert.AreEqual(((IEntityCore)newAddress).ObjectID, ((IEntityCore)newCustomer.BillingAddress).ObjectID);

        PrefetchPath2 path = new PrefetchPath2(EntityType.CustomerEntity);
        path.Add(CustomerEntity.PrefetchPathBillingAddress);
        path.Add(CustomerEntity.PrefetchPathVisitingAddress);
        CustomerEntityExSe fetchedCustomer = new CustomerEntityExSe(customerID);
        adapter.FetchEntity(fetchedCustomer, path);
        Assert.AreEqual(1, fetchedCustomer.NumberOfOrders);

        fetchedCustomer.CompanyName += " ";
        adapter.SaveEntity(fetchedCustomer);
    }
    finally
    {
        adapter.Dispose();
    }
}

This fails in that case. The batch query approach makes it work properly.

So the if statement was doing the right thing, but for the wrong reasons. I think (but I have to dig really deep into SVN to find when the exact code is written as it's inside v1.0.2005.1, which is the first system with inheritance), at one time it did work the way it was intended but it resulted in an issue which was solved at another place, making the if-statement redundant.

So in short: the if-statement is redundant and the 'else' clause is dead code. It has to be refactored in shorter code, as it shouldn't use a batch query if there's just 1 target, but it also shouldn't fail if there are missing persistence info's because of scalar fields appended to the field list (fields list is then longer than the persistence info list, for obvious reasons).

Which means: simply assume it always produces a batch query, as the else clause will never be executed. It will be refactored in a future version to remove the dead code and to optimize it a bit (as 1 target doesn't need a batch query nor the bucket stuff). So in a future version this might change.

I respect your many years working on database/framework stuff, I really do. And believe or not I do listen to your advice even if I don't always follow it.

The thing is, your advice is general advice because you cannot possible know how my system is designed to work. One of the reasons for my long posts is that I try describe in more detail why I want to do something but even then I cannot describe every detail.

Your example with X deleting a Person whilst Y is promoting that Person to a Client is a good example. However, I know that I will never delete a Person and so that scenario cannot happen but there is no way you could know that. When a Person is fetched by whatever thread, they will come back as a Person or a Client - there is no in between as it is a transactional change.

That's not how it works. Next up is sharing a row in Person between two Client rows, because... hey, they're the same, right?

Why would you suggest I would be thinking about doing that. The PK of Client has to be the same as the PK in Person so that's not possible anyway AFAIK.

Similarly with the Sequences thing. Your advice was fine as general advice but it wasn't supported by the database so I couldn't follow it. So your next advice was to change the PK types to ints which might solve that problem but would quadruple the size of the database which was the point of supporting Sql CEin the first place. Its not like I was demanding another solution from you, I already had the solution in mind and was just asking for help about where I could insert table hints in DataAccessAdapter.

A Role-based system is possible (multi-role system because I already said we were using roles for tagging things) but I think it would break down on the other elements of the hierarchy - its not just Person->Client - there are other branches too.

I am interested in why you think an entity IS-A type and can never be changed. I thought of it more along the lines of an entity IS-A type at any one time although I agree with you deletions are not obvious but also they are not insurmountable - just need a bit of thinking about if it is to be done manually. A Company Car isn't a company car all its life, what would you do to change it in your sample?

I'm glad you found the issue/non-issue, depending on how you look at it, about the batch query stuff. You might be delighted to know that leaves me up a certain creek because I need to use the stuff in the else-clause 'cos the Batch-way doesn't work for my purposes - does something with ParameterParameterRelation. ( Edit: nulling out the LinkedSuperTypeField does the trick)

I am interested in why you think an entity IS-A type and can never be changed.

Let me say it differently. Inheritance was borrowed from the Object Oriented World, to the Relational World. And in Inheritance an object can't chnage it's type, to be promoted or downgraded in the Hierarchy. Or even toanother type at the same level.

The concept is the same in the database. Types can't change in general, as in a Customer can never be an Order, and a Car can not be an Employee.

Another example, is that Employee and Customer are both Persons, but you can't change an Employee to a Customer, unless you delete the Employee (resigned), and create the Customer.

Else if the same Employee as a Person, may exist as a Customer at the same time, then Both Records in both tables should reference the same Person Entity, and this turns into a Relational Model, not an Inheritance one.

You know your business more than us for sure, but we are speaking about the concepts. So in your business if you find out that you may need to change the type of an entity, then you should rethink of that attribute as a Role or a property but it should not represent the Type.

If a property can be changed, then this property should not define the type.

A Company Car isn't a company car all its life, what would you do to change it in your sample?

Don't use Inheritance, and either have a property for the type of the car, or build a relational model, where Company Car refer to Car, but it does not inherit from it, so that later on you can delete thois record and add another one to the ExecutivesCar which refers to the same Car entity.

Let me say it differently. Inheritance was borrowed from the Object Oriented World, to the Relational World. And in Inheritance an object can't chnage it's type, to be promoted or downgraded in the Hierarchy. Or even toanother type at the same level.

That's perfectly true if I was doing new Person() or new Client().

But what I am actually doing is more equivalent to:

IPerson PersonFetcher.Fetch(int id)

So I will get back either a Person or a Client and that could change between calls.

Another example, is that Employee and Customer are both Persons, but you can't change an Employee to a Customer, unless you delete the Employee (resigned), and create the Customer.

I would suggest that that is moving across a hierarchy rather than up (or down) and I agree with you that it is not a good idea.

I also agree with you that if Types can change frequently or across a hierarchy then Roles is the way to go.

However, my system requires that the Type changes only in one particular scenario, is a simple promotion and cannot be reversed (once a Client, has to stay a Client for legal auditing purposes).

The objections to this thus far have largely been theoretical/design issues. The one practical issue mentioned, deleting on different threads, is valid but not applicable in this case. The code is now written and working and so we will have to agree to disagree on whether my solution to my problem is a good idea or not.

Simon, you're your own boss, do what you have to do We just gave you advice not to do it. You find our arguments not convincing, which is your right, we just gave you advice it's better to do it differently.

In v4, the method CreateInsertDQ (and update equivalent) now properly creates BatchActionQueries only when necessary, so a single target insert / update won't use a BatchActionQuery object. If your code relies on this, it will be a breaking change. Thanks for bringing this to our attention.