Entity Model 4.0

Posted on 2011-10-25
Last Modified: 2012-05-12
I am an Entity Model 4.0 newbie.  I have an entity model setup that was created by modeling an SQL database.  I can use the model's basic capabilities - query tables, update tables, join and select, etc.

Imagine we have the following DB structure.



It is very easy to create a Parent object or a Child object using the model's Entity Object types and poplulate those objects from the DB using a linq query.

What I want is a combined object that has both the Parent and Child info in one object where the parent child relationship is maintained.

One way to do this is as follows:

pubilc class CombinedObject
       public Parent ParentObject;
       public Child Child Object;

CombinedObject object = new CombinedObject()
Parent object.ParentObject = new Parent()
Child object.ChildObject = new Child()

List<object> ParentsAndChildren = new List<object>

Now we can populate ParentsAndChildren with a linq Query joining Child to Parent and looping through the results adding each joined record to the list appropriately.  This will create one ParentsAndChildren list item for each Child record.  The Parent information would be repeated.  You could avoid this by having the ChildObject in inside the class be a list.

Creating this object outside of the Entity Model seems to be repetive.  The model should already have a way to store the combined objects becuase it already understands the Parent/Child relationship.  

Is there a better way to create the combined Parent/Child object.  Imagine an application that is going to read the entire DB into a business object that is structred just like the DB.

Thank You
Question by:RogerWH
    LVL 4

    Expert Comment

    In the Entity Model Designer you can add associations which then appear as properties in the generated code.
    You can then add your parent or child objects using these properties and the parent objects will be created followed by the children automatically on submit.

    These relationships should have been found by the EDM designer when you created the model, if it didn't then the foreign key relationship isn't configured in the database.

    Hope this helps

    LVL 17

    Expert Comment

    In VS entity model design add has many relationship with parent and child. Now if u want all child u can do like
    var childlist=from p in parents where p.parent_id=18 [b]select p.childs;[\b]

    Author Comment

    The entitiy model is complete.  All associations are present.

    Instead of doing a queary that get's all the children for 1 parent, I want to do a queary that reads all Child records AND all Parent records (JOIN) and stores the results in a structured business object that I can then use in my applications.  

    I might want to do something like the following (pseudo code)

    foreach child
           print child name
           print child.parent name

    I recoginize that I could do the following:

    foreach parent
           query children for this parent
           print child name
           print parent name

    However the second method relies on a re-query everytime you want to do that funtion.  In this case the Parent Child db is static and I want to read it all in one time and have it in a structured object.

    Can the Enitity Model objects hold this in some way or do I need to roll my own business object as I described in the post above?

    LVL 4

    Expert Comment

    Can you not do this,

    var parentList = context.Parents.Include("Children").ToList();

    This would give you a IList<Parent> of all parents and all children and would only hit the database once.

    Is this what you mean?

    Author Comment

    Gruff82, Your suggestion does work.  However, to use the objects it appears you have to do someting like this.

    for (int p = 0; p < parentList.Count(); p++)
        string pName = parentList[p].ParentName;

        List<Children> children = new List<Children>();
        children = parentList[p].Children.ToList();

        for (int c = 0; c < children.Count(); c++)
             string cName = children[c].ChildName;

    I can't see how you could interate through all of the children directly and have access to the parent data of each child.  Let's look at a different strategy so we can all understand the ultimate goal better.

    Imagine the following object:

        public class ParentComplete
              pubic Parent pcParent;
              public Child pcChild;

    Then we use this object this way:

    List<ParentComplete> ParentListComplete = new List<ParentComplete>();

    var completeList = (from p in context.parents
                                   join c in context.children on p.ChildID == c.ChildID
                                   select new
                                        parentID = p.ParentID,
                                        parentName = p.ParentName,
                                        childID = c.ChildID,
                                        childName = c.ChildName
    ParentComplete pc = null;

    foreach (var record in completeList)
         pc = new ParentCompete();
         pc.pcParent = new Parent();
         pc.pcChild = new Child();

         pc.pcParent.ParentID = record.ParentID;
         pc.pcParent.ParentName = record.ParentName;
         pc.pcChild.ChildID = record.ChildID;
         pc.pcChild.ChildName = record.ChildName;


    We now have a complete list that includes all parent data for each child.  The parent data is repeated so this is not as effecient as the original solution suggested by Gruff82.

    We can now iterate through the children directly.

    for (x=0; x < ParentListComplete.Count; x++)
        pName = ParentListComplete[x].pclParent.ParentName;
        cID = ParentListComplete[x].pclChild.ChildID;
        cName = ParentListComplete[x].pclChild.ChildName;

    These two different methods create two different ways to navigate through the data.  The first is parent centric and requires a drill down approach with nested loops.  The second is child centric and requires only one loop.  If these are essentially the only two ways to build a complete parent/child object, then I guess the best approach just depends on how you will use the object.  

    So, am I missing a simpler way that combines the simplicity of the first method suggested by Gruff82, but allows you to iterate through the children directly with access to the parent data?  

    You can imagine that if the data model is more complicated the second method can take a bit of work to create.  In my actual case there are 12 tables that will be joined to create the complete record that includes 50 fields.  Creating a custom object is fine, but will be harder to maintain if the DB schema changes.  

    Creating all the nested loops requried to navigate though the pure entity object is also cumbersome.

    Am I missing something?
    LVL 4

    Accepted Solution

    Ah right I think I see now, can't you just switch it to

    var children = context.Children.Include("Parents").ToList();

    foreach (var child in children)
        var pid = child.Parent.Id;  
        var cid = child.Id;
       var pname =;
      var cname = child.Name

    One  thing I couldn't determine is if you have 1 or 2 parents recorded for each child? You would need a intermediate table/entity to model the many to many relationship.


    Author Comment

    Gruff82.  Thank you for your quick responses.  Your new solution is perfect!

    I have connected all 12 tables (Child->Parent->GrandParent->Uncle->Cousin....

    Thanks again, I knew I had to be missing a big piece of now Entities worked. This opens up a treamendous amount of simplity in creating a Data Layer.

    LVL 4

    Expert Comment

    Excellent Roger, glad to be of help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now