Entity framework: How to reduce database hits?

So I have this query in my repository (also using Unit of Work pattern) which uses eager loading to make one hit to the database:

from g in _context.Games.Include(pg => pg.PreviousGame).Include(go => go.GameObjects)
where EntityFunctions.DiffMilliseconds(DateTime.Now, g.EndDate) > 0
    && g.GameTypeId == (int)GameTypes.Lottery
    && g.GameStatusId == (int)GameStatues.Open
select new LotteryModel
{
    EndDate = g.EndDate,
    GameId = g.Id,
    PreviousGameEndDate = g.PreviousGame.EndDate,
    PreviousGameId = g.PreviousGameId.HasValue ? g.PreviousGameId.Value : 0,
    PreviousGameStartDate = g.PreviousGame.StartDate,
    PreviousWinningObjectCount = g.PreviousGame.GameObjects.Select(go => go.Object.Count).FirstOrDefault(),
    PreviousWinningObjectExternalVideoId = g.PreviousGame.GameObjects.Select(go => go.Object.Video.ExternalVideoId).FirstOrDefault(),
    PreviousWinningObjectName = g.PreviousGame.GameObjects.Select(go => go.Object.Video.Name).FirstOrDefault(),
    StartDate = g.StartDate,
    WinningObjectCount = g.GameObjects.Select(go => go.Object.Count).FirstOrDefault(),
    WinningObjectExternalVideoId = g.GameObjects.Select(go => go.Object.Video.ExternalVideoId).FirstOrDefault(),
    WinningObjectName = g.GameObjects.Select(go => go.Object.Video.Name).FirstOrDefault()
};

However I'm reluctant to use this because I now have to create a separate LotteryModel object to return up throughout my other layers.

I would like to be able to return an entity of type "Game" which has all of the navigational methods to all of my other data (PreviousGame, GameObjects, etc) and then map the needed properties to my flat view model, but when I do this it seems to only lazy load the objects and then I have the additional hits to the DB.

Or do I have this wrong and whenever I need to return heirarchical data I should return it through my开发者_运维知识库 LINQ query in the select portion?

My basic goal is to reduce the hits to the DB.

---

I don't really understand the problem. You return your Games object and you can access the properties and subobjects off it. Your use of the Include() method tells it to load what you need, and not lazy load it.

Make sure you return a single object via a .First, .FirstOrDefault, .Single, .SingleOrDefault, or similar methods.

---

I ended up with this query (FYI I'm using the System.Data.Objects namespace for the Include extension):

(from g in _context.Games.Include(pg => pg.PreviousGame.GameObjects.Select(o => o.Object.Video))
    .Include(go => go.GameObjects.Select(o => o.Object.Video))
where EntityFunctions.DiffMilliseconds(DateTime.Now, g.EndDate) > 0
    && g.GameTypeId == (int)GameTypes.Lottery
    && g.GameStatusId == (int)GameStatues.Open
select g).FirstOrDefault();

I guess I just needed to include more of the heirarchy and didn't know I could use Select() in the Include() function!