Home/ASP.NET Forums/General ASP.NET/Data Scaffolding using ASP.NET Dynamic Data/Filtering foreign key drop down list question.

Filtering foreign key drop down list question. [Answered]RSS

38 replies

Last post Dec 06, 2010 11:54 AM by PeterSkellern

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Nov 17, 2010 07:16 AM|sjnaughton|LINK

    Hi Peter, are you usin g VS2008 or VS2010? 

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Nov 17, 2010 11:34 AM|PeterSkellern|LINK

     Thank you. I am currently using Visual Studio 2010 Ultimate

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Nov 17, 2010 12:57 PM|sjnaughton|LINK

    Hi Peter, if yu need to pre filter all data, I would look at WCF RIA Service see David Ebbo's session a MIX09 He dose a Domain Service Demo about half way through, you can get WCF RIA Services with DD templates here WCF RIA Services

    Domain Service Dynamic Data 4 WCF RIA Services

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Nov 18, 2010 01:57 PM|PeterSkellern|LINK

    Thank you. I saw the video and it is really complicated. For now I'd rather stay were I am.

    How can I make prefiltering inside your hierarchical controls? Knowing that will be really helpful

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Nov 18, 2010 03:34 PM|sjnaughton|LINK

    The Heirachical filters and field template already filter themselves I don't think there would be a generic way, I would definatly go for the WCF RIA Services as once you get the hang of it life will become a lot easier. WCF RIA Service is certanly simpler the  Expressions.

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Nov 19, 2010 09:22 PM|PeterSkellern|LINK

    Thank you. In my case and despite of all other considerations, the code provided in reply #7 (entry number 8) of this post is enough to solve the problem of prefiltering because the same condition will apply for all entities in my model. Let's see the code:

  • public new void PopulateListControl(ListControl listControl)   
  • {   
  •     const string STR_CategoryId = "CategoryId";   
  •   
  •     var uiHint = MetadataAttributes.OfType<UIHintAttribute>().FirstOrDefault();   
  •   
  •     if (uiHint.ControlParameters.Count == 0 || !uiHint.ControlParameters.Keys.Contains(STR_CategoryId))   
  •         throw new InvalidOperationException(String.Format("Parameter field is missing from UIHint on {0}", Column.Name));   
  •   
  •     var categoryId = (int)uiHint.ControlParameters[STR_CategoryId];   
  •   
  •     var OC = new Models.NorthwindDataContext();   
  •     var values = from p in OC.Products                    /// This line could be pointing to any table not just Products
  •                  where p.CategoryID == categoryId   
  •                  select p;   
  •   
  •     foreach (var p in values)   
  •     {   
  •         listControl.Items.Add(new ListItem()   
  •         {   
  •             Text = p.ProductName,   
  •             Value = p.ProductID.ToString()   
  •         });   
  •     }   
  • The Linq sentence in which I have added a commnet accomplishes what I want with the exception that the code has been written specifically for the Products table. If we had that line of code to be valid for any table the prefiltering would be done correctly.

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Nov 20, 2010 06:04 AM|sjnaughton|LINK

    OK Peter, I think I can sort that for you can you send me a couple of sample tables so I can test please?

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Nov 20, 2010 08:02 AM|PeterSkellern|LINK

    Thank you. Here is the basic database structure you asked for

    Create table [company]
    (
     [company_number] Numeric(8,0) Identity NOT NULL,
     [company_name] Varchar(40) NULL, UNIQUE ([company_name]),
     [registration_number] Varchar(14) NULL, UNIQUE ([registration_number]),
     [main_activity] Varchar(120) NULL
    )
    go

    Create table [warehouse]
    (
     [warehouse_number] Numeric(4,0) Identity NOT NULL,
     [container_type] Varchar(1) NOT NULL,
     [port_number] Varchar(1) NOT NULL,
     [company_number] Numeric(8,0) NOT NULL,
     [main_location] Varchar(1) NOT NULL,
     [download_type] Numeric(2,0) NOT NULL,
     [warehouse_name] Varchar(1) NULL, UNIQUE ([warehouse_name]),
     [storage_capacity] Varchar(2) NULL,
     [number_of_racks] Numeric(3,0) NULL
    )
    go

    Create table [patio]
    (
     [patio_number] Numeric(3,0) Identity NOT NULL,
     [warehouse_number] Numeric(4,0) NOT NULL,
     [style_number] Numeric(18,0) NOT NULL
    )
    go

    Create table [furniture]
    (
     [furniture_number] Numeric(8,0) Identity NOT NULL,
     [patio_number] Numeric(3,0) NOT NULL,
     [glazed_folding] Binary(1) NULL,
     [wood_folding] Binary(1) NULL,
     [aluminum_clad_folding] Binary(1) NULL,
     [sliding_type] Varchar(2) NULL,
     [accessories] Binary(1) NULL,
     [complementary_info] Varchar(120) NULL
    )
    go

    Create table [style]
    (
     [style_number] Numeric(18,0) Identity NOT NULL,
     [style_name] Varchar(60) NULL, UNIQUE ([style_name])
    )
    go

    Create table [location]
    (
     [main_location] Varchar(1) Identity NOT NULL,
     [location_name] Varchar(40) NULL, UNIQUE ([location_name])
    )
    go

    Create table [downloader]
    (
     [download_type] Numeric(2,0) Identity NOT NULL,
     [download_name] Varchar(40) NULL, UNIQUE ([download_name])
    )
    go

    Create table [container]
    (
     [container_type] Varchar(1) Identity NOT NULL,
     [container_name] Varchar(60) NULL, UNIQUE ([container_name])
    )
    go

    Create table [port]
    (
     [country_number] Varchar(1) NOT NULL,
     [port_number] Varchar(1) Identity NOT NULL,
     [port_name] Varchar(120) NULL
    )
    go

    Create table [country]
    (
     [country_number] Varchar(1) Identity NOT NULL,
     [country_name] Varchar(80) NULL, UNIQUE ([country_name])
    )
    go


    Alter table [company] add Constraint [pk_company] Primary Key ([company_number])
    go
    Alter table [warehouse] add Constraint [pk_warehouse] Primary Key ([warehouse_number])
    go
    Alter table [patio] add Constraint [pk_patio] Primary Key ([patio_number])
    go
    Alter table [furniture] add Constraint [pk_furniture] Primary Key ([furniture_number])
    go
    Alter table [style] add Constraint [pk_style] Primary Key ([style_number])
    go
    Alter table [location] add Constraint [pk_location] Primary Key ([main_location])
    go
    Alter table [downloader] add Constraint [pk_downloader] Primary Key ([download_type])
    go
    Alter table [container] add Constraint [pk_container] Primary Key ([container_type])
    go
    Alter table [port] add Constraint [pk_port] Primary Key ([port_number])
    go
    Alter table [country] add Constraint [pk_country] Primary Key ([country_number])
    go


    Alter table [warehouse] add Constraint [FK_Company_Patio] foreign key([company_number]) references [company] ([company_number])  on update no action on delete no action
    go
    Alter table [patio] add Constraint [FK_Warehouse_Patio] foreign key([warehouse_number]) references [warehouse] ([warehouse_number])  on update cascade on delete cascade
    go
    Alter table [furniture] add Constraint [FK_Patio_Furniture] foreign key([patio_number]) references [patio] ([patio_number])  on update no action on delete no action
    go
    Alter table [patio] add Constraint [FK_Style_Patio] foreign key([style_number]) references [style] ([style_number])  on update no action on delete no action
    go
    Alter table [warehouse] add Constraint [FK_Location_Warehouse] foreign key([main_location]) references [location] ([main_location])  on update no action on delete no action
    go
    Alter table [warehouse] add Constraint [FK_Downloader_Warehouse] foreign key([download_type]) references [downloader] ([download_type])  on update no action on delete no action
    go
    Alter table [warehouse] add Constraint [FK_Container_Warehouse] foreign key([container_type]) references [container] ([container_type])  on update no action on delete no action
    go
    Alter table [warehouse] add Constraint [FK_port_Warehouse] foreign key([port_number]) references [port] ([port_number])  on update no action on delete no action
    go
    Alter table [port] add Constraint [FK_Country_Port] foreign key([country_number]) references [country] ([country_number])  on update no action on delete no action
    go


    Set quoted_identifier on
    go


    /* Update trigger "tu_warehouse" for table "warehouse" */
    Create trigger [tu_warehouse]
    on [warehouse] after update as
    begin 
      
     declare @numrows int
     select @numrows = @@rowcount
     if @numrows = 0
      return
     
     
     /* Restrict parent "company" when child "warehouse" updated */
      if update([company_number])
      begin
       if ((select count(*)
        from [company] t, inserted i
        where t.[company_number] = i.[company_number] ) != @numrows)
        begin
         raiserror  50002 'Parent does not exist in table ''company''. Cannot update child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
     /* Restrict parent "location" when child "warehouse" updated */
      if update([main_location])
      begin
       if ((select count(*)
        from [location] t, inserted i
        where t.[main_location] = i.[main_location] ) != @numrows)
        begin
         raiserror  50002 'Parent does not exist in table ''location''. Cannot update child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
     /* Restrict parent "downloader" when child "warehouse" updated */
      if update([download_type])
      begin
       if ((select count(*)
        from [downloader] t, inserted i
        where t.[download_type] = i.[download_type] ) != @numrows)
        begin
         raiserror  50002 'Parent does not exist in table ''downloader''. Cannot update child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
     /* Restrict parent "container" when child "warehouse" updated */
      if update([container_type])
      begin
       if ((select count(*)
        from [container] t, inserted i
        where t.[container_type] = i.[container_type] ) != @numrows)
        begin
         raiserror  50002 'Parent does not exist in table ''container''. Cannot update child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
     /* Restrict parent "port" when child "warehouse" updated */
      if update([port_number])
      begin
       if ((select count(*)
        from [port] t, inserted i
        where t.[port_number] = i.[port_number] ) != @numrows)
        begin
         raiserror  50002 'Parent does not exist in table ''port''. Cannot update child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
     
    end
    go

    /* Update trigger "tu_patio" for table "patio" */
    Create trigger [tu_patio]
    on [patio] after update as
    begin 
      
     declare @numrows int
     select @numrows = @@rowcount
     if @numrows = 0
      return
     
     
     /* Restrict parent "warehouse" when child "patio" updated */
      if update([warehouse_number])
      begin
       if ((select count(*)
        from [warehouse] t, inserted i
        where t.[warehouse_number] = i.[warehouse_number] ) != @numrows)
        begin
         raiserror  50002 'Parent does not exist in table ''warehouse''. Cannot update child table ''patio''.'
         rollback transaction
         return
        end
      end  
     
     /* Restrict parent "style" when child "patio" updated */
      if update([style_number])
      begin
       if ((select count(*)
        from [style] t, inserted i
        where t.[style_number] = i.[style_number] ) != @numrows)
        begin
         raiserror  50002 'Parent does not exist in table ''style''. Cannot update child table ''patio''.'
         rollback transaction
         return
        end
      end  
     
     
    end
    go

    /* Update trigger "tu_furniture" for table "furniture" */
    Create trigger [tu_furniture]
    on [furniture] after update as
    begin 
      
     declare @numrows int
     select @numrows = @@rowcount
     if @numrows = 0
      return
     
     
     /* Restrict parent "patio" when child "furniture" updated */
      if update([patio_number])
      begin
       if ((select count(*)
        from [patio] t, inserted i
        where t.[patio_number] = i.[patio_number] ) != @numrows)
        begin
         raiserror  50002 'Parent does not exist in table ''patio''. Cannot update child table ''furniture''.'
         rollback transaction
         return
        end
      end  
     
     
    end
    go

    /* Update trigger "tu_port" for table "port" */
    Create trigger [tu_port]
    on [port] after update as
    begin 
      
     declare @numrows int
     select @numrows = @@rowcount
     if @numrows = 0
      return
     
     
     /* Restrict parent "country" when child "port" updated */
      if update([country_number])
      begin
       if ((select count(*)
        from [country] t, inserted i
        where t.[country_number] = i.[country_number] ) != @numrows)
        begin
         raiserror  50002 'Parent does not exist in table ''country''. Cannot update child table ''port''.'
         rollback transaction
         return
        end
      end  
     
     
    end
    go


    /* Insert trigger "ti_warehouse" for table "warehouse" */
    Create trigger [ti_warehouse]
    on [warehouse] after insert as
    begin 
     
     declare @numrows int
     select @numrows = @@rowcount
     if @numrows = 0
      return
     
      /* Restrict child "warehouse" when parent "company" insert */
      if update([company_number])
      begin
       if ((select count(*)
        from [company] t, inserted i
        where t.[company_number] = i.[company_number] ) != @numrows)
        begin
         raiserror  50004 'Parent does not exist in table ''company''. Cannot insert into child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
      /* Restrict child "warehouse" when parent "location" insert */
      if update([main_location])
      begin
       if ((select count(*)
        from [location] t, inserted i
        where t.[main_location] = i.[main_location] ) != @numrows)
        begin
         raiserror  50004 'Parent does not exist in table ''location''. Cannot insert into child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
      /* Restrict child "warehouse" when parent "downloader" insert */
      if update([download_type])
      begin
       if ((select count(*)
        from [downloader] t, inserted i
        where t.[download_type] = i.[download_type] ) != @numrows)
        begin
         raiserror  50004 'Parent does not exist in table ''downloader''. Cannot insert into child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
      /* Restrict child "warehouse" when parent "container" insert */
      if update([container_type])
      begin
       if ((select count(*)
        from [container] t, inserted i
        where t.[container_type] = i.[container_type] ) != @numrows)
        begin
         raiserror  50004 'Parent does not exist in table ''container''. Cannot insert into child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
      /* Restrict child "warehouse" when parent "port" insert */
      if update([port_number])
      begin
       if ((select count(*)
        from [port] t, inserted i
        where t.[port_number] = i.[port_number] ) != @numrows)
        begin
         raiserror  50004 'Parent does not exist in table ''port''. Cannot insert into child table ''warehouse''.'
         rollback transaction
         return
        end
      end  
     
     
    end
    go

    /* Insert trigger "ti_patio" for table "patio" */
    Create trigger [ti_patio]
    on [patio] after insert as
    begin 
     
     declare @numrows int
     select @numrows = @@rowcount
     if @numrows = 0
      return
     
      /* Restrict child "patio" when parent "warehouse" insert */
      if update([warehouse_number])
      begin
       if ((select count(*)
        from [warehouse] t, inserted i
        where t.[warehouse_number] = i.[warehouse_number] ) != @numrows)
        begin
         raiserror  50004 'Parent does not exist in table ''warehouse''. Cannot insert into child table ''patio''.'
         rollback transaction
         return
        end
      end  
     
      /* Restrict child "patio" when parent "style" insert */
      if update([style_number])
      begin
       if ((select count(*)
        from [style] t, inserted i
        where t.[style_number] = i.[style_number] ) != @numrows)
        begin
         raiserror  50004 'Parent does not exist in table ''style''. Cannot insert into child table ''patio''.'
         rollback transaction
         return
        end
      end  
     
     
    end
    go

    /* Insert trigger "ti_furniture" for table "furniture" */
    Create trigger [ti_furniture]
    on [furniture] after insert as
    begin 
     
     declare @numrows int
     select @numrows = @@rowcount
     if @numrows = 0
      return
     
      /* Restrict child "furniture" when parent "patio" insert */
      if update([patio_number])
      begin
       if ((select count(*)
        from [patio] t, inserted i
        where t.[patio_number] = i.[patio_number] ) != @numrows)
        begin
         raiserror  50004 'Parent does not exist in table ''patio''. Cannot insert into child table ''furniture''.'
         rollback transaction
         return
        end
      end  
     
     
    end
    go

    /* Insert trigger "ti_port" for table "port" */
    Create trigger [ti_port]
    on [port] after insert as
    begin 
     
     declare @numrows int
     select @numrows = @@rowcount
     if @numrows = 0
      return
     
      /* Restrict child "port" when parent "country" insert */
      if update([country_number])
      begin
       if ((select count(*)
        from [country] t, inserted i
        where t.[country_number] = i.[country_number] ) != @numrows)
        begin
         raiserror  50004 'Parent does not exist in table ''country''. Cannot insert into child table ''port''.'
         rollback transaction
         return
        end
      end  
     
     
    end
    go


    Set quoted_identifier off
    go

     

    As you can see, there are no security controls here (as a business type, login code, securiy level, granted access and so on) because that will be handled by a different security application which will grant shared access to different applications under a single login (portal like). Thank you again
     

     

     

  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Nov 20, 2010 11:55 AM|PeterSkellern|LINK

    Thank you again.  I have to add that those not-included variables that I mentioned before are the ones I need to prefilter. In our design all companies share the same database but a logged user will have access strictly to a portion of it that he will be allowed to see. That's why I have tought of including general variables (seen all the time by the application while the user is logged in). In that way I will add an additional condition to your Linq sentence and I will have the hierarchichal filters working ok to our needs. They do work fine right now but we need to add pre filtering to them.

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Nov 20, 2010 07:01 PM|sjnaughton|LINK

    Hi Peter, I get these errors when I try to run the script

    Msg 2749, Level 16, State 2, Line 2

    Identity column 'main_location' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

    Msg 2749, Level 16, State 2, Line 2

    Identity column 'container_type' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

    Msg 2749, Level 16, State 2, Line 2

    Identity column 'port_number' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

    Msg 2749, Level 16, State 2, Line 2

    Identity column 'country_number' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

    Msg 4902, Level 16, State 1, Line 1

    Cannot find the object "location" because it does not exist or you do not have permissions.

    Msg 4902, Level 16, State 1, Line 1

    Cannot find the object "container" because it does not exist or you do not have permissions.

    Msg 4902, Level 16, State 1, Line 1

    Cannot find the object "port" because it does not exist or you do not have permissions.

    Msg 4902, Level 16, State 1, Line 1

    Cannot find the object "country" because it does not exist or you do not have permissions.

    Msg 1767, Level 16, State 0, Line 1

    Foreign key 'FK_Location_Warehouse' references invalid table 'location'.

    Msg 1750, Level 16, State 0, Line 1

    Could not create constraint. See previous errors.

    Msg 1767, Level 16, State 0, Line 1

    Foreign key 'FK_Container_Warehouse' references invalid table 'container'.

    Msg 1750, Level 16, State 0, Line 1

    Could not create constraint. See previous errors.

    Msg 1767, Level 16, State 0, Line 1

    Foreign key 'FK_port_Warehouse' references invalid table 'port'.

    Msg 1750, Level 16, State 0, Line 1

    Could not create constraint. See previous errors.

    Msg 4902, Level 16, State 1, Line 1

    Cannot find the object "port" because it does not exist or you do not have permissions.

    Msg 8197, Level 16, State 4, Procedure tu_port, Line 3

    The object 'port' does not exist or is invalid for this operation.

    Msg 8197, Level 16, State 4, Procedure ti_port, Line 3

    The object 'port' does not exist or is invalid for this operation.

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Nov 21, 2010 10:05 AM|PeterSkellern|LINK

    Thank you and sorry. This is a simplified version. Sorry for using Varchar instead of a Numeric definition in the main primary key. Probably to much used to work with Oracle default naming convention. My apologies again.

    I am including the first part as a housekeeper just if some newer entity might have been added to your database running my other script. You can skip it if you have already deleted those if any.


    if exists (select * from sys.foreign_keys where object_id = object_id('[FK_Company_Patio]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [warehouse] drop constraint [FK_Company_Patio]
    go
    if exists (select * from sys.foreign_keys where object_id = object_id('[FK_Warehouse_Patio]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [patio] drop constraint [FK_Warehouse_Patio]
    go
    if exists (select * from sys.foreign_keys where object_id = object_id('[FK_Patio_Furniture]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [furniture] drop constraint [FK_Patio_Furniture]
    go
    if exists (select * from sys.foreign_keys where object_id = object_id('[FK_Style_Patio]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [patio] drop constraint [FK_Style_Patio]
    go
    if exists (select * from sys.foreign_keys where object_id = object_id('[FK_Location_Warehouse]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [warehouse] drop constraint [FK_Location_Warehouse]
    go
    if exists (select * from sys.foreign_keys where object_id = object_id('[FK_Downloader_Warehouse]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [warehouse] drop constraint [FK_Downloader_Warehouse]
    go
    if exists (select * from sys.foreign_keys where object_id = object_id('[FK_Container_Warehouse]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [warehouse] drop constraint [FK_Container_Warehouse]
    go
    if exists (select * from sys.foreign_keys where object_id = object_id('[FK_port_Warehouse]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [warehouse] drop constraint [FK_port_Warehouse]
    go
    if exists (select * from sys.foreign_keys where object_id = object_id('[FK_Country_Port]') and OBJECTPROPERTY(object_id, 'IsForeignKey') = 1)
    Alter table [port] drop constraint [FK_Country_Port]
    go


    if exists (select * from sys.tables where object_id = object_id('[country]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [country]
    go
    if exists (select * from sys.tables where object_id = object_id('[port]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [port]
    go
    if exists (select * from sys.tables where object_id = object_id('[container]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [container]
    go
    if exists (select * from sys.tables where object_id = object_id('[downloader]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [downloader]
    go
    if exists (select * from sys.tables where object_id = object_id('[location]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [location]
    go
    if exists (select * from sys.tables where object_id = object_id('[style]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [style]
    go
    if exists (select * from sys.tables where object_id = object_id('[furniture]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [furniture]
    go
    if exists (select * from sys.tables where object_id = object_id('[patio]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [patio]
    go
    if exists (select * from sys.tables where object_id = object_id('[warehouse]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [warehouse]
    go
    if exists (select * from sys.tables where object_id = object_id('[company]') and OBJECTPROPERTY(object_id, 'IsUserTable') = 1)
     Drop table [company]
    go


    Create table [company]
    (
     [company_number] Numeric(8,0) Identity NOT NULL,
     [company_name] Varchar(40) NULL, UNIQUE ([company_name]),
     [registration_number] Varchar(14) NULL, UNIQUE ([registration_number]),
     [main_activity] Varchar(120) NULL,
    Constraint [pk_company] Primary Key ([company_number])
    )
    go

    Create table [warehouse]
    (
     [warehouse_number] Numeric(4,0) Identity NOT NULL,
     [container_type] Numeric(2,0) NOT NULL,
     [port_number] Numeric(2,0) NOT NULL,
     [company_number] Numeric(8,0) NOT NULL,
     [main_location] Numeric(2,0) NOT NULL,
     [download_type] Numeric(2,0) NOT NULL,
     [warehouse_name] Varchar(1) NULL, UNIQUE ([warehouse_name]),
     [storage_capacity] Varchar(2) NULL,
     [number_of_racks] Numeric(3,0) NULL,
    Constraint [pk_warehouse] Primary Key ([warehouse_number])
    )
    go

    Create table [patio]
    (
     [patio_number] Numeric(3,0) Identity NOT NULL,
     [warehouse_number] Numeric(4,0) NOT NULL,
     [style_number] Numeric(18,0) NOT NULL,
    Constraint [pk_patio] Primary Key ([patio_number])
    )
    go

    Create table [furniture]
    (
     [furniture_number] Numeric(8,0) Identity NOT NULL,
     [patio_number] Numeric(3,0) NOT NULL,
     [glazed_folding] Binary(1) NULL,
     [wood_folding] Binary(1) NULL,
     [aluminum_clad_folding] Binary(1) NULL,
     [sliding_type] Varchar(2) NULL,
     [accessories] Binary(1) NULL,
     [complementary_info] Varchar(120) NULL,
    Constraint [pk_furniture] Primary Key ([furniture_number])
    )
    go

    Create table [style]
    (
     [style_number] Numeric(18,0) Identity NOT NULL,
     [style_name] Varchar(60) NULL, UNIQUE ([style_name]),
    Constraint [pk_style] Primary Key ([style_number])
    )
    go

    Create table [location]
    (
     [main_location] Numeric(2,0) Identity NOT NULL,
     [location_name] Varchar(40) NULL, UNIQUE ([location_name]),
    Constraint [pk_location] Primary Key ([main_location])
    )
    go

    Create table [downloader]
    (
     [download_type] Numeric(2,0) Identity NOT NULL,
     [download_name] Varchar(40) NULL, UNIQUE ([download_name]),
    Constraint [pk_downloader] Primary Key ([download_type])
    )
    go

    Create table [container]
    (
     [container_type] Numeric(2,0) Identity NOT NULL,
     [container_name] Varchar(60) NULL, UNIQUE ([container_name]),
    Constraint [pk_container] Primary Key ([container_type])
    )
    go

    Create table [port]
    (
     [country_number] Numeric(2,0) NOT NULL,
     [port_number] Numeric(2,0) Identity NOT NULL,
     [port_name] Varchar(120) NULL,
    Constraint [pk_port] Primary Key ([port_number])
    )
    go

    Create table [country]
    (
     [country_number] Numeric(2,0) Identity NOT NULL,
     [country_name] Varchar(80) NULL, UNIQUE ([country_name]),
    Constraint [pk_country] Primary Key ([country_number])
    )
    go


    Alter table [warehouse] add Constraint [FK_Company_Patio] foreign key([company_number]) references [company] ([company_number])  on update no action on delete no action
    go
    Alter table [patio] add Constraint [FK_Warehouse_Patio] foreign key([warehouse_number]) references [warehouse] ([warehouse_number])  on update no action on delete no action
    go
    Alter table [furniture] add Constraint [FK_Patio_Furniture] foreign key([patio_number]) references [patio] ([patio_number])  on update no action on delete no action
    go
    Alter table [patio] add Constraint [FK_Style_Patio] foreign key([style_number]) references [style] ([style_number])  on update no action on delete no action
    go
    Alter table [warehouse] add Constraint [FK_Location_Warehouse] foreign key([main_location]) references [location] ([main_location])  on update no action on delete no action
    go
    Alter table [warehouse] add Constraint [FK_Downloader_Warehouse] foreign key([download_type]) references [downloader] ([download_type])  on update no action on delete no action
    go
    Alter table [warehouse] add Constraint [FK_Container_Warehouse] foreign key([container_type]) references [container] ([container_type])  on update no action on delete no action
    go
    Alter table [warehouse] add Constraint [FK_port_Warehouse] foreign key([port_number]) references [port] ([port_number])  on update no action on delete no action
    go
    Alter table [port] add Constraint [FK_Country_Port] foreign key([country_number]) references [country] ([country_number])  on update no action on delete no action
    go


    Set quoted_identifier on
    go


    Set quoted_identifier off
    go


     

     

    **********************************************************************

     

     

    That must work

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Nov 21, 2010 12:33 PM|sjnaughton|LINK

    Hi Peter, I cant see which table this filtered FK field would work on can you suggest which table and which columns on the sample DB you sent me please [:)]

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Nov 21, 2010 03:16 PM|PeterSkellern|LINK

    Thank you.

    Let's say I have 3 companies: AKME, KATZ and DOGZ.

          a) AKME main_activity field will be "STUFF"

          b) KATZ and DOGZ main_activity field will be "ANIMALZ"

     

    Let's also say that we have 6 warehouses: AKME1, AKME2, KATZ1, KATZ2, DOGZ1 and DOGZ2. 

    We will also think that we have 6 patios: PATIOAKME1, PATIOAKME2, PATIOKATZ1, PATIOKATZ2, PATIODOGZ1 AND PATIODOGZ2.

     

    In our example there will be 12 furniture records:

          a) PATIOAKME1: glazed_folding and wood_folding
          b) PATIOAKME2: accessories and sliding_type

          c) PATIOKATZ2: glazed_folding and wood_folding
          d) PATIOKATZ2: accessories and sliding_type

          e) PATIODOGZ1: glazed_folding and wood_folding
           f) PATIODOGZ2: accessories and sliding_type

    The example should be good for displaying the furnitures entity. There will be also a Session["typeofbusiness"] = "STUFF" which will complete the filtering stage.

    So it will be expected that after creation of all records in the database there will be displayed:

    a) Combo Box for Companies : AKME only

    b) Combo Box for warehouses: AKME1 and AKME2 only

    c) Combo Box for Patios : patios: PATIOAKME1 and PATIOAKME2 only

    d) Combo Box for Furniture (using the ToString() modification)

         PATIOAKME1-glazed_folding
         PATIOAKME1-wood_folding
         PATIOAKME2-accessories
         PATIOAKME2-sliding_type

    Is that ok for creating a working sample?

     

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Nov 21, 2010 06:19 PM|sjnaughton|LINK

    I'll do my best to get somthing working you will have to tune it to you exact specification but it should work. 

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Nov 22, 2010 06:15 AM|sjnaughton|LINK

    Hi Peter, try this

    public new void PopulateListControl(ListControl listControl)
    {
        var filterAttribute = Column.GetAttribute<FilterForeignKeyAttribute>();
        if (filterAttribute == null || Session[filterAttribute.SessionVariableName] == null)
        {
            base.PopulateListControl(listControl);
            return;
        }
    
    
        // get context;
        var context = Column.Table.CreateContext();
    
    
        // get fkTable
        var foreignKeyTable = ForeignKeyColumn.ParentTable;
    
    
        // get filter column
        var filterColumn = foreignKeyTable.GetColumn(filterAttribute.FilterColumnName);
    
    
        // get value
        var value =  Convert.ChangeType(Session[filterAttribute.SessionVariableName].ToString(), filterColumn.TypeCode, CultureInfo.InvariantCulture);
    
    
        // Get Column Value query
        var query = foreignKeyTable.GetQuery(context);
    
    
        // get the table entity to be filtered
        var entityParam = Expression.Parameter(foreignKeyTable.EntityType, foreignKeyTable.Name);
    
    
        // get the property to be filtered
        var property = Expression.Property(entityParam, filterColumn.Name);
    
    
        // get the equal call
        var equalsCall = Expression.Equal(property, Expression.Constant(value));
    
    
        // get the where lambda
        var whereLambda = Expression.Lambda(equalsCall, entityParam);
    
    
        // get the where call
        var whereCall = Expression.Call(typeof(Queryable), "Where", new Type[] { foreignKeyTable.EntityType }, query.Expression, whereLambda);
    
    
        var values = query.Provider.CreateQuery(whereCall);
    
    
        foreach (var row in values)
        {
            listControl.Items.Add(new ListItem()
            {
                Text = foreignKeyTable.GetDisplayString(row),
                Value = foreignKeyTable.GetPrimaryKeyString(row)
            });
        }
    }

    And here is the Attribute:

    [AttributeUsage(AttributeTargets.Property | AttributeTargets.Field, AllowMultiple = false)]
    public class FilterForeignKeyAttribute : Attribute
    {
        /// <summary>
        /// Gets or sets the name of the filter column.
        /// </summary>
        /// <value>The name of the filter column.</value>
        public String FilterColumnName { get; set; }
    
        /// <summary>
        /// Gets or sets the name of the session variable.
        /// </summary>
        /// <value>The name of the session variable.</value>
        public String SessionVariableName { get; set; }
    
        public FilterForeignKeyAttribute(String filterColumnName, String sessionVariableName)
        {
            FilterColumnName = filterColumnName;
            SessionVariableName = sessionVariableName;
        }
    }

    and lastly here is the extension method:

    /// <summary>
    /// Get the attribute of type T or null if not found
    /// </summary>
    /// <typeparam name="T">Attribute type</typeparam>
    /// <param name="table">Column to search for the attribute on.</param>
    /// <returns>Returns the attribute T or null</returns>
    public static T GetAttribute<T>(this MetaColumn column) where T : Attribute
    {
        return column.Attributes.OfType<T>().FirstOrDefault();
    }

    If you e-mail me from using the Contact button I can send you the sample project.

     

    Dynamic Data 4 Filtered ForeignKey

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Nov 29, 2010 01:06 PM|PeterSkellern|LINK

    Thank you. I must say that before writing this question I tried first creating a new post but it never shows. So here I go.

    If I had the Linq sentence:

        var MyDC = new MyModel.MyDataContext(); 
        var MyValues = from p in MyDC.Patio
              where p.LoginID == loginId
              select p;  

    Let's say that I have a second variable: MyCurrentTable which could be equal to: Patio, Furniture, Warehouse, Company, etc. depending on the table being used at any given time

    From there, how can I get to a kind more generic kind of construction, like this one (for sure I know that the syntax I am using is not ok, sorry about that. I have used like this as to illustrate my question)

        var MyValues = from p in ({0}.{1}, MyDC, MyCurrentTable)
                     where p.LoginID == loginId
                      select p;  

    Where “MyCurrentTable” could be: any table at any given time. In that way I could have this piece of code working in a recyclable way, valid for any table. Is that possible? Thank you.

     

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Nov 29, 2010 07:30 PM|sjnaughton|LINK

    Hi Peter the only way is to build the Linq query using Expression as in the above sample in my previous post. 

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Dec 01, 2010 03:13 AM|PeterSkellern|LINK

    Thank you. I wouldn't like to seem stubborn but I am trying to understand something. If using normal C# syntax as in many examples wit Console.WriteLine followed by {0}, {1}, {3}, ..., {n} why can't you use the same logic for writing more simpler evaluated sentences?

    I am just trying to say that there should be a simpler way of using the values contained in some variables and use those values to create C# sentences like when you use the {number} syntax.

    e.g. (I am just copying something found randomly on the web)

    using System;

    class Program
    {
        static void Main()
        {
            string value1 = "Dot";
            string value2 = "Net";
            string value3 = "Perls";

            Console.WriteLine("{0}, {1}, {2}", // <-- This is called a format string.
                value1,                        // <-- These are substitutions.
                value2,
                value3);
        }
    }

     

    So I think that you might use as well substitutions to create C# sentences on the fly, no? Or should I understand that the evaluated values contained inside a variable can not be used or can not be recognized to create sentences on the fly? Something like instantiating those values. Sorry I am getting all of this really wrong. OOP is a nightmare

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Dec 01, 2010 09:05 AM|sjnaughton|LINK

    Because  Console.WriteLine is just string manipulation same as String.Format with overloads for different types.

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Dec 02, 2010 01:59 AM|PeterSkellern|LINK

    Thank you. Your CascadeHierarchical works fine. It seems that the whole thing is about having a good MetaData file.

    What if you don't want the filters to be reset for every new insertion? I tried deactvating ResetAllDescendantListControls(childIndex); but if you do it will raise an exception. This would be a nice feature to have

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Dec 02, 2010 08:11 AM|sjnaughton|LINK

    Hi Peter, do you mean retain the filter values after inseting a new item? 

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Dec 02, 2010 08:54 PM|PeterSkellern|LINK

    Thank you. You just said it beautifully:  retaining the filter values after inserting a new item, yes

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Dec 03, 2010 06:40 AM|sjnaughton|LINK

    Just to fix the language used here fist so that others know what we are talking about dropdown list cascade or other in the Edit or Insert pages are field templates not filters, filters are only on the List page and filter the data being shown [:)]

    That said there have been several other threads on this forum about what you are asking for, the simplest solution would be to post to insert page with a query string as this has been supported since DD1 e.g.

    ~/Orders/Insert.aspx?CustomerID=ALFKI&EmployeeID=3&ShipVia=3

    This can then be used to set the value in the ForeignKey field template. You could do the same in other field templates with a little bit of code.

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Dec 03, 2010 06:23 PM|PeterSkellern|LINK

    Thank you. I will try that and I will also check similar questions too.  If I had understood what you're saying I will have to collect the values selected from your cascade controls before insertion and pass them as query string to a redirected page which will be similar to the used before but accepting url parameters. But how can you pass an indefinite amount of values depending on the cascade size? It could be as well 2 parameters or 5 or any number.

    Not quite sure how will I identify current combo box entity name and its corresponding selected value because all of them will be sharing the same cascade control. I think that maybe creating a variable array to strore those values will suffice; its dimension will vary depending on the number of levels of the cascade.. That could work, right?

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Dec 03, 2010 08:05 PM|sjnaughton|LINK

    PeterSkellern

    But how can you pass an indefinite amount of values depending on the cascade size?
     

    You would only need the final value the CascadingFilter works off that in Edit mode anyway.

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Dec 03, 2010 08:38 PM|PeterSkellern|LINK

    Thank you. I got lost. 

    You wrote: ~/Orders/Insert.aspx?CustomerID=ALFKI&EmployeeID=3&ShipVia=3, so you are passing 3 parameters in the query string, right? But you are using the same control so I will have to collect that value 3 times because it must be populated 3 times, one for each table

    That's why I need to know in what precise moment populate with them the combo box control pre assigining the selected value for each case. There must be a way of keeping track of what is what and when. I'm rather confused. Thank you

     

  • sjnaughton sjnaughton

    All-Star

    17905 Points

    5679 Posts

    MVP

    Re: Filtering foreign key drop down list question.

    Dec 03, 2010 09:44 PM|sjnaughton|LINK

    Hi Peter, see this long thread here http://forums.asp.net/t/1592267.aspx Scott Hunter mentions a solution you may be interested in and Carlos also finds a solution.

    Dynamic Data 4

    See my blog C# Bits | Twitter @sjnaughton
    Always seeking an elegant solution.
  • klca klca

    Member

    242 Points

    480 Posts

    Re: Filtering foreign key drop down list question.

    Dec 04, 2010 08:59 AM|klca|LINK

    Hi Steve,

    Is always a good thing to hear something from the greatest guru ever about Dynamic Data.

    Mr. Naughton seems to be the "humblest" guy ever but at the same time he would be the "smartest" guy ever talking about a product that MS left just abandoned on its own.

    I want to make clear that "my solution" is not really such a thing but my own interpretation and understanding of all the tricky game that Dynamic Data plays in order to build (we could call it COMFORM in the spanish language) the UI of its pages.

    So it seems that postback doesn't exist in Dynamic Data ... .... ..... ..... THEN YOU MUST CREATE IT ON YOUR OWN ... .... ..... THAT'S IT!!!!

    Of course that "my solution" is "not the elegant solution ... (if you're talking about C# code) BUT IT WORKS!!!!! ... .... and something that has been said here is true .... "my solution" is just about creating a matrix of variables as "to keep STATE .... artificially"

    My game was about identifiying the name of the table, the value selected in the dropdownlist during the insertion process. So I changed the way DD fills its dropdownlists and created a huge chain of conditions (using a switch structure) as to include context variables into my own Linq queries for every single table DD was using at a time.

    All of this was done by using massivelly the debug option of visual studio. So my solution is just a giant trip, hand by hand with DD, trough all the rounds and rounds that DD does to do what it does to create UI's. ... ... I just put pieces of code of my own in the middle ... THAT'S IT!!!! jijijiji Tongue out

    Best regards ever Mr. Naughton and my wishes of a blessing Christmas for you and your family ... you really deserve it.

     

    Carlos Porras (El Salvador)

     

  • PeterSkellern PeterSkeller...

    Member

    76 Points

    151 Posts

    Re: Filtering foreign key drop down list question.

    Dec 06, 2010 11:54 AM|PeterSkellern|LINK

    Thank you. It doesn't seem easy at all. Maybe I will try further ahead

  • ‹ Previous Thread|Next Thread ›