locked
EntityDataSource - connecting through multiple foreign keys? RRS feed

  • Question

  • User885317632 posted

    Tried Googling this but apparently couldn't find the right combination of keywords.  Hoping someone here has the answer.

    The general idea is that the work table has a foreign key to another table.  That secondary table also contains a foreign key to a third table.  How do I set up one Gridview to display columns from the work table, the secondary table, and the third table?

    Example:
    The work table has three columns:
    ID
    FullName
    StateID

    The secondary table has three columns:
    ID
    StateName
    ZoneID

    StateID in the work table is a foreign key to the ID column in the secondary table.

    The third table has two columns:
    ID
    ZoneName

    ZoneID in the secondary table is a foreign key to the ID column in the third table.

    The goal is to produce a GridView connected to an EntityDataSource that is connected to an Entity Data Model.  The columns in the Gridview would display the Fullname, the StateName, and the ZoneName.  It's easy to connect the work table to the secondary table and produce this:

    <asp:GridView ID="GridView2" runat="server" AllowSorting="True" 
        AutoGenerateColumns="False" DataKeyNames="ID" DataSourceID="EntityDataSource1" 
        EnableModelValidation="True">
        <Columns>
            <asp:BoundField DataField="FullName" HeaderText="FullName" 
                SortExpression="FullName" />
            <asp:TemplateField HeaderText="State" SortExpression="it.tblStates1.StateName">
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("tblStates1.StateName") %>'></asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

    Using this as an EntityDataSource, with an Include to the secondary table:

    <asp:EntityDataSource ID="EntityDataSource1" runat="server" 
        ConnectionString="name=demoEntities" DefaultContainerName="demoEntities" 
        EnableDelete="True" EnableInsert="True" EnableUpdate="True" 
        EntitySetName="tblNames1" Include="tblStates1">
    </asp:EntityDataSource>

    All of that works as expected.  What I'm trying to wrap my head around is how to add in that third column that connects to the third table.  Already tried modifying the EntityDataSource's Includes property to "tblStates1, tblZones1".  That causes an exception. 

    Entity Data Model is touted as being so robust.  Is it capable of doing such a thing?

     

    Tuesday, August 16, 2011 3:54 PM

Answers

  • User885317632 posted

    SOLVED!!!  A co-worker who is good with hand-coded LINQ queries gave me a tip about connecting to columns in tables that are daisy-chained.  Turned out to be pretty easy to adapt the EntityDataSource to do the same thing.  Here's what the EntityDataSource looks like now:

            <asp:EntityDataSource 
                ID="EntityDataSource1" 
                runat="server" 
                ConnectionString="name=demoEntities"
                DefaultContainerName="demoEntities"
                EntitySetName="Cities"
                Include="States,States.Countries" />
    

    The key was to Include the next table in the daisy-chain and then include that entity's reference to the next table in the chain... and so on and so no.  The Template field in the Gridview now looks like this:

                    <asp:TemplateField HeaderText="County" SortExpression="it.States.Countries.CountryName">
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("States.Countries.CountryName") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
    

    Works perfectly and allows editing of the State, which automatically updates the Country.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 23, 2011 10:48 AM

All replies

  • User3866881 posted

    Hello diverguy:)

    For such a case I think you can just manually write codes like this:

    protected void Page_Load(……)

    {

           if(!IsPostBack)

            {

                  using (EntitiesContenxt ec = new ExtitiesContext())

                   {

                          var result = from a in Table1

                                            from b in Table2

                                            from c in Table3

                                            where a.XXX==b.XXX && b.XXX==c.XXX

                                            select new

                                            {

                                                Your Property1 = a.XXX,

                                                Your Property2 = b.YYY,

                                                Your Property3 = c.ZZZ

                                            };

                            GridView1.DataSource = result;

                            GridView1.DataBind();

                   }

            }

    }

    Wednesday, August 17, 2011 11:11 PM
  • User885317632 posted

    Thanks Decker but that's not the direction this project is headed.  We're trying to determine how to do this entirely in markup, no code behind.  There has to be a way.  It's a very common scenario and the Entity Data Model is so robust that, surely, there is a way to do this. 

    Here's the general scenario:  Your database has a table of Cities, a table of States, and a table of Countries.  The Cities table has a foreign key to the table of States, which in turn has a foreign key to Countries.  The tables would look something like this:

    tblCities

    ID

    City

    StateID (fk)

     

    tblStates

    StateID

    StateName

    CountryID (fk)

    tblCountries

    CountryID

    Country

    Instead of displaying in a gridview something like:

    Dallas

    1

    1

    Houston

    1

    1

    Windsor

    4

    2

     The goal is to display this:

    Dallas

    Texas

    USA

    Houston

    Texas

    USA

    Windsor

    Ontario

    Canada


    Pretty simple.  If there is a City, we want to be able to also display the name of the state and the name of the country associated up the relational chain.  This is the EntityDataSource so far:

    <asp:EntityDataSource ID="EntityDataSource1" runat="server" ConnectionString="name=demoEntities"
        DefaultContainerName="demoEntities" EnableDelete="True" EnableInsert="True"
        EnableUpdate="True" EntitySetName="tblCities" Include="tblStates">
    </asp:EntityDataSource> 

    We tried changing the Include property to Include="tblStates,tblCountries" but that gave an exception that there was no navigation property from tblCities to tblCountries. 

    Does anyone know how to accomplish this using markup?  Or maybe changing a setting in the data model?  If so, what steps do we need to take to make this work?  Thanks for any help or guidance.

    Thursday, August 18, 2011 1:09 PM
  • User3866881 posted

    Hello divergury:)

    If you insist doing this……OK——

    1)Use my sql script to generate the relationship tables and add them into your EntityFramework's file:

    use master
    if exists (select name from sysdatabases where name='dbCountry')
    drop database dbCountry
    go

    create database dbCountry
    go

    use dbCountry
    create table Countries
    (
       CountryId int primary key identity(1,1),
       CountryName varchar(max)
    )

    create table States
    (
     stateId int primary key identity(1,1),
     stateName varchar(max),
     CountryId int foreign key  references Countries(CountryId)
    )
    create table Cities
    (
        CityId int primary key identity(1,1),
        CityName varchar(max),
        StateId int foreign key references States(StateId)
    )

    --Insert Test Data
    insert into Countries(CountryName) values('China')
    insert into States(stateName,CountryId)values('ZheJiang',1)
    insert into Cities(CityName,StateId)values('ShangHai',1)
    insert into Countries(CountryName) values('USA')
    insert into States(stateName,CountryId)values('Texas',2)
    insert into Cities(CityName,StateId)values('Dallas',2)

    2)Then Try this:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Login.aspx.cs" Inherits="WebCSharp.Login" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                DataSourceID="EntityDataSource1">
                <Columns>
                    <asp:BoundField DataField="CountryName" HeaderText="CountryName" 
                        ReadOnly="True" SortExpression="CountryName" />
                         <asp:BoundField DataField="StateName" HeaderText="StateName" 
                        ReadOnly="True" SortExpression="StateName" />
                           <asp:BoundField DataField="CityName" HeaderText="CityName" 
                        ReadOnly="True" SortExpression="CityName" />
                </Columns>
            </asp:GridView>
            <asp:EntityDataSource ID="EntityDataSource1" runat="server" 
                ConnectionString="name=dbCountryEntities" 
                DefaultContainerName="dbCountryEntities" EnableFlattening="False" 
                CommandText="select it.[CountryName],s.[StateName],c.[CityName] from Countries as it,States as s,Cities as c" >
            </asp:EntityDataSource>
        </form>
    </body>
    </html>
    Thursday, August 18, 2011 9:45 PM
  • User885317632 posted

            <asp:EntityDataSource ID="EntityDataSource1" runat="server" 
                ConnectionString="name=dbCountryEntities" 
                DefaultContainerName="dbCountryEntities" EnableFlattening="False" 
                CommandText="select it.[CountryName],s.[StateName],c.[CityName] from Countries as it,States as s,Cities as c" >
            </asp:EntityDataSource>

    We're getting really close!  Thanks for your help so far. 

    Making use of the CommandText property instead of the Include property managed to get all of the columns in the gridview.  However, all of the cities appear for every possible combination of states and countries.  Meaning, the gridview displays ShangHai for ZheJaing, China and a ShangHai for Texas, USA.  Dallas appears for Texas, USA and for ZheJaing, China.  I added a few more cities and states and ran the page again.  Every possible combination appears.  Not really sure why.  Never had another type of datasource do this.  By the way... EnableFlattening was not a valid property for my version of the EntityDataSource.  Not sure if this is why but our company hasn't rolled out .NET 4.0 yet; we're still on 3.5 SP1. 

    Any suggestions on how to stop the repetition?

    Friday, August 19, 2011 11:17 AM
  • User3866881 posted

    Terribly sorry, I'm too excited to come to the success so that I forgot the where cause....

    Plz have a try like this——

    select it.[CountryName],s.[StateName],c.[CityName] from Countries as it,States as s,Cities as c where it.[CountryId]=s.[CountryId] and s.[StateId]=c.[StateId]

    Friday, August 19, 2011 9:05 PM
  • User885317632 posted
    Thanks Decker. That looks like it will probably do the trick. I'll be able to test it Monday and let you know. Thanks for hanging in there with me.
    Friday, August 19, 2011 9:22 PM
  • User3866881 posted

    Pleasure! Happy being with you to talk about this....

    Friday, August 19, 2011 11:45 PM
  • User885317632 posted

    select it.[CountryName],s.[StateName],c.[CityName] from Countries as it,States as s,Cities as c where it.[CountryId]=s.[CountryId] and s.[StateId]=c.[StateId]

    Hmm... That didn't work, after all.  In the model, the CountryId column in table States and the StateId column in table Cities is "absorbed" in the schema.  At runtime, an exception is thrown indicating 'CountryId' is not a member of type 'demo1Model.States'.  Any suggestions?

    Monday, August 22, 2011 8:29 AM
  • User3866881 posted

    Well……I've sent you a private message, plz open you inputbox in the asp.net after you logged in and package your whole proj and your db for testing to the email I've included.——With the title "Sample offered, plz help……"

    Thx again

    Monday, August 22, 2011 9:03 PM
  • User885317632 posted
    Thanks for the offer, Decker. Unfortunately, my company is very security conscious and has built in "safeguards" that prevent being able to package and export even this demo project, including in zip files. Going to try a couple of more things and will report back here on their success.
    Tuesday, August 23, 2011 6:20 AM
  • User885317632 posted

    SOLVED!!!  A co-worker who is good with hand-coded LINQ queries gave me a tip about connecting to columns in tables that are daisy-chained.  Turned out to be pretty easy to adapt the EntityDataSource to do the same thing.  Here's what the EntityDataSource looks like now:

            <asp:EntityDataSource 
                ID="EntityDataSource1" 
                runat="server" 
                ConnectionString="name=demoEntities"
                DefaultContainerName="demoEntities"
                EntitySetName="Cities"
                Include="States,States.Countries" />
    

    The key was to Include the next table in the daisy-chain and then include that entity's reference to the next table in the chain... and so on and so no.  The Template field in the Gridview now looks like this:

                    <asp:TemplateField HeaderText="County" SortExpression="it.States.Countries.CountryName">
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# Eval("States.Countries.CountryName") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
    

    Works perfectly and allows editing of the State, which automatically updates the Country.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 23, 2011 10:48 AM