Example for LINQ to Entities query with multi level relationship (grandparent / grandchild records)

Posted on 2008-11-14
Last Modified: 2013-11-11
Hi All,
Hoping there's a good LINQ coder that can help me out here. My database has a string of related tables where table B has a foreign key to table A and table C has foreign key to table B and table D has foreign key to table C. I need to write a LINQ to Entities query that looks something like:

var query = from d in entities.D where d.c.b.a.primarykey = x select d.textfield;

I don't seem to have grip on how to reach that far through the relationships to filter on a value.

Question by:danburyit
    LVL 62

    Expert Comment

    by:Fernando Soto
    Can you provide a UML type diagram of how the tables are set up Primary Key / Foreign Key and Relationships to see if I may be able to help you.
    LVL 1

    Accepted Solution

    I found a solution. (I'll post in case it helps anyone else. I'm just starting to get the hang of this LINQ stuff.) I changed object names to make it simple and generic (and match my question.)

    Just to reiterate a bit: A table has a child table B (B has foreign keys to A). B table has a child table C. C has a child table D. (It's just a 4 table parent/child heirarchy chain.) I need to return a distinct list of D.PrimaryKey and D.TextField values where A.PrimaryKey = x.

    (Thanks for looking)
                using (myEntities ent = new myEntities())
                    ObjectQuery<Dtable> dTable = ent.Dtable;
                    ObjectQuery<Ctable> cTable = ent.Ctable;
                    var query = (from d in dTable
                                 from c in cTable
                                 where d.DprimaryKey == c.d.DprimaryKey &&
                                 c.b.a.AprimaryKey == x &&
                                 d.TextField.Length > 0
                                 select new { Key = d.DprimaryKey, Value = d.TextField }).Distinct();

    Open in new window


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In my previous two articles we discussed Binary Serialization ( and XML Serialization ( In this article we will try to know more about SOAP (Simple Object Acces…
    Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    733 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

    24 Experts available now in Live!

    Get 1:1 Help Now