Help Refactoring Entity Framework 4 Query

I have two entities:

• Location
• Post

It's a 1..* between Location and Post.

Location is abstract, i have many derived entities such as City. I use Table-Per-Type inheritance for my model.

I'm trying to write the following query: (simplified)

• Get Top 20 Cities, and include the "Top Rated Post" (highest rating).

So, Location has a navigational property called Posts.

A thing to keep in mind - i disable lazy loading, hence i must eager load, or perform two queries.

So, here is the query i currently have. Keep in mind, i need to return an ICollection<Location> from this method:

public ICollection<Location> FindTopTwentyLocations()
{
    var results = new List<Location>();

    var cities = locationRepository
                   .Find()
                   .OfType<City>()
                   .Select(x => ne开发者_开发问答w
                    {
                       Location = x,
                       TopPost = x.Posts.OrderByDescending(r => x.Rating).FirstOrDefault()
                    }).Take(20).ToList();

    foreach (var city in cities)
    {
       var aggregatedCity = city.Location;
       aggregatedCity.Posts = new List<Post> { aggregatedCity.TopPost };
       results.Add(city);
    }

    return results;
}

So essentially, im grabbing the first 20 cities, projecting into an anonymous type so i can grab the top post, then looping through that collection of anonymous types to shove the post back into the "City" object, in order to be added to the return type of List<Location>.

• I can't use .Include as that will return all posts
• I don't want to execute 2 queries
• I must use anonymous type projection otherwise it will throw an EF error (cannot translate query)

With those points in mind, is there a better way we can do this? I'm reasonably happy with the var cities query, but i don't really like the looping/copying over of the anonymous type properties to my model entity.

Any ideas?

EDIT

I've also been noticing the projection to the anonymous type is losing the eager loaded Location associations i am retrieving.

E.g

var query = locationRepository.Find().OfType<City>().Include("State").ToList();

works - all "State" associations returned.

but:

var query = locationRepository.Find().OfType<City>().Include("State").Select(x => new {
   Location = x,
   TopPost = x.Posts.OrderByDescending(r => x.Rating).FirstOrDefault()
}).ToList();

Results in all the "State" associations being null.

Bizarre!

Happy to keep this open for a while and see other answers, but i'm sticking with what i have for now.

One point i'd like to mention regarding my EDIT above.

Projecting a eager-loaded query to an anonymous type appears to lose the included association. I have no idea why.

A workaround i used was to include the eager-loaded association in the anonymous type:

var cities = locationRepository
                   .Find()
                   .OfType<City>()
                   .Select(x => new
                    {
                       Location = x,
                       State = x.State, // include association in anon type
                       TopPost = x.Posts.OrderByDescending(r => x.Rating).FirstOrDefault()
                    }).Take(20).ToList();

    foreach (var city in cities)
    {
       var aggregatedCity = city.Location;
       aggregatedCity.State = city.State; // copy anon type association over
       aggregatedCity.Posts = new List<Post> { aggregatedCity.TopPost };
       results.Add(city);
    }

And that seems to work.

In terms of refactoring the code, i don't think the var cities query can be optimized. I tried to move the foreach left-to-right copying into another .Select projection (e.g after the query has been materialized - after the .ToList()), but in order to do that i would have to left-to-right copy ALL of the properties, e.g:

var cities = locationRepository
                   .Find()
                   .OfType<City>()
                   .Select(x => new
                    {
                       Location = x,
                       State = x.State, // include association in anon type
                       TopPost = x.Posts.OrderByDescending(r => x.Rating).FirstOrDefault()
                    }).Take(20).ToList().Select(x => new City
                    {
                       CityName = Location.Name,
                       State = State,
                       // etc etc
                    });

Considering i have 20+ properties on my entities, i do not want to do this. Brings me back to the painful world of Linq-To-Sql and POCO's.

As i said - open to other suggestions.

EDIT

I ended up using a stored procedure here. My code works, but it's 100+ lines. I'd rather have those 100+ lines abstracted away into a stored procedure.

Also, this result is basically read-only (i don't need the entities in the graph - i just get the results, display them and be done with it).

I might be missing something, but can you not do something like this?

var cities = locationRepository 
                   .Find() 
                   .OfType<City>() 
                   .Select(x => 
                    { 
                       x.Posts = x.Posts.OrderByDescending(r => x.Rating).Take(1);
                       return x;
                    }).Take(20).ToList(); 

or if EF complains about altering the Posts property then create a new Location object & add the Post in the select operation.