目前我有一個通過SqlGeography列“geolocatable”的實體,我可以通過表達式使用它來進行過濾和排序。我已經能夠得到點y的距離x內排序最接近(或最遠)y點實體的所有實體。但是,為了返回從實體到y的距離,我必須重新計算應用程序中的距離,因為我還沒有確定如何將距離計算的結果從數據庫實現到IQueryable中的實體。這是一個映射的實體,並且大量的應用程序邏輯圍繞返回的實體類型,因此將其投影到動態對像中並不是這種實現的可行選擇(儘管我理解這將如何工作)。我也嘗試使用從映射實體繼承但未遇到相同問題的未映射對象。基本上,據我所知,我應該能夠定義未映射屬性的getter以在可查詢擴展中分配計算值。如果我修改表示IQueryable的表達式樹但是如何逃避我。我以前用這種方式寫過表達式,但我認為我需要能夠修改現有的select而不僅僅是鏈接一個新的Expression.Call,這對我來說是未開發的領域。
以下代碼應該正確說明問題:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity.Spatial; // from Microsoft.SqlServer.Types (Spatial) NuGet package
using System.Linq;
public class LocatableFoo
{
[Key]
public int Id { get; set; }
public DbGeography Geolocation { get; set; }
[NotMapped]
public double? Distance { get; set; }
}
public class PseudoLocatableFoo : LocatableFoo
{
}
public class LocatableFooConfiguration : EntityTypeConfiguration<LocatableFoo>
{
public LocatableFooConfiguration()
{
this.Property(foo => foo.Id).HasColumnName("id");
this.Property(foo => foo.Geolocation).HasColumnName("geolocation");
}
}
public class ProblemContext : DbContext
{
public DbSet<LocatableFoo> LocatableFoos { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new LocatableFooConfiguration());
base.OnModelCreating(modelBuilder);
}
}
public class Controller
{
public Controller(ProblemContext context) // dependency injection
{
this.Context = context;
}
private ProblemContext Context { get; set; }
/* PROBLEM IN THIS METHOD:
* Do not materialize results (ie ToList) and then calculate distance as is done currently <- double calculation of distance in DB and App I am trying to solve
* Must occur prior to materialization
* Must be assignable to "query" that is to type IQueryable<LocatableFoo>
*/
public IEnumerable<LocatableFoo> GetFoos(decimal latitude, decimal longitude, double distanceLimit)
{
var point = DbGeography.FromText(string.Format("Point({0} {1})", longitude, latitude), 4326); // NOTE! This expects long, lat rather than lat, long.
var query = this.Context.LocatableFoos.AsQueryable();
// apply filtering and sorting as proof that EF can turn this into SQL
query = query.Where(foo => foo.Geolocation.Distance(point) < distanceLimit);
query = query.OrderBy(foo => foo.Geolocation.Distance(point));
//// this isn't allowed because EF doesn't allow projecting to mapped entity
//query = query.Select( foo => new LocatableFoo { Id = foo.Id, Geolocation = foo.Geolocation, Distance = foo.Geolocation.Distance(point) });
//// this isn't allowed because EF doesn't allow projecting to mapped entity and PseudoLocatableFoo is considered mapped since it inherits from LocatableFoo
//query = query.Select( foo => new PseudoLocatableFoo { Id = foo.Id, Geolocation = foo.Geolocation, Distance = foo.Geolocation.Distance(point) });
//// this isn't allowed because we must be able to continue to assign to query, type must remain IQueryable<LocatableFoo>
//query = query.Select( foo => new { Id = foo.Id, Geolocation = foo.Geolocation, Distance = foo.Geolocation.Distance(point) });
// this is what I though might work
query = query.SelectWithDistance(point);
this.Bar(query);
var results = query.ToList(); // run generated SQL
foreach (var result in results) //problematic duplicated calculation
{
result.Distance = result.Geolocation.Distance(point);
}
return results;
}
// fake method representing lots of app logic that relies on knowing the type of IQueryable<T>
private IQueryable<T> Bar<T>(IQueryable<T> foos)
{
if (typeof(T) == typeof(LocatableFoo))
{
return foos;
}
throw new ArgumentOutOfRangeException("foos");
}
}
public static class QueryableExtensions
{
public static IQueryable<T> SelectWithDistance<T>(this IQueryable<T> queryable, DbGeography pointToCalculateDistanceFrom)
{
/* WHAT DO?
* I'm pretty sure I could do some fanciness with Expression.Assign but I'm not sure
* What to get the entity with "distance" set
*/
return queryable;
}
}
Distance
字段在邏輯上不是表的一部分,因為它表示到動態指定點的距離。因此,它不應該是您實體的一部分。
此時,如果您希望在db上計算它,您應該創建一個存儲過程,或者一個TVF(或sg else),它返回您的實體,該距離是擴展的。這樣,您可以將返回類型映射到實體。對我來說這是一個更清晰的設計。
怎麼樣更換線
var results = query.ToList();
同
var results = query
.Select(x => new {Item = x, Distance = x.Geolocation.Distance(point)}
.AsEnumerable() // now you just switch to app execution
.Select(x =>
{
x.Item.Distance = x.Distance; // you don't need to calculate, this should be cheap
return x.Item;
})
.ToList();