c# - How do I rewrite part of an entity framework expression tree for re-use server side? (SQL)

Question

I have the following code below, which is part of a larger expression tree, and it strikes me that I should be able to write a method/expression that can be reused to obtain the Contacts list without all this duplicated code.

Is there a way I can write something like this in each part of the tree:

Contacts = getContactEmailAndPhones(cnt)

or

Contacts = ... select getContactEmailAndPhones(cnt)

and not have to duplicate the code as below?

   Company = com != null
            ? new PM_VC<Company>()
            {
                Vendor = com,
                // Get all the contacts mapped to the vendor
                Contacts = (from m_cc in db.M_CCs
                            join cnt in db.Contacts on m_cc.ContactID equals cnt.ContactID
                            where m_cc.CompanyID == com.CompanyID
                            select new PM_Contact
                            {
                                Contact = cnt,
                                Emails = (from ea in db.EmailAddresses
                                          where ea.ContactID == cnt.ContactID
                                          select ea).ToList(),
                                Phones = (from ph in db.Phones
                                          where ph.ContactID == cnt.ContactID
                                          select ph).ToList(),
                                Primary = m_cc.PrimaryContact
                            }).ToList()
            }
            : null,

   Individual = ivend != null
            ? new PM_VC<Individual>()
            {
                Vendor = ivend,
                // Get all the contacts mapped to the vendor
                Contacts = (from m_vc in db.M_IVCs
                            join cnt in db.Contacts on m_vc.ContactID equals cnt.ContactID
                            where m_vc.IVid == ivend.IVid
                            select new PM_Contact
                            {
                                Contact = cnt,
                                Emails = (from ea in db.EmailAddresses
                                          where ea.ContactID == cnt.ContactID
                                          select ea).ToList(),
                                Phones = (from ph in db.Phones
                                          where ph.ContactID == cnt.ContactID
                                          select ph).ToList(),
                                Primary = m_vc.PrimaryContact
                            }).ToList()
            }
            : null,

   Joint = jvend != null
            ? (from m_ivjv in db.M_IVJVs
               join ijven in db.Individual on m_ivjv.IVid equals ijven.IVid
               where m_ivjv.JointID == jvend.JointID
               select new PM_VC<Individual>()
               {
                   Vendor = ijven,
                   // Get all the contacts mapped to the vendor
                   Contacts = (from m_vc in db.M_IVCs
                               join cnt in db.Contacts on m_vc.ContactID equals cnt.ContactID
                               where m_vc.IVid == ijven.IVid
                               select new PM_Contact
                               {
                                   Contact = cnt,
                                   Emails = (from ea in db.EmailAddresses
                                             where ea.ContactID == cnt.ContactID
                                             select ea).ToList(),
                                   Phones = (from ph in db.Phones
                                             where ph.ContactID == cnt.ContactID
                                             select ph).ToList(),
                                   Primary = m_vc.PrimaryContact
                               }).ToList()
               }).ToList()
            : null,

I presume the easier method is to replace the part after the select (since there are different mapping/join table references to obtain the relevant cnt object. Any suggestions on how to do this so it can be executed server-side, rather than in the client code, would be greatly appreciated!

To clarify, while I could leave the code as-is, I want to use the parts that obtain email and phones in another method to get those details for specific vendor IDs. In this method, it's using a join, and it would be unnecessary duplication and maintenance to have yet another instance.

I am using entity framework core 5 and c#.

question from:https://stackoverflow.com/questions/65887708/how-do-i-rewrite-part-of-an-entity-framework-expression-tree-for-re-use-server-s

Answer 1

Waitting for answers