locked
Composite Foreign Key RRS feed

  • Question

  • User1682745178 posted

    When I try to access any table that includes a composite foreign key, I get the error, "Assertion Failed: Abort=Quit, Retry=Debug, Ignore=Continue."  I noted an earlier blog note by David Ebbo in Dec 07 suggesting that composite foreign keys were broken and would be fixed, so I assume it's still broken, but I wanted to check for a workaround.

    A little more detail.  I'm developing a full-blown payroll app with Dynamic Data.  Until recently, I had all FK cols defined as Primary Key (Identity Cols), which worked fine, but when performing Adds, Edits and Inserts on given records, the drop down lists in dynamic data offered choices from the foreign key tables that may not be appropriate for that record.  For example, if I'm adding an earnings record for Company 1 (CompaniesID) which contains a FK PayCode field, and the PayCode table also contains a CompaniesID col, I don't want the user to be able to select a paycode from Company 2.  So I create a composite FK consisting of CompaniesID and PayCodesID.  Then Dynamic Data blows up on both the Earnings table and the PayCodes table.

    I've seen some really great posts by Stephen Naughton, but if I address all these FK cases with code (as opposed to within Sql Server), it could become a really daunting task.

    Thanks for the help.  I really appreciate it...

     

    Tuesday, August 5, 2008 3:10 PM

Answers

  • User-330204900 posted

    Hi Greg have look a this post on my blog A Second Advanced FieldTemplate. I'm not sure if its what you want but it may be a start.

    Hope this helps [:D]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 7, 2008 5:29 PM
  • User1682745178 posted

    Hi Steve,

    Thanks...!  Actually I'm reviewing it right now, and your work has been a great help.  I'm going back and forth between your work and ForeignKey_Edit (the latest futures version).  I'm probably making this too difficult.  Basically, I want to tell a given FK column in the record who it's parent is (just another column in the record) and filter the FK column based on the value of the parent.  Basically, I'm trying to replace the PopulateListControl in ForeignKey_Edit using your work as a guide.

    Thanks again for all your submissions.  They've been a great help.

    Greg

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 7, 2008 5:50 PM

