CODEWITHSUNDEEP
linq
Peter Wone
Peter Wone

Reputation: 18765

Finding bounds with LINQ

This code works ok, but I was wondering whether there isn't some way to write it in one LINQ expression so a single pass can be done by the database server instead of realising a result set and then looping through it, which is what my code will produce.

  var logs = from AssetLog log in dc.AssetLogs
             where log.AssetId == assetId && log.Recorded >= start && log.Recorded <= finish
             select log;
  return new GetInteractionBoundsResult()
  {
    N = logs.Max(log => log.Latitude),
    S = logs.Min(log => log.Latitude),
    W = logs.Min(log => log.Longitude),
    E = logs.Max(log => log.Longitude)
  };

So, LINQ gurus, how would you write the above so that it produces more or less this at the database:

SELECT MIN(Latitude) S, MAX(Latitude) N, MIN(Longitude) W, MAX(Longitude) E
FROM ASSETLOG WHERE etc etc

Upvotes: 1

Views: 62

Answers (2)

StriplingWarrior
StriplingWarrior

Reputation: 156524

Sure, just trick your LINQ provider into thinking it's still working with a query until the very end:

  var logs = from asset in dc.Assets
             where asset.AssetId == assetId 
             let g = asset.AssetLogs
                 .Where(log => log.Recorded >= start && log.Recorded <= finish)
             select new GetInteractionBoundsResult
                 {
                     N = g.Max(log => log.Latitude),
                     S = g.Min(log => log.Latitude),
                     W = g.Min(log => log.Longitude),
                     E = g.Max(log => log.Longitude)
                 };
  return logs.Single();

A Group By may perform better than the join that the above query would produce:

  var logs = from log in dc.AssetLogs
             where log.AssetId == assetId &&
                 log.Recorded >= start && log.Recorded <= finish
             group log by log.AssetId into g
             select new GetInteractionBoundsResult
                 {
                     N = g.Max(log => log.Latitude),
                     S = g.Min(log => log.Latitude),
                     W = g.Min(log => log.Longitude),
                     E = g.Max(log => log.Longitude)
                 };
  return logs.Single();

Upvotes: 1

dahlbyk
dahlbyk

Reputation: 77540

It would take a pretty sophisticated LINQ provider, but a subquery may work:

  var res = from asset in dc.Assets
            where log.AssetId == assetId 
            let logs = (from AssetLog log in asset.AssetLogs
                        where log.Recorded >= start && log.Recorded <= finish
                        select log)
            select new GetInteractionBoundsResult()
            {
              N = logs.Max(log => log.Latitude),
              S = logs.Min(log => log.Latitude),
              W = logs.Min(log => log.Longitude),
              E = logs.Max(log => log.Longitude)
            };
  return res.Single();

Upvotes: 0

Related Questions