You already have my project file and I am using LegalBody<-Person as an example.

LegalBodyEntity has 18 1:n or m:1 relationships defined. PersonEntity adds a couple more.

A user wants to Delete a Person. If any of those relationships involve this Person.ID then it is going to fail of course (excepting Cascade Deletes). So I am reviewing some options:-

1. Just delete, catch an exception and give a generic reason - "This Person is in use elsewhere". Not helpful to the user as to what to do.

2. Just delete, catch an exception and interpret the exception - "This Person is involved in an AccountsTransaction". But if the Person is involved in a lot of relationships then the user is going to get annoyed at removing that obstacle only to find another one in its place.

3. Semi-automated check across all the relationships (GetRelationships() or similar) creating a list of other tables where the Person is used. Show the user a complete list of obstacles if they really want to delete that Person. Issues: I can't see any model Delete Rules in the Relationship data (runtime-side) therefore false-positives would be shown.

4. Manual check using hard-coded relationships in a CanDeletePerson() method to build the obstacle list. Should work but a pain to create and difficult to maintain. Maybe split the relationships into 3 groups: (1) Relationships that should prevent Deletion outright (e.g. an Invoice to that Person must keep that Person in the system); (2) Relationships that need specific user confirmation (e.g. Questionnaire that would be deleted and not recoverable) ; (3) Relationships that are OK to be automatically deleted when the Person is deleted (eg. additional information of no use without the Person)

I suspect I will be using (1) or (2) for speed but (3) or (4) for certain important enties.

Can you guys see any mileage in me pursuing option (3)? And can you confirm whether DeleteRules are in fact present anywhere in generated entity code and I have just missed it.

Cheers Simon

I usually use Cascade Deletes in the database for all relations that can be deleted without any business implications. For other critical relations, I use your first approach, plus an option to direct the user to a master/detail page of the entity to be deleted, so he can see all critical relationships.

So it's basically very similar to approaches 3&4, except for having to visit the database first.

Cons: An extra Db visit (for the initial failed delete). Pros: For all deletes where there is no critical relationships in the database, it saves you multiple database queries trying to find the related entities.

For other critical relations, I use your first approach, plus an option to direct the user to a master/detail page of the entity to be deleted, so he can see all critical relationships.

I don't think our users would understand our master detail structure especially when the inheritance is factored in. Not sure even I do at the moment

What I want to do is (4) to get full control over which relationships to use but make it generic and reusable - a CheckIfDeletable(entity). I then need to create a custom query like this:-

DECLARE @p1 int; SET @p1 = 83

SELECT
    (SELECT COUNT(*) FROM AccountsTransaction WHERE LegalBodyID = LegalBody.ID) AS 'Invoices',
    (SELECT COUNT(*) FROM AssetToOwner WHERE LegalBodyID = LegalBody.ID) AS 'Assets Owned'
    .etc.
FROM LegalBody WHERE LegalBody.ID=@P1

where each 'column' is created from a IEntityRelation (and a custom string). This would allow me to detect when a delete is going to fail and I can bung the result into a grid to show the user exactly why in terms they can understand.

