How do i rewrite this code to avoid the "Linq to entities Does not recognize method" error

Question

I have the following method on a MVC 4 Web API controller.

public JsonResult GetJourney(List<string> assetIds, DateTime start, DateTime finish)
{
    var journey = new List<JourneyPoint>();

    var startUTC = start.ToUniversalTime();
    var finishUTC = finish.ToUniversalTime();

    foreach (var assetId in assetIds)
    {
        string id = assetId;

        var events = _eventRepo.GetAll().Where(evt => evt.EventTypeId == 0 && evt.TimeStamp > startUTC && evt.TimeStamp < finishUTC && evt.AssetId == id);

        foreach (var @event in events)
        {
            var myGps = _gpsRepo.GetAll().FirstOrDefault(gps => gps.Id == @event.GPSId);

            if (myGps != null)
            {
                var myJourneyPoint = new JourneyPoint
                {
                    Id = @event.Id,
                    AssetId = @event.AssetId,
                    TimeStamp = @event.TimeStamp.ToUnixEpocSeconds(),
                    Lat = myGps.Lat,
                    Long = myGps.Long,
                    Speed = myGps.Speed,
                    Elevation = myGps.Elevation,
                    Heading = myGps.Head
                };
                journey.Add(myJourneyPoint);
            }
        }
    }

    var jsonJourney = Json(journey.OrderBy(ju => ju.TimeStamp).ToList());
    jsonJourney.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
    return jsonJourney;
}

With the repo methods as:

public IQueryable<Event> GetAll()
{
    var db = new CasLogEntities();
    return db.Event;
}

public IQueryable<GPS> GetAll()
{
    var db = new CasLogEntities();
    return db.GPS;
}

This all works great, and the use of the repositories allowed me to write a test suite for the controller code.

Although I suspect that this code is inefficient, in that there are multiple calls to the data base and a lot of the computational work is being done by .Net and not the sql server.

Re-sharper suggested that I could turn the for each loops into linq statements, which i did and ended up with the following code.

public JsonResult GetJourney(List<string> assetIds, DateTime start, DateTime finish)
{
    var journey = new List<JourneyPoint>();

    var startUTC = start.ToUniversalTime();
    var finishUTC = finish.ToUniversalTime();

    foreach (var assetId in assetIds)
    {
        string id = assetId;

        var events = _eventRepo.GetAll().Where(evt => evt.EventTypeId == 0 && evt.TimeStamp > startUTC && evt.TimeStamp < finishUTC && evt.AssetId == id);

        journey.AddRange(from @event in events
                         let myGps = _gpsRepo.GetAll().FirstOrDefault(gps => gps.Id == @event.GPSId)
                         where myGps != null
                         select new JourneyPoint
                         {
                             Id = @event.Id,
                             AssetId = @event.AssetId,
                             TimeStamp = @event.TimeStamp.ToUnixEpocSeconds(),
                             Lat = myGps.Lat,
                             Long = myGps.Long,
                             Speed = myGps.Speed,
                             Elevation = myGps.Elevation,
                             Heading = myGps.Head
                         });
    }

    var jsonJourney = Json(journey.OrderBy(ju => ju.TimeStamp).ToList());
    jsonJourney.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
    return jsonJourney;
}

However I get an error when I run this code:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[CasWeb.Models.DataContext.GPS] GetAll()' method, and this method cannot be translated into a store expression.

I understand this is because LINQ To Entities is trying to map "GetAll()" method to sql and It can't.

My question is: how can I rewrite my code to avoid this error and have as much work as possible performed by the SQL server? and if possible maintain the repository pattern to allow for testing?

Answer 1

As @Rup suggested in the comment, the following should avoid the error.

    journey.AddRange(from @event in events
                 join gps in _gpsRepo.GetAll() on gps.Id == @event.GPSId
                 select new JourneyPoint
                 {
                     Id = @event.Id,
                     AssetId = @event.AssetId,
                     TimeStamp = @event.TimeStamp.ToUnixEpocSeconds(),
                     Lat = gps.Lat,
                     Long = gps.Long,
                     Speed = gps.Speed,
                     Elevation = gps.Elevation,
                     Heading = gps.Head
                 });