Databases
StackOverflow2013
Postsdbo
POLinq to Entities returning all outer entities and one or none associated entities
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinq to Entities returning all outer entities and one or none associated entities
    text
    copied!<p>I'm using LinqPad with and Entity Framework 4 model and MS SQL 2008 database. </p> <p>Suppose we have a Customer entity that has an association to Purchase with a foreign key to Customer.CustomerID.</p> <p>The following linq produces an <code>ObjectQuery&lt;Customer&gt;</code> with <code>Purchases</code> association of <code>EntityCollection&lt;Purchase&gt;</code>.</p> <pre><code>from c in Customers.Include("Purchases") select c </code></pre> <p>Now I want to get <em>all</em> customers with only the most recent purchase. If there is no purchase then I want the <code>Purchases</code> collection to be empty.</p> <p>I need something like the following, but that maintains the entity and association. I want to get <em>all</em> the customers with a collection limited to 0 or 1 purchases.</p> <pre><code>from c in Customers from p in c.Purchases.Where(p =&gt; p.PurchaseDate == c.Purchases.Max(m =&gt; m.PurchaseDate).DefaultIfEmpty() select new { CustomerID = c.CustomerID, PurchaseID = (int?)p.PurchaseID } </code></pre> <p>In my service I return a <code>List&lt;Customer&gt;</code>, so I think I need to maintain the <code>Customer</code> entity and <code>Purchases</code> association in the return from the linq query.</p> <p>Use case:</p> <pre><code>var customers = CustomerService.GetCustomersAndMostRecentOrder(); foreach (Customer c in customers) { Console.WriteLine(c.Lastname + ":" + c.Purchases.Count() == 0 ? "None" : c.Purchases[0].PurchaseOrder); } </code></pre> <p>Thanks for your insight.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload