Language preference:

ForeignKeyQueryAttribute

The user interface provides a Field Template to edit foreign key DataFields. That editor is usually a list of records from the table associated with the foreign key. For example, when editing a Product, its Categories DataField shows a list of Categories. You may not want all records displayed, or you want a different sort order.

The ForeignKeyQueryAttribute lets you define the query. In fact, you can add several ForeignKeyQueryAttributes to offer different queries, each with a unique name from which the user interface can choose.

It can also offer a DataField name whose value to display in the list.

Here are ForeignKeyQueryAttributes associated with the Product Entity class. The Category property has 3 ForeignKeyQueryAttributes. The first, with QueryName="", is selected by default. (This can be overridden within the ICustomizeDataField interface.)

public class ProductMetadata
{
   [DESDA.ForeignKeyQuery(QueryName="", DisplayDataFields="CompanyName|ContactName", OrderBy="CompanyName")]
   public object Supplier { get; set; }

   [DESDA.ForeignKeyQuery(QueryName="", Where="", OrderBy="CategoryName")]
   [DESDA.ForeignKeyQuery(QueryName="Desc", Where="", OrderBy="CategoryName desc")]
   [DESDA.ForeignKeyQuery(QueryName="Condiments and Beverages", 
      Where="(CategoryName == \"Condiments\") or (CategoryName == \"Beverages\")", OrderBy="CategoryName")]
   public object Category { get; set; }
}

The Source Code Browser shows completed DataAnnotations. The ForeignKeyQueryAttributes have been highlighted.