All replies

  • User1641955678 posted

    The Dec 07 comment was with a much earlier build, so you can ignore it.  Dynamic Data works fine with composite FKs, but does require one end of the FK to be the PK of the parent table.  e.g. in this case it would mean that your PayCode table's PK is made up of both PayCodesID and CompaniesID, not just PayCodesID.

    While this may work, I'm not sure that this is necessarily the right answer for you.  If I understand your schema correctly, what I find unusual about it is that it stores the Company_ID redundantly: the earning record has it, and then the earning record's paycode has it as well, opening the door for them to be different.

    Instead, would it be conceivable to remove CompanyID from the Earning Record, and have it be implied by selected PayCode?  Of course, that would mean that you only get one drop down for Company/PayCode, while you may prefer to first select the company and then select the valid pay codes within it?

    David

    Tuesday, August 5, 2008 4:20 PM
  • User1682745178 posted

    David,

    Thank you so much for the prompt reply.  I understand the requirement for the FK to be the PK of the parent table; I'll try that immediately.

    As to the schema, I understand your suggestion about the redundancy.  I would prefer for the user to first select the Company and then select the valid Pay Codes within it, but I'm more concerned about setting up the Schema correctly (I'm having to teach myself DBA skills), so I'll consider your suggestion for removing the CompanyID from the Earnings Record.  I know this isn't a forum for Sql Server basics.

    Thanks again, David. 

     

     

    Tuesday, August 5, 2008 5:09 PM
  • User1682745178 posted

    David,

    As you suggested, I changed the PK on the PayCodes table from PayCodesID to a composite PK with PayCodesID/CompaniesID and reestablished the FK on the EarningsMiscWrk table as the new composite key.  Also, as you suggested, Dynamic Data handled the composite FK just fine, but when I edit a record for the EarningsMiscWrk table, the PayCodesID field still offers PayCodes from all Companies even though a specific company has already been selected for the record.  However, if I select a PayCodesID associated with a different Company, the Company itself will change in the EarningsMiscWrk table (obviously maintaining integrity).

    I need the choices for the PayCodesID field/FK to be pre-filtered by the Company selection.  I'm reviewing Stephen Naughton's example on the CascadingFilter from Dynamic Data Futures project, but I'm not sure if this is exactly what I need.

    Thanks,

    Greg 

     

    Tuesday, August 5, 2008 11:50 PM
  • User1641955678 posted

    Hi Greg,

    Sorry for the delayed response.  I guess the behavior that you describe here is what is expected given how Dynamic Data works.  By default, when you edit/insert a row, there is no interaction between the values of different columns.  But here, what you want is that the selection of the Company causes the Paycode list to get filtered.  For this to happen, there needs to be a server postback at the time the Company is selected.  Also, what would you want the Paycode drop down to display *before* you select a company?  Presumably it should be grayed out?

    Though this is not supported out of the box, I think it can be done with some additional logic,  e.g.

    • Create a new attribute that can be placed on the EarningRecord's PayCode column, telling it that the Paycode's Company needs to be filtered EarningRecord's Company.
    • Add logic to ForeignKey_Edit to look for this attribute.  When found, it can look for the value of the 'driver' field (here the Company), and populate the drop down accordingly.  To do this, it cannot call PopulateListControl (which doesn't support such filtering), but must instead use alternate logic to populate the drop down.  Also, it can choose to disable the drop down if the 'driver' field is not set.

    If I get some cycles in the next few days I'll to put together a proof of concept, which maybe can be added to our 'Futures' solution.

    David

     

    Thursday, August 7, 2008 2:28 AM
  • User1682745178 posted

    David,

    Thank you so much for the response; you've already been more gracious with your time than I could've hoped for.  In either case, I understand your remarks about the additional logic.  I've been reviewing a similar post by Marcind, "Replacing PopulateListControl with a custom (but still dynamic) query" - http://forums.asp.net/p/1281377/2450473.aspx  It appears that the requestor, rmdaustin, has a similar need.  Marcind references several files including the LinqExpressionHelper class, the cascading drop-down filter control, and the autocomplete filter service.  It appears that the cascading drop-down filter code is similar to the code you're suggesting.

    Yes, I would want the child (PayCode) drop down to be grayed-out on an Insert until after the value for the CompanyCode was set (unless a default value on insert had already been set).  On an Edit, I would prefer the child drop down to be available (not grayed out), but to be pre-filtered by the existing value for the parent/driver (CompanyCode).  If the value for the parent changes (as an edit), then the child (or children) should be automatically re-filtered.  When I say "children," I'm thinking that you could potentially create a one (parent) to many (child) relation by decorating one or more child fields/cols (obviously FK cols) with the name of a parent field/col (not necessarily an FK col).  The parent field would reference an identical field in the child FK table (though (hopefully) not necessarily part of the PK of the child FK table).  I would assume that since the parent name(s) would be a parameter to a customized FieldTemplate, a given record could support multiple parent/child "sets."

    Presumably one could extend this logic to include a "many to many" relationship in which a given child column had multiple parent cols or even extend the logic to include cascading behavior in which a parent was also a child - but I would be all too happy to have the baseline (one-to-many) case work.

    Again, David, thanks for the consideration.  Given that I'm new to C# and newer to DynamicData, customizing a FieldTemplate is a little beyond me, but I'll take a cut at it.

    Greg

     

    Thursday, August 7, 2008 3:44 PM
  • User-330204900 posted

    Hi Greg have look a this post on my blog A Second Advanced FieldTemplate. I'm not sure if its what you want but it may be a start.

    Hope this helps [:D]

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 7, 2008 5:29 PM
  • User1682745178 posted

    Hi Steve,

    Thanks...!  Actually I'm reviewing it right now, and your work has been a great help.  I'm going back and forth between your work and ForeignKey_Edit (the latest futures version).  I'm probably making this too difficult.  Basically, I want to tell a given FK column in the record who it's parent is (just another column in the record) and filter the FK column based on the value of the parent.  Basically, I'm trying to replace the PopulateListControl in ForeignKey_Edit using your work as a guide.

    Thanks again for all your submissions.  They've been a great help.

    Greg

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 7, 2008 5:50 PM
  • User-330204900 posted

    Thanks...!  Actually I'm reviewing it right now, and your work has been a great help.  I'm going back and forth between your work and ForeignKey_Edit (the latest futures version).  I'm probably making this too difficult.  Basically, I want to tell a given FK column in the record who it's parent is (just another column in the record) and filter the FK column based on the value of the parent.  Basically, I'm trying to replace the PopulateListControl in ForeignKey_Edit using your work as a guide.

    Hi Greg, how are you getting on have developed an answer?

    Monday, August 11, 2008 6:01 AM
  • User1682745178 posted

    Steve,

    I've got it working at a pretty basic level (thanks to help from your code).  I have a few more revisions to make and I'll post it...

    Thanks.

    Greg 

     

    Monday, August 11, 2008 10:57 AM
  • User1682745178 posted

    Steve,

    As indicated previously, I borrowed heavily from your code (thanks).  I'm sure that my portions of the code could be buggy due to my inexperience, but it seems to be working at a basic level.  I'm simply filtering values in FK tables by a specified (by attribute) session variable.  The field being filtered in the FK table(s) is also specified by attribute.  The field name in the FK table(s) is also identically named to a column/field in the dynamicdata record being edited.  So, initially, I'm filtering from the session variable, but if the "parent" column changes, then I refilter by the new parent column value.

    using System;
    using System.Collections.Specialized;
    using System.Diagnostics;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.DynamicData;
    using Microsoft.Web.DynamicData;
    
    
    public partial class ForeignKey_Filter : FieldTemplateUserControl {
    
        private MetaTable filterTable;
        private string filterSessName;
        private string filterParentColumn;
        private MetaColumn parentColumn;
        private object context;
    
        protected void Page_Init(object sender, EventArgs e)
        {
            // Session Attribute
            var filterSessNameAttribute = this.Column.Attributes.OfType<FILTERSESSNAMEATTRIBUTE>().FirstOrDefault();
            if (filterSessNameAttribute == null) {
                throw new InvalidOperationException("Was expecting a FilterSessNameAttribute.");
            }
    
            filterSessName = filterSessNameAttribute.FilterSessName;
    
            // Parent Column Attribute
            var filterParentColumnAttribute = this.Column.Attributes.OfType<FILTERPARENTCOLUMNATTRIBUTE>().FirstOrDefault();
            if (filterParentColumnAttribute == null)
            {
                throw new InvalidOperationException("Was expecting a FilterParentColumnAttribute.");
            }
    
            filterParentColumn = filterParentColumnAttribute.FilterParentColumn;
            parentColumn = this.Table.GetColumn(filterParentColumn);
    
            // Parent Column table
            filterTable = ((MetaForeignKeyColumn)Column).ParentTable;
            context = filterTable.CreateContext();
    
        }
    
        protected void Page_Load(object sender, EventArgs e) {
            if (filterDropDown.Items.Count == 0) {
                if (Mode == DataBoundControlMode.Insert || !Column.IsRequired) {
                    filterDropDown.Items.Add(new ListItem("[Not Set]", ""));
                }
                
                // Session Variable Attribute
                var sessionType = Session.Contents[filterSessName].GetType();
                var sessionValue = Convert.ChangeType((Session[filterSessName]), sessionType);
    
                var filterItems = GetChildListFilteredByParent(sessionValue);
    
                foreach (var row in filterItems) {
                    filterDropDown.Items.Add(new ListItem(filterTable.GetDisplayString(row), filterTable.GetPrimaryKeyString(row)));
                }
            }
    
            SetUpValidator(DynamicValidator1);
            SetUpValidator(RequiredFieldValidator1);
            DynamicDataFutures.SetUpValidator(Column, RequiredFieldValidator1);
        }
    
        protected override void OnDataBinding(EventArgs e) {
            base.OnDataBinding(e);
    
            if (Mode == DataBoundControlMode.Edit) {
                filterDropDown.Enabled = true;
                filterDropDown.Items.Clear();
    
                var parentColumnValue = GetColumnValue(parentColumn);
                var filterItems = GetChildListFilteredByParent(parentColumnValue);
    
                foreach (var row in filterItems) {
                    filterDropDown.Items.Add(new ListItem(filterTable.GetDisplayString(row), filterTable.GetPrimaryKeyString(row)));
                }
            }
    
            if (Mode != DataBoundControlMode.Insert) {
                string foreignkey = ForeignKeyColumn.GetForeignKeyString(Row);
                ListItem item = filterDropDown.Items.FindByValue(foreignkey);
                if (item != null)
                {
                    filterDropDown.SelectedValue = foreignkey;
                }
            }
        }
    
        protected override void ExtractValues(IOrderedDictionary dictionary)
        {
            // If it's an empty string, change it to null
            string val = filterDropDown.SelectedValue;
            if (val == String.Empty)
                val = null;
    
            ExtractForeignKey(dictionary, val);
        }
    
        public override Control DataControl {
            get {
                return filterDropDown;
            }
        }
    
        private IQueryable GetChildListFilteredByParent(object parentColumnValue)
        //private IQueryable GetChildListFilteredByParent(object selectedParent)
        {
            var query = filterTable.GetQuery(context);
            // this make more sense as the parameter now has the table name (filteredTable.Name)
            // note the change from "product" to filterTable.Name
            var parameter = Expression.Parameter(filterTable.EntityType, filterTable.Name);
            // product.Category
            var property = Expression.Property(parameter, filterParentColumn);
            // selectedCategory
            var constant = Expression.Constant(parentColumnValue);
            // product.Category == selectedCategory
            var predicate = Expression.Equal(property, constant);
            // product => product.Category == selectedCategory
            var lambda = Expression.Lambda(predicate, parameter);
            // Products.Where(product => product.Category == selectedCategory)
            var whereCall = Expression.Call(typeof(Queryable), "Where", new Type[] { filterTable.EntityType }, query.Expression, lambda);
    
    
            //================================== Order by ================================
            if (filterTable.SortColumn != null)
            {
                // this make more sense as the parameter now has the table name (filteredTable.Name)
                // table.sortColumn
                var sortProperty = Expression.Property(parameter, filterTable.SortColumn.Name);
    
                // Column => Column.SortColumn
                var orderByLambda = Expression.Lambda(sortProperty, parameter);
    
                //.OrderBy(Column => Column.SortColumn)
                MethodCallExpression orderByCall = Expression.Call(
                    typeof(Queryable),
                    "OrderBy",
                    new Type[] { filterTable.EntityType, filterTable.SortColumn.ColumnType },
                    whereCall,
                    orderByLambda);
    
                //{
                //Table(Product).
                //Where(Products => (Products.Category = value(Category))).
                //OrderBy(Products => Products.ProductName)
                //}
                return query.Provider.CreateQuery(orderByCall);
            }//================================== Order by ================================
            else
            {
                return query.Provider.CreateQuery(whereCall);
            }
        }
    
    }
     
    Monday, August 11, 2008 7:04 PM