Passing a List of DBGeometry to retrieve points contained from SQL

Question

I have a dataset on my SQL server that contains a few million Geography Points that I want to retrieve based on a grid of DBGeography rectangles I create on the client side. Basically I want to pass in a c# List of DBGeography , and my server would hand back the list of points that fit into those geometries, along with the DBGeography they fall into. I am currently querying for each individual rectangle, causing a lot of queries if my map I'm pulling is divided into a large number of tiles. Obviously the performance suffers.

I am currently using the code below.

var pointsInRectangle = (from p in MyTable.Points
                                       where MyRectangle.Intersects(p.Location)
                                       select p).ToList();

Question: How would I alter it to pass in a List of DBGeography Rectangles and get the corresponding data back?

MyRectangle is a DBGeography I create in C# and Location is the point geography in my SQL table. This code currently works but only for one cell at a time. I tried a few things but to no avail. Entity Framework is not my specialty, but I also wouldn't know how in straight SQL (though I'm fine using SQL if needed for a solution).

Also I tried pulling all the points (from the larger outer rectangle) to the client, and then processing on the client side to subdivide it into a grid. The performance is very slow even though the initial query is almost instant (probably because of indexes).

You can create a table type (this is a structure in SQL that you create to hold data in a table format and you can pass that to a stored procedure. Then in SQL treat that table type variable as a table in your logic for your seelct — Brad, Apr 23 '18 at 19:03
Maybe combine all your rectangles with Union (method of DbGeography)? Then you will need just one query. — Evk, Apr 23 '18 at 19:16

Brad · Accepted Answer · 2018-04-23T19:15:28.287

You would have to do a couple things. Create a table type in SQL (code below), create a Stored Procedure to accept the Table Type and select from that table type (select example below), and then in your C# code populate the SQL type and pass it to a SQLCommand calling the Stored Procedure you created. (populating the Table Type variable is below, not the rest of the SQLCommand.

// create table type in SQL (this is an example in case you need to pass more then 1 field/value
CREATE TYPE [dbo].[NameValuePairTable] AS TABLE(
    [Name] [NVARCHAR](MAX) NULL,
    [Value] [NVARCHAR](MAX) NULL
)
GO

create SP here with namevalue pair paramater

Create PROCEDURE [dbo].[SPPassingTableType]         
    @NameValuePairAnswers as dbo.NameValuePairTable READONLY    
AS
BEGIN
    -- do your SQL logic stuff here

    -- this will select from the table type you passed to the SP
    SELECT * 
    FROM @NameValuePairAnswers  
END

in your c# code you could do this. NOTE: This is not FULL C# code, this is just showing how to handle the table type variable and populate it. You still have to generate your SQL Command and pass this table type to the SQL Command

// now build out the namevalue pair paramater
                SqlParameter TableData = new SqlParameter();
                TableData.ParameterName = "@NameValuePairAnswers";
                TableData.TypeName = "dbo.NameValuePairTable";
                TableData.SqlDbType = SqlDbType.Structured;
                TableData.Value = dtFormResultsToSave;

Where the dtFormResultsToSave is a datatable that matches your Table Type in SQL above.

Passing a List of DBGeometry to retrieve points contained from SQL

1 Answers1