Open the Source Code Browser (C# only)
Source Code Browser
 
/* ------------------------------------------------
 * Describes the Entity class for: Product
 * Classes:
 *    Product - Entity class. Edit it for validation and to customize metadata at runtime
 *    ProductMetadata - Entity Metadata class. It contains the DataAnnotations.
 *    ProductDAO - BLD DataAccessObject format version of a Data Access Object.
 *    
 * Requires .net 4.0 and these references:
 *    System.ComponentModel.DataAnnotations
 *    PeterBlum.DES
 *    PeterBlum.DES.DataAnnotations
 * Generated: 7/8/2011 4:19:57 PM
 * ------------------------------------------------*/

using System;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Collections.Generic;
using PeterBlum.DES.DAO.Attributes;
using PeterBlum.DES.DAO.EntityDAO;
using PeterBlum.DES.DAO.EntityFilters;
using PeterBlum.DES.DAO;
// Some members of this namespace have identical names to those in System.ComponentModel.DataAnnotations
// making it easy to switch from one to the other by adding the "DESDA." prefix
using DESDA = PeterBlum.DES.DataAnnotations;

namespace PeterBlum.WithDataAnnotations
{
   // --- ENTITY CLASS --------------------------------------------------
   /// <summary>
   /// Entity class. Edit it for validation and to customize metadata at runtime
   /// </summary>
   [EntityDAOType(typeof(ProductDAO))]
   [MetadataType(typeof(ProductMetadata))]
   public partial class Product : DESDA.ICustomizeDataField
   {
      [DESDA.CurrencyDataType()]
      [DESDA.DisplayName("Value of Stock")]
      [DESDA.ScaffoldColumn(100, true)]
      [DESDA.CalculationDescriptor("if (Not Discontinued) then {UnitPrice * UnitsInStock} else novalue")]
      [DESDA.ColumnRestriction("Customer", DESDA.DenyAccess.All)]
      [DESDA.SortableColumn(SortExpression="UnitPrice * UnitsInStock", Sortable=true)]
      public decimal ValueOfStock
      {
         get 
         { 
            if (!Discontinued)
               if (UnitPrice.HasValue && UnitsInStock.HasValue)
                  return UnitPrice.Value * UnitsInStock.Value;
            return 0.0M;
         }
      }

      [DESDA.CurrencyDataType()]
      [DESDA.CalculationDescriptor("if (!Discontinued) then {UnitPrice * (UnitsInStock + UnitsOnOrder)} else novalue")]
      [DESDA.ScaffoldColumn(101, true)]
      [DESDA.DisplayName("Value of Available Units", ShortDisplayName="Val avail.")]
      [DESDA.ColumnRestriction("Customer", DESDA.DenyAccess.All)]
      public decimal ValueOfAvailableUnits
      {
         get
         {
            if (!Discontinued)
            {
               int vStock = 0;
               if (UnitsInStock.HasValue)
                  vStock = UnitsInStock.Value;
               if (UnitsOnOrder.HasValue)
                  vStock = vStock + UnitsOnOrder.Value;

               if (UnitPrice.HasValue)
                  return UnitPrice.Value * vStock;
            }
            return 0.0M;
         }
      }


/// <summary>
/// Expose a property of the nested Supplier object
/// </summary>
      [AttributeSource(typeof(Supplier), "HomePage")]
      [DESDA.ScaffoldColumn(AfterThisDataField="Supplier")]
      public string SupplierHomePage
      {
         get
         {
            if (Supplier != null)
               return Supplier.HomePage;
            return String.Empty;
         }
      }

      #region ICustomizeDataField Members
      /// <summary>
      /// Modify the business logic to account for runtime situations.
      /// Always consider pModifiableColumn.StaticColumn as a READ ONLY property.
      /// </summary>
      /// <param name="pModifiableColumn"></param>
      public void CustomizeDataField(DESDA.ActiveDataField activeDataField)
      {
         switch (activeDataField.DataField)
         {
            case "Category":
               activeDataField.SelectForeignKeyQueryAttribute("Desc", false);
               break;
         }  // switch

      }

      #endregion
   }  // class Product

   // --- ENTITY METADATA --------------------------------------------------
   /// <summary>
   /// Entity Metadata class.
   /// Companion to the Product Entity class that contains the DataAnnotations
   /// on properties with the same names as those in the actual Entity class.
   /// These properties do not require their types to match those in the Entity class.
   /// An Entity Metadata class allows the Entity class to be regenerated without
   /// overwriting DataAnnotations.
   /// </summary>
   [DESDA.TableRestriction("Admin", DESDA.DenyAccess.None)]
   [DESDA.TableRestriction("Customer", DESDA.DenyAccess.Edit | DESDA.DenyAccess.Delete | DESDA.DenyAccess.Insert)]
   public class ProductMetadata
   {
      [DESDA.DisplayName("Product ID", ShortDisplayName="ID")]
      public object ProductID { get; set; }

      [DESDA.CharacterSet(LettersLowercase=true, LettersUppercase=true, Digits=true, 
         Space=true, DiacriticLetters=true, OtherCharacters="'",
         ErrorMessage="Illegal character", SummaryErrorMessage="{LABEL} has an illegal character.")]
      [DESDA.DisplayName("Product Name", ShortDisplayName="Name")]
      [DESDA.Description("The product name.", Prompt="Enter the product name:")]
      [DESDA.Filter(AutoGeneratePriority=DESDA.AutoGeneratePriority.Always)]
      public object ProductName { get; set; }
  
      [DESDA.DisplayName("Quantity Per Unit")]
      [DESDA.Filter(AutoGeneratePriority=DESDA.AutoGeneratePriority.Advanced)]
      [DESDA.ScaffoldColumn("UnitPrice")] // positioned after UnitPrice when scaffolding
      [DESDA.SortableColumn(false)]
      public object QuantityPerUnit { get; set; }

      [DESDA.CurrencyDataType(MaxWholeDigits=4, UseCurrencySymbol=true, AllowNegatives=false)]
      [DESDA.Description("The price of one unit", Prompt="Enter the price for one unit")]
      [DESDA.DisplayName("Unit Price")]
      [DESDA.Filter(DataFormatString="c")]
      [DESDA.RangeEntityFilterPicker(true, 0, 10, 20, 100)]
      [DESDA.CompareBooleanDependency(DataField="Discontinued", ValueToCompare=false)]
      public object UnitPrice { get; set; }

      [DESDA.IntegerDataType(MaxWholeDigits=3, AllowNegatives=false)]
      [DESDA.CompareToValue(500, PeterBlum.DES.ConditionOperator.LessThan)]
      [DefaultValue(10)]
      [DESDA.SortableColumn(false)]
      [DESDA.DisplayName("Units In Stock")]
      [DESDA.Filter(AutoGeneratePriority=DESDA.AutoGeneratePriority.Advanced)]
      [DESDA.RangeEntityFilterPicker(false, 0, 9, 10, 49, 50, 99, 100)]
      [DESDA.ColumnRestriction("Customer", DESDA.DenyAccess.View)]
      public object UnitsInStock { get; set; }

      [DESDA.IntegerDataType(MaxWholeDigits=3, AllowNegatives=false)]
      [DESDA.DisplayName("Units On Order")]
      [DESDA.Range(0, 199)]
      [DESDA.Filter(AutoGeneratePriority=DESDA.AutoGeneratePriority.Advanced)]
      [DESDA.RangeEntityFilterPicker(false, 0, 9, 10, 49, 50, 99, 100)]
      [DESDA.ColumnRestriction("Customer", DESDA.DenyAccess.View)]
      public object UnitsOnOrder { get; set; }

      [DESDA.Enumerated(typeof(ReorderLevelEnum), ValueNameList="10=Medium Low|50=Medium High")]
      [DESDA.DisplayName("Reorder Level")]
      [DESDA.Filter(AutoGeneratePriority=DESDA.AutoGeneratePriority.Advanced)]
      [DESDA.ColumnRestriction("Customer", DESDA.DenyAccess.View)]
      public object ReorderLevel { get; set; }

      [DESDA.Filter(AutoGeneratePriority=DESDA.AutoGeneratePriority.Advanced)]
      [DESDA.ForeignKeyQuery(QueryName="", DisplayDataFields="CompanyName|ContactName", OrderBy="CompanyName")]
      public object Supplier { get; set; }

/// <summary>
/// Each ForeignKeyQuery describes one way to query the Category table
/// that the UI may use. The Product.CustomizeDataField method identifies
/// the default query in ActiveDataField.SelectForeignKeyQueryAttribute.
/// ForeignKey FieldTemplates offer a QueryName property where
/// you can override this default.
/// </summary>
      [DESDA.Filter(AutoGeneratePriority=DESDA.AutoGeneratePriority.Advanced)]
      [DefaultValue(1)]  // = ID of Beverages
      [DESDA.ForeignKeyQuery(QueryName="", Where="", OrderBy="CategoryName")]
      [DESDA.ForeignKeyQuery(QueryName="Desc", Where="", OrderBy="CategoryName desc")]
      [DESDA.ForeignKeyQuery(QueryName="Condiments and Beverages", 
         Where="(CategoryName == \"Condiments\") or (CategoryName == \"Beverages\")", OrderBy="CategoryName")]
      public object Category { get; set; }

/// <summary>
/// The RequiredAttribute is only enabled when the ZeroUnitsInStock dependency
/// determines that the UnitsInStock is 0.
/// The Dependency called "Strict" is available for the UI to enforce
/// stricter rules, where its required if QuantityPerUnit is empty
/// or UnitsInStock are 0. A Field Template must set its DependencyName property
/// to "Strict" to use this dependency.
/// </summary>
      [DESDA.CompareToValueDependency(Name="ZeroUnitsInStock", DataField="UnitsInStock",
         Operator=PeterBlum.DES.ConditionOperator.Equal, ValueToCompareAsNative=0)]
      [DESDA.RequiredDependency(Name="QtyPerUnitEmpty", DataFields="QuantityPerUnit", NotCondition=true )]
      [DESDA.MultiConditionDependency(Name="Strict", 
        Expression="ZeroUnitsInStock OR QtyPerUnitEmpty")]
      [DESDA.Required(DependencyName="ZeroUnitsInStock")]
      [DefaultValue(false)]
      public object Discontinued { get; set; }

      [DESDA.ColumnRestriction("Customer", DESDA.DenyAccess.All)]
      public object Order_Details { get; set; }

   }  // class ProductMetadata

/// <summary>
/// Used by the ReorderLevel property to establish fixed integer values
/// that can be assigned by the user.
/// </summary>
   public enum ReorderLevelEnum
   {
      None = 0,
      Low = 5,
      MediumLow = 10,
      Medium = 30,
      MediumHigh = 50,
      High = 100
   }

   // --- BLD DATAACCESSOBJECT  --------------------------------------------------
   /// <summary>
   /// BLD DataAccessObject class for the Product Entity class.
   /// It provides CRUD actions. The parent class already has default
   /// methods for Update(), Insert(), Delete() and several queries.
   /// </summary>
   /// <remarks>
   /// <para>For documentation, see the BLD DataAccessObject section of the Business Logic User's Guide.</para>
   /// </remarks>
   [TableName("Products")]
   public class ProductDAO : LINQtoSQLEntityDAO<Product>
   {
      public ProductDAO() : base(typeof(NorthWindDataContext)) { }
      public ProductDAO(object pDataContext) : base(pDataContext) { }

/// <summary>
/// Returns products between a price range. Supports filters
/// and sorting passed in through the SelectArgs.
/// Uses EntityFilters to add a filter to the query. 
/// (There are many ways to adjust the query, such as using LINQ
/// and the PeterBlum.DES.DAO.EntityDAO.WhereClause class which
/// lets you construct a SQL-like statement.)
/// </summary>
/// <param name="startPrice"></param>
/// <param name="endPrice"></param>
/// <param name="selectArgs"></param>
/// <returns></returns>
      [DataObjectMethod(DataObjectMethodType.Select, false)]
      [SelectMethodParameter(ActualName="startPrice", AliasNames="StartPrice")]
      [SelectMethodParameter(ActualName="endPrice", AliasNames="EndPrice")]
      [Description("All Products whose UnitPrice is within these parameters.")]
      [SelectArgs(Filters=true, SortExpression=true, Paging=true)]
      public IEnumerable<Product> SelectPriceRange(
        decimal startPrice, decimal endPrice, SelectArgs selectArgs)
      {
        if (endPrice == 0)
           endPrice = 999999999.99M;

        selectArgs.EntityFilters.Add(
          new RangeEntityFilter("UnitPrice", startPrice, endPrice));
 
        return ExecuteSelectCommand(selectArgs);
      }

/// <summary>
/// Uses a stored procedure called Ten Most Expensive Products that has
/// already been added to the NorthWindDataContext class as a method
/// named Ten_Most_Expensive_Products(). It takes no parameters.
/// </summary>
/// <param name="selectArgs"></param>
/// <returns></returns>
      [DataObjectMethod(DataObjectMethodType.Select, false)]
      [SelectMethodParameter(ActualName="startPrice", AliasNames="StartPrice")]
      [SelectMethodParameter(ActualName="endPrice", AliasNames="EndPrice")]
      [Description("All Products whose UnitPrice is within these parameters.")]
      [SelectArgs(Filters=true, SortExpression=true, Paging=true)]
      public IEnumerable<Product> TopTen(SelectArgs selectArgs)
      {
         NorthWindDataContext vDataContext = new NorthWindDataContext();
         var vProducts = vDataContext.Ten_Most_Expensive_Products();
        return ExecuteSelectCommand(vProducts, selectArgs, "TenMostExpensive");
      }
   }  // class ProductDAO

}