Query Db from within a Linq Query - C#

Question

I perform a query on my XML file with Linq and when I parse and obtain data from the XML document, I need to go against a DB to populate 2 properties of my object. I can perform 2 calls as my snipet shows, but I would like to make just one call and obtain the result to populate the 2 properties

  XDocument recentOrdersXDoc = GetResults(...);

var q = from c in recentOrdersXDoc.Descendants("prop")
        let handle = c.Element("handle")
        select new ReturnResult()
        {
            ClientTemplateID = (string)c.Element("TemplateID"),
            Handle = resultref != null ? (string)resultref.Attribute("handle") : null,
            ClientID = DataContext.GetClientID((string)c.Element("TemplateID")), 
            ClientName = DataContext.GetClientName((string)c.Element("TemplateID")),

        };

To populate ClientID and ModifiedDate I need to make 2 calls. There is a table called Clients which has these 2 columns, ClientID and ClientName. Also can i access ClientTemplateID property when I need to pass it as a param in GetClientID and GetClientName, as in my code above I have to obbtain the result from the XDocument.

Answer 1

How about something like:

var q = from c in recentOrdersXDoc.Descendants("prop")
        let handle = c.Element("handle")
        let clientTemplateID = (string)c.Element("TemplateID")
        let client = DataContext.Clients
            .Where(x=>x.ClientTemplateID == clientTemplateID)
            .Select(x=>new {x.ClientID, x.ClientName}).Single()
        select new ReturnResult()
        {
            ClientTemplateID = clientTemplateID,
            Handle = resultref != null ?
                   (string)resultref.Attribute("handle") : null,
            ClientID = client.ClientID,
            ClientName = client.ClientName
        };

This still only reads the two columns you need (I had to make some assumptions on names, though).