How do I get a spatial value from EF Core's FromSqlRaw

Recently I’ve encountered a problem where I just had to use FromSqlRaw to my db to execute the function of one of Postgresql extensions. This function returns a single entry, and I want to get value of this entry somehow. However, for some reasons I can’t do it this way:

var buffer = DataContext.LayerDatas.FromSqlRaw($"select ST_Buffer(ld."Geom", 100, 'join=mitre') " +
            $"from public."Layers" ld where ld."LId" = {layerId} and ld."OId" = {objectId}").FirstOrDefault();

What’s the propper way of getting calculated value from SqlRaw in order to use it in my code?

Edit:
When I execute query through DBeaver it gives me the following thing:

And I want to get this inside of my C# code.

But the code inside c# app gives me the following exception:

42601 syntax error (at or near "d"),

and it shows my query as:

{select ld."Geom", ST_Buffer(ld."Geom", 100, 'join=mitre') from public."Layers" ld where ld."LId" = f21e400c-9e6d-4c28-b14c-1f0ced5b6ebb and ld."OId" = 3126}

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You used string interpolation to generate the query string which resulted in an invalid query.

... where ld."LId" = f21e400c-9e6d-4c28-b14c-1f0ced5b6ebb

Instead of a parameterized query, the code created a SQL string with injected values that resulted in an invalid query.

FromSqlRaw works with normal strings and explicitly specified parameters. In fact, the docs have a pretty big warning about this.

Using parameters with FromSqlRaw

To use FromSqlRaw, interpolation must not be used. The parameters can be passed by position:

var sql="select ST_Buffer(ld.Geom, 100, 'join=mitre') 
from public.Layers ld 
where ld.LId = {0} and ld.OId = {1}";

var buffer = DataContext.LayerDatas.FromSqlInterpolated(sql,layerId,objectId)
                        .FirstOrDefault();

C# allows multiline strings so there’s no need to use concatenation to make the query readable.

Using interpolation with FromSqlInterpolated

If you want to use interpolation to pass parameters you need FromSqlInterpolated :

var buffer = DataContext.LayerDatas.FromSqlInterpolated(
$"select ST_Buffer(ld.Geom, 100, 'join=mitre') 
from public.Layers ld 
where ld.LId = {layerId} and ld.OId = {objectId}"
).FirstOrDefault();

Notice that the string is one long string without concatenations. This produces the FormattableString class expected by FromSqlInterpolated.

Reading GIS types

ST_Buffer is a standard GIS function used by PostgreSQL and MySQL which returns a spatial type. In SQL Server it’s STBuffer.

Spatial types are supported in EF Core through the NetTopologySuite package and the database-specific packages mentioned in the docs.

For PostgreSQL the correct package is Npgsql.EntityFrameworkCore.PostgreSQL.NetTopologySuite. Its use is explained in Spatial Mapping with NetTopologySuite

To use it with EF Core, after installing the correct packages, it needs to be registered when configuring the DbContext, eg :

options.UseSqlServer(
    @"Data Source=(localdb)MSSQLLocalDB;Initial Catalog=WideWorldImporters",
    x => x.UseNetTopologySuite());

After that, spatial types and properties can be used the same as other types :

// Find the nearest city
var nearestCity = db.Cities
    .OrderBy(c => c.Location.Distance(currentLocation))
    .FirstOrDefault();

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating