none
Multiple derived entities from base entity RRS feed

  • Question

  • Hi.  Iam an absolute learner with EF but by way of background I have done some ASP Web apps on SQL Table Adapters.

    The application Iam writing is for a sports competition (TPT)

    I have a base entity as Person (Entity Set Name "People") with the usual properties eg. FirstName, LastName, Address.

    I have derived entities (from Person) as Player, Referee, Coach.  Each of these derived entities have one or two properties that particularly pertain to them.  It is very likley that one person could also be one or more of a the derived entities.

    eg. Fred Smith will be a Person but may also be a Player and a Referee

    eg. Harry Jones will be a Person and may also be a Coach.

    eg. Mary Brown may only be a Person

    I'm struggling as to how to create a query (Linq to Entity) or ESQL that would give me a list of all people, and their derived entities.

    Any help (vb preferred) would be greatly appreciated.

     

    Thanks 



    Saturday, August 20, 2011 7:44 AM

Answers

  • Hi the.blacksmith;

    This is what I found out running your DB and EDMX and the code. Because a Person can be a Player, Referee, Coach and User all at the same time this TPT or TPH table type will NOT seem to be a good choise for a db design in this case and here is why. When you make the query of People of type Person it will return a record for each typer of Person and in the test case you will receive two records for Brad Huggett whose Primary key is 1. At this point object tracking will throw an exception because it has two records with the same Primary Key for a table of type Person. To show that this is the case you can place the following line of code just after you instantiate the object context:

    context.People.MergeOption = MergeOption.NoTracking

    Now the program will work without throwing an exception because ObjectContext or more correctly Object State Manager will no longer track the entities coming from the query and so there will be no conflict with primary keys. Now this is fine as long as you do not need to update any of the records. If you need to update a record you will need to query for that record with change tracking enabled and then modify the record and SaveChanges. Of course this same issue will occurs if you query for Referee and then query for lets say User with one person having both types because both types share the same Primary Key from Person.

    In this case I would just create the five tables in the DB and define relations between Peron and the other four tables.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by The.Blacksmith Wednesday, August 24, 2011 12:32 PM
    Tuesday, August 23, 2011 7:12 PM

All replies

  • Hi the.blacksmith;

    From your description I as assuming you are using TPT, Table Per Type, and so the solution below should do what you need.

    // Cerate the object context for the query
    SportsCompetitionEntities ctx = new SportsCompetitionEntities( );
    
    // Query the base table and group them in such a way that each group is one person.
    // I used LastName and FristName but they may be more then one person with the same names
    // so if there is a PersonId for each person that would be a better key
    var people = from p in ctx.Person.OfType<People>( )
           group p by new { LastName = p.LastName, FirstName = p.FirstName } into pGroup
           select pGroup;
    
    // Iterate through each group
    foreach (var pg in people)
    {
      // The key of the group identifies the person the group contains
      Console.WriteLine(pg.Key.LastName + ", " + pg.Key.FirstName);
      
      // Now iterate through the different function the person does
      foreach (var p in pg)
      {
        // Get the type of the underlaying object for this class. This type has some
        // added characters after the class name starting at the _ character, so we 
        // strip it out.
        string groupType = p.GetType( ).Name.Substring( 0, p.GetType( ).Name.IndexOf( '_' ) );
        // Now cast the class to the derived type so that you can access its properties
        switch( groupType )
        {
          case "Player":
            var player = ( Player ) p;
            Console.WriteLine("\t" + player. ...);
            break;
          case "Referee":
            var referee = ( Referee ) p;
            Console.WriteLine("\t" + referee. ...);
            break;
          case "Coach":
            var coach = ( Coach ) p;
            Console.WriteLine("\t" + coach. ...);
            break;
        }          
      }        
    }
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, August 20, 2011 3:58 PM
  • Thanks Fernando for your unbelievable effort so far.  But Im still a little stuck.

    Let me give you some explicit info in the hope that maybe you can help me further.

    Just to confirm, yes, it is TPT. It is a model first design in EF 4

    Base Entity: Person – entity set name People. (I’m hoping your first C# query should actually say ctx.Person.OfType<People> (not ctx.People.OfType<Person>) otherwise I’ve got no idea…

    • Person Properties: Id:Integer (PK), LastName:String, FirstName:String

     

     Inherited Entity:Referee (From Person)

    • Referee Properties: Id:Integer, Qualification: String

     

    Inherited Entity:Player (From Person)

    • Player Properties: Id:Integer, ShirtNumber:String

     

    (Let's leave the Coach out of it for the moment)


    So yes, they should be able to group on the Id as per your assumption…


    I’m sorry but I’m a dunce in C# so I have tried valiantly to translate your first query using Id as the Grouping key … only trouble is I get:

     

    <strong>Dim people = From p In context.People.OfType(Of Person)() Group p By New With {Key .Id = p.Id} Into pGroup() Select pGroup</strong>
    


    Only trouble is I get an error in the IDE that says:


    "Range variable name can be inferred only from a simple or qualified name with no arguments"

     

    Any further ideas? Or do you need any more in from me?

     

    Thank you very much.

     

     

    Sunday, August 21, 2011 8:47 AM
  • Hi the.blacksmith;

    OK I made the changes to VB .Net language. This should get you near where you want to be.

    Dim people = From p In context.People.OfType(Of Person)() _
           Group p By ThisKey = New With { .Key = p.Id, .LastName = p.LastName, .FirstName = p.FirstName} into pGroup = Group _
           Select Key = ThisKey.Key, _
              Name = ThisKey.LastName & ", " & ThisKey.FirstName, _
              pGroup
             
    For Each gp In people
      Console.WriteLine(gp.Key & " : " & gp.Name)
      For Each p in gp.pGroup
        Dim pType As String = p.GetType().Name.Substring(0, p.GetType().Name.IndexOf("_"c))
        Select pType
          Case "Referee"
            Dim personType As Referee = CType(p, Referee)
            Console.WriteLine( "Access the Referee type parameters")
          Case "Player"
            Dim personType As Player = CType(p, Player)
            Console.WriteLine( "Access the Player type parameters")
        End Select
      Next
    Next
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, August 21, 2011 4:59 PM
  • Hi Fernando

    Thank you for your continued help...

    This is now getting closer... in my test data, all of the people are only players except I have only made 1 a referee also.

    Your people query seems to execute OK.  Then the for each outer loop goes well and executes for every record including the one preceding the person who is a referee also... as it hits the Next (ie to process the person who is the referee also), the program throws the following:

    All objects in the EntitySet 'FootballContainer.People' must have unique primary keys. However, an instance of type 'SportEF.Referee' and an instance of type 'SportEF.Player' both have the same primary key value, 'EntitySet=People;Id=1' (See below).

    Not sure if this is a function of what we have done or if this is a fundamental flaw in my design.  The whole point is that players can be referees and vice versa and that Im using the base entity (person) to store things that are common (like name, address etc).

    Any suggestions?

    Monday, August 22, 2011 3:54 AM
  • Hi the.blacksmith;

    Hum. Can you post a SQL script as can be produced by SQL Server Management Studio so that I can recreate the DB here or better if you have a test db that you can zip up and post somewhere where I can download it so I can see what is going on it would help. I will also need a copy of the edmx files so I have the correct conceptual model layout.

     

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, August 22, 2011 2:09 PM
  • Fernando
    You are prompt and efficient as ever.
     
    OK - here is a shared dropbox link:

    http://db.tt/UcyUhff

    In here I have included a generated sql script which should generate schema AND my test data.

    As you can see the EDMX is VERY basic.  I have also resposted your vb code.
    I still get the same error.
    Good luck.



    Tuesday, August 23, 2011 8:19 AM
  • Hi the.blacksmith;

    This is what I found out running your DB and EDMX and the code. Because a Person can be a Player, Referee, Coach and User all at the same time this TPT or TPH table type will NOT seem to be a good choise for a db design in this case and here is why. When you make the query of People of type Person it will return a record for each typer of Person and in the test case you will receive two records for Brad Huggett whose Primary key is 1. At this point object tracking will throw an exception because it has two records with the same Primary Key for a table of type Person. To show that this is the case you can place the following line of code just after you instantiate the object context:

    context.People.MergeOption = MergeOption.NoTracking

    Now the program will work without throwing an exception because ObjectContext or more correctly Object State Manager will no longer track the entities coming from the query and so there will be no conflict with primary keys. Now this is fine as long as you do not need to update any of the records. If you need to update a record you will need to query for that record with change tracking enabled and then modify the record and SaveChanges. Of course this same issue will occurs if you query for Referee and then query for lets say User with one person having both types because both types share the same Primary Key from Person.

    In this case I would just create the five tables in the DB and define relations between Peron and the other four tables.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by The.Blacksmith Wednesday, August 24, 2011 12:32 PM
    Tuesday, August 23, 2011 7:12 PM
  • BTW are you a pilot?
    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, August 23, 2011 7:14 PM
  • Fernandoo

    Yes I am a pilot.  Used to fly for QANTAS but gave that up some time ago.  Fly for fun now when I have time and can afford it.  My Avatar is none other than the famous Sir Charles Kingsford Smith - first to fly the Pacific from the US to Oz and is a legend in Australian aviation. 

     

    Back to the topic.  I see exactly what you are saying.  I think Im confusing the whole Enity / Object thing with RDBMS theory... before EF I would have done exactly as you had suggested and created 5 tables and then a view for each of Player, Referee etc that joined People to the respective "sub table" to get the representation that I want.  

    To be clear then, this is what you are suggesting in EF for my model... do you concur?

     

     

     


    ____________________________________________________ Regards The.Blacksmith
    Wednesday, August 24, 2011 4:23 AM
  • Hi the.blacksmith;

    Yes something like that should work. Also you may want to think about making some or all the other tables other then Person a collection so that a person can coach on multiple positions or be a part of multiple teams then that would be a collection of player. This is it would look like if they were collections.

    And the following is sample code that can be used on the above schema.

    Dim context As New sporttestEntities()
    
    Dim allPersons = From p In context.People.Include("Players").Include("Coaches").Include("Referees").Include("Users")
             Select p
    
    For Each p In allPersons
      Console.WriteLine(p.LastName & ", " & p.FirstName & " : " & p.eMail)
      For Each t In p.Players
        Console.WriteLine("Player Shirt Number " & t.ShirtNumber)
      Next
      For Each c In p.Coaches
        Console.WriteLine(vbTab & "Team Coach " & c.TeamCoached)
      Next
    Next
    
    Dim allPlayers = From p In context.Players.Include("Person")
             Select p
    
    For Each p In allPlayers
      Console.WriteLine("Player {0}, {1} Email : {2} Shirt Number : {3}", p.Person.LastName, p.Person.FirstName, p.Person.eMail, p.ShirtNumber)
    Next
    
    
    Console.ReadLine()
    

    Note the use of the Include method in the query. Any table that is Included, its data will also be returned in the query results. Be careful when using the Include method because you will be returning all related entities of the entity being returned.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by The.Blacksmith Wednesday, August 24, 2011 12:28 PM
    • Unmarked as answer by The.Blacksmith Wednesday, August 24, 2011 12:32 PM
    Wednesday, August 24, 2011 6:21 AM
  • Good suggestions.

    Yes a person could play in multiple teams but I was thinking that I could introduce a team entity and then set up an appropriate association between team and player...

    Anyway, we have come a long way from where we started and I am extremely grateful.  Thanks for your help. Looks like I need to go back to the drawing board on the model but I think it will be the better for it.

     

    I'll fly solo now for a while and be sure to let you know if I get stuck again.

     

    Thanks again.

     

     

     

     

     

     

     

     


    ____________________________________________________ Regards The.Blacksmith
    • Marked as answer by The.Blacksmith Wednesday, August 24, 2011 12:27 PM
    • Unmarked as answer by The.Blacksmith Wednesday, August 24, 2011 12:28 PM
    Wednesday, August 24, 2011 12:27 PM
  •  

    Well any time you need a co-pilot just let me know.  You have a great day.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, August 25, 2011 3:40 AM