Any idea of how to convert an IEntityRelation into this type of subquery? (I'm going to have a bash now but any help appreciated)

How would you dynamically get the Custom String "Label"? How would this be figured out?

For a given Entity, I manually choose which relationships to use (including inherited ones) and a give each a friendly name.

The code below works for the two sample inherited relations from LegalBody but as soon as I uncomment the one for Person, I get an exception: "Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?" which I suspect is the old "entities are entities not individual tables" issue I have come across before.

Still, you get the idea - the DataTable will have columns made up of the appropriate Friendly names and I can fetch for just one entity or all so I can even find 'unused' Persons.

The idea is sound but I just get so confused converting metadata into SQL I can handwrite (this is the latest in many previous attempts).

    public class DeleteChecker
    {
        public static DeleteChecker ForPerson(int id)
        {
            return new DeleteChecker(new PersonEntity(id), LegalBodyRelations, PersonRelations);
        }

        static void LegalBodyRelations(DeleteChecker result)
        {
            result.AddRelation(LegalBodyEntity.Relations.AccountsTransactionEntityUsingLegalBodyID, "Invoices");
            result.AddRelation(LegalBodyEntity.Relations.AssetToOwnerEntityUsingLegalBodyID, "Assets Owned");
        }

        static void PersonRelations(DeleteChecker result)
        {
            //result.AddRelation(PersonEntity.Relations.LifeAssuredEntityUsingPersonID, "Life Assured on Policy");
        }

        readonly CommonEntityBase entity;
        readonly List<RelationInfo> relationInfos = new List<RelationInfo>();

        DeleteChecker(CommonEntityBase entity, params Action<DeleteChecker>[] sources)
        {
            this.entity = entity;

            foreach (var source in sources)
            {
                source(this);
            }
        }


        void AddRelation(IEntityRelation relation, string friendlyName)
        {
            relationInfos.Add(new RelationInfo { FriendlyName = friendlyName, Relation = relation });
        }

        class RelationInfo
        {
            public IEntityRelation Relation { get; set; }
            public string FriendlyName { get; set; }
        }

        public object BuildQuery()
        {
            var resultSetFields = new ResultsetFields(relationInfos.Count + 1);
            var bucket = new RelationPredicateBucket();

            resultSetFields.DefineField(entity.Fields.PrimaryKeyFields[0], 0, "ID");

            var groupByCollection = new GroupByCollection(resultSetFields[0]);

            for (var i = 0; i < relationInfos.Count; i++)
            {
                var resultSetInfo = relationInfos[i];
                var relation = resultSetInfo.Relation;

                resultSetFields.DefineField(relation.GetFKEntityFieldCore(0), i + 1, resultSetInfo.FriendlyName, AggregateFunction.Count);

                bucket.Relations.Add(relation, JoinHint.Left);
            }

            using (var adapter = new DataAccessAdapter())
            {
                var dataTable = new DataTable();
                adapter.FetchTypedList(resultSetFields, dataTable, bucket, 0, null, false, groupByCollection);
            }

            return null;
        }

The SQL produced is:-

SELECT DISTINCT
  [LPA_L1].[ID],
  COUNT([LPA_L2].[LegalBodyID]) AS [Invoices],
  COUNT([TIPS].[dbo].[AssetToOwner].[LegalBodyID]) AS [Assets Owned] 
FROM
  ((( [TIPS].[dbo].[LegalBody] [LPA_L1]  
LEFT JOIN
  [TIPS].[dbo].[AccountsTransaction] [LPA_L2] ON [LPA_L1].[ID] = [LPA_L2].[LegalBodyID]) 
LEFT JOIN
  [TIPS].[dbo].[AssetToOwner] ON [LPA_L1].[ID] = [TIPS].[dbo].[AssetToOwner].[LegalBodyID]) 
LEFT JOIN
  [TIPS].[dbo].[Person] [LPA_L3] ON [LPA_L1].[ID] = [LPA_L3].[ID]) 
WHERE
  ( ( ( [LPA_L3].[ID] IS NOT NULL))) 
GROUP BY
  [LPA_L1].[ID]

(a COUNT(*) would probably be better then I don't need to use a PK or FK field)

I think I have surprised myself and got a working solution :- (It needs some more testing of course but seems to be giving correct results so far)

        public DataTable GetInfo(int? targetID = null)
        {
            const string TargetAlias = "ID";
            const string TargetEntityAlias = "X";

            var resultSetFields = new ResultsetFields(relationInfos.Count + 1);
            var bucket = new RelationPredicateBucket();

            var primaryKeyField = entity.Fields.PrimaryKeyFields[0];

            resultSetFields.DefineField(primaryKeyField, 0, TargetAlias, TargetEntityAlias);

            var groupByCollection = new GroupByCollection(resultSetFields[0]);

            for (var i = 0; i < relationInfos.Count; i++)
            {
                var resultSetInfo = relationInfos[i];
                var relation = resultSetInfo.Relation;
                var aliasRelationEnd = "E" + i;

                resultSetFields.DefineField(relation.GetFKEntityFieldCore(0), i + 1, resultSetInfo.FriendlyName, aliasRelationEnd, AggregateFunction.Count);

                bucket.Relations.Add(relation, TargetEntityAlias, aliasRelationEnd, JoinHint.Left);
            }

            if (targetID != null)
            {
                bucket.PredicateExpression.Add(new FieldCompareValuePredicate(primaryKeyField, null, ComparisonOperator.Equal, targetID.Value));
            }

            var dataTable = new DataTable();

            using (var adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(resultSetFields, dataTable, bucket, 0, null, true, groupByCollection);
            }

            return dataTable;
        }

Great.

Walaa wrote:

Great.

Thanks for that enthusiasm Walaa.

I have tarted this up a bit and can now show in auto-generated dialog when a given entity cannot be deleted due to its relations, their types and counts. This helps a lot.

Request: Would it be possible in a future version to store UpdateRule/DeleteRule info in IEntityRelation?

PS I just noticed that I posted this in Helpdesk rather than Generated Code - feel free to move this if you think others might be interested.

Would it be possible in a future version to store UpdateRule/DeleteRule info in IEntityRelation?

I see it as a persistence info rather than entity/relation info.

Walaa wrote:

Would it be possible in a future version to store UpdateRule/DeleteRule info in IEntityRelation?

I see it as a persistence info rather than entity/relation info.

I agree it is info-only as far as generated entities are concerned, but it is set via the Relationship editor so that seems the obvious place to store it.

Just a question for maintenance: is the thread done or do you need further assistance? (I couldn't find a question in your last posts so I assume the thread is done?)

I made this suggestion:-

Request: Would it be possible in a future version to store UpdateRule/DeleteRule info in IEntityRelation?

Walaa suggested it was persistence info and not relation info.

I then suggested that whilst it was currently used only by the database generation bit, it could be useful as metadata in an app and thought it could go as read-only info on the IEntityRelation since there is a 1:1 between that and the Relationship Editor.

My use for it would be in the code listed earlier so I can differentiate a relationship that would prevent an entity being deleted and one that had a cascade delete in place and therefore wouldn't be fatal.

That info won't be part of IEntityRelation as it's part of the relational model and not part of entity model. I.e. you can have 1 entity model being mapped onto two different databases, one having different rules than the other on the FKs which are represented by the IEntityRelation. The rule data in the model in the designer is for model first development to produce rules on FK constraints and has no use in the entity model in our framework. This won't change in the future as it is not meant to be part of the entity model in any way.