I am using the DbGeography type in Entity Framework 6.1 with SQL Server 2014, I need to find the nearest location for possibly 100,000 entities, it is slow when doing it one by one like below, is it possible to use a similar method as I have below but do them all at once?
C#:
public List<GeocodableEntity> LinkToRoadNodes(List<GeocodableEntity> entities)
{
foreach (var entity in entities)
{
var nearestLocation = GetNearestLocation(entity.Latitude, entity.Longitude, DEFAULT_ROAD_NODE_LINK_RADIUS);
// update entity with values from nearestLocation
}
return entities;
}
private GeoLocation GetNearestLocation(float latitude, float longitude, double searchRadius)
{
var sourcePoint = Helper.CreateDbGeographyPoint(latitude, longitude);
return Locations.Where(x => x.Point.Distance(sourcePoint) < searchRadius)
.OrderBy(x => x.Point.Distance(sourcePoint))
.FirstOrDefault();
}
Reason I say this is because prior to this, I was doing a similar thing using a stored procedure.
Similar in SQL:
ALTER PROCEDURE [dbo].[GetNearestLocationNodesByCoordinates]
@CoordinateList dbo.CoordinateListWithRefId READONLY
AS
BEGIN
WITH InputCTE AS
(
SELECT RefID, geography::Point(Latitude, Longitude, 4326) AS Point
FROM @CoordinateList
)
SELECT x.RefID, x.NodeId, x.Latitude, x.Longitude, x.LocationTypeId
FROM (SELECT I.RefID as 'RefID',
L.NodeId,
L.Point.Lat as 'Latitude',
L.Point.Long as 'Longitude',
L.LocationTypeId,
ROW_NUMBER() OVER (PARTITION BY I.RefID ORDER BY L.Point.STDistance(I.Point)) AS Ranking
FROM InputCTE AS I
JOIN Location AS L
ON L.Point.STDistance(I.Point) <= 5000) AS x WHERE Ranking = 1
END
Where @CoordinateList is :
CREATE TYPE [dbo].[CoordinateListWithRefId] AS TABLE(
[RefID] [int] NOT NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL
)
I would like to do the same thing if possible in C# code without touching stored procedures or typing a SQL query literal.
Parallel Foreach :
An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code Additional information: The context cannot be used while the model is being created. This exception may be thrown if the context is used inside the OnModelCreating method or if the same context instance is accessed by multiple threads concurrently. Note that instance members of DbContext and related classes are not guaranteed to be thread safe.
