converting linq to entities to esql

Question

I am struggling to convert the following to esql

var qry1 = from ev in db.Events

                             group ev by new { ev.State, ev.City } into g

                              where g.Count() > 1

                             select new

                             {

                                 State = g.Key.State,

                                 City = g.Key.City,

                                 Events = g

                             };

Can anyone help me with the above.

Zeeshan

Answer 1

Ok the best i could write was this

string esql = @"select ev.State,ev.City,

                                 (select value e 

                                  from Events as e 

                                  where e.State = ev.State and e.City = ev.City) as Events

                                from Events as ev

                               group by ev.State,ev.City

                               having count(ev.EventId) > 1";

which looks really bad compared to linq to entities. Is there a more cleaner query that i can write?

Zeeshan

Answer 2

For 3.5 Sp1 the above syntax is correct.

In 4.0 you can try this:

select
 ev.State,
 ev.City,
 GroupPartition(ev) as Events
from Events as ev
group by ev.State,ev.City
having count(ev.EventId) > 1

---

This posting is provided "AS IS" with no warranties.

Answer 3

wow u gave an answer that's yet to be found anywhere on the web.

thks