Databases
StackOverflow2013
Postsdbo
POUnknown column error using Entity Framework and LINQ
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUnknown column error using Entity Framework and LINQ
    text
    copied!<p>I started with EF yesterday and I am in trouble to transform this simple query into EF sintax</p> <p>Translate:</p> <pre><code>select a.city from offer o, address a, offer_address oa where o.identifier = oa.offeridentifier and a.identifier = oa.addressidentifier group by a.city order by count(*) desc </code></pre> <p>Into:</p> <pre><code>var cities = (from o in db.offer from a in db.address from oa in db.offer_address where (o.identifier == oa.offeridentifier &amp;&amp; a.identifier == oa.addressidentifier) group a by a.city into c select new { quantity = c.Count(), city = c.Key }).OrderByDescending(a =&gt; a.quantity).Select(a =&gt; a.city); var cityCollection = new List&lt;string&gt;(); foreach (var city in cities) cityCollection.Add(city.ToString()); </code></pre> <p>I have tried withou success</p> <pre><code>var cities = (from oa in db.offer_address from of in db.offer.Where(x =&gt; x.identifier == oa.identifier) from ad in db.address.Where(y =&gt; y.identifier == oa.offeridentifier).AsEnumerable() group ad.city by new { ad.city } into g select new { quantity = g.Count(), city = g.Key }).OrderByDescending(a =&gt; a.quantity); </code></pre> <p>The problem occurs when try to get inside the first loop!</p> <pre><code>Unknown column 'GroupBy1.K1' in 'field list'` Line 55: foreach (var city in cities)` </code></pre> <p>With the second case:</p> <p><code>Can't group on 'A1'</code></p> <p><strong>UPDATE</strong></p> <p>This code works, but its not i need</p> <pre><code>var cities = ( from of in db.offer from ad in db.address from oa in db.offer_address where (of.identifier == oa.offeridentifier &amp;&amp; ad.identifier == oa.addressidentifier) group ad.city by new { ad.city } into g select new { quantity = g.Count() }).OrderByDescending(a =&gt; a.quantity) </code></pre> <p>Or</p> <pre><code>var cities = ( from of in db.offer from ad in db.address from oa in db.offer_address where (of.identifier == oa.offeridentifier &amp;&amp; ad.identifier == oa.addressidentifier) group ad.city by new { ad.city } into g select new { city = g.Key }); </code></pre>
 

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