I have a SQL Server spatial column (Specifically a geometry).

For indexing purposes and to avoid re-projection I have set the column to require the STrid to be 3857 and this is checked on insert.

But when saving with LLBLGen its passing the value as a point string 'POINT( x, y )' which is causing it to materialize into the wrong (default) coordinate system and fail the check.

Realistically we would want to be using something like: geometry::STPointFromText('POINT( x, y)', 3857)

Is there a magic setting I'm missing in the designer, or when creating the point in c#:

Microsoft.SqlServer.Types.SqlGeometry.Point(x, y, 3857);

that would have caused the coordinate system identifier to also be passed in?

The geometry types are passed as 'UDT' so user defined type and these end up as a string value by calling ToString() on the value (in this case a SqlGeometry object). In our tests we use toInsert.GeometryCol = SqlGeometry.STGeomFromText(new SqlChars(new SqlString("LINESTRING (100 100, 20 180, 180 180)")), 0);. You can replace LINESTRING() with POINT() of course and specify an srid.

If you want to emit a specific piece of SQL instead, you can specify it as an expression on the field. See the example below which specifies a CONVERT expression on the value before it's inserted into the column.

[Test]
public void InsertWithExpressionTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var dummy = EntityCreator.CreateNewAddress(2);
        Assert.IsTrue(adapter.SaveEntity(dummy));
        var addressCount = adapter.GetDbCount(new EntityCollection<AddressEntity>(), null);
        var address = EntityCreator.CreateNewAddress(1);
        address.Fields[(int)AddressFieldIndex.StreetName].ExpressionToApply =
                new Expression(
                        new DbFunctionCall("CONVERT(VARCHAR(50), {0})", new object[] { new ScalarQueryExpression(AddressFields.AddressId.Count()) }), ExOp.Add, address.City);
        address.StreetName = string.Empty;
        Assert.IsTrue(adapter.SaveEntity(address, true));
        Assert.AreEqual((addressCount) + address.City, address.StreetName);
    }
}

I think I understand the specific piece of SQL explanation which is going to come in handy.

Can you point out the tests?

In my code I have:

// Parse the incoming geometry
geometryToSave = Wkx.Geometry.Deserialize<Wkx.EwktSerializer>(dataItem.Change.Geometry!);
            
Microsoft.SqlServer.Types.SqlGeometry sqlGeometry = Microsoft.SqlServer.Types.SqlGeometry.STGeomFromText(
    new System.Data.SqlTypes.SqlChars(geometryToSave.SerializeString<Wkx.WktSerializer>()), geometryToSave.Srid ?? 3857);
entity.Geometry = sqlGeometry;

// And then later
await Adapter.SaveEntityAsync(entity);

The problem of course is that when I do that the SQL it actually generates ends up: <Simulated, not copy/pasted>

UPDATE Spatial
Set Geometry = @p1
-- other fields cut

@p1 = 'LINESTRING (100 100, 20 180, 180 180)'

Which is tossing away the srid. And since my column has a check constraint on the Srid the update or insert fails. Due to the check the only srid that works is 0.

Hmm, it indeed is dropped somewhere.

[Test]
public void InsertOfSpatialValueTest()
{
    var toInsert = new GeoTableEntity();
    toInsert.Id = 1;
    toInsert.Name = "Foo";
    toInsert.GeometryCol = SqlGeometry.STGeomFromText(new SqlChars(new SqlString("LINESTRING (100 100, 20 180, 180 180)")), 0);
    toInsert.GeographyCol = SqlGeography.STGeomFromText(new SqlChars(new SqlString("POINT(55.9271035250276 -3.29431266523898)")), 4931);
    using(var adapter = new DataAccessAdapter())
    {
        try
        {
            Assert.IsTrue(adapter.SaveEntity(toInsert, true));
            // fetch it back.
            var inserted = adapter.FetchNewEntity<GeoTableEntity>(new RelationPredicateBucket(GeoTableFields.Id == 1));
            Assert.AreEqual(1, inserted.Id);
            Assert.AreEqual(toInsert.GeometryCol.ToString(), inserted.GeometryCol.ToString());
            Assert.AreEqual(toInsert.GeographyCol.ToString(), inserted.GeographyCol.ToString());
            
            // test in DTO projection too
            var metaData = new LinqMetaData(adapter);
            var results = metaData.GeoTable.Where(g => g.Id == 1).ProjectToGeoTable().ToList();
            Assert.AreEqual(1, results.Count);
            var dto = results[0];
            Assert.AreEqual(1, dto.Id);
            Assert.AreEqual(toInsert.GeometryCol.ToString(), dto.GeometryCol.ToString());
            Assert.AreEqual(toInsert.GeographyCol.ToString(), dto.GeographyCol.ToString());
        }
        finally
        {
            adapter.DeleteEntity(toInsert);
        }
    }
}

However upon close examination, the string variant doesn't contain the SRID. There are a few weird things going on. If I fill in your SRID in the test, I get an exception that the sys table doesn't contain the SRID:

System.ArgumentException : 24204: The spatial reference identifier (SRID) is not valid. The specified SRID must match one of the supported SRIDs displayed in the sys.spatial_reference_systems catalog view.
   at Microsoft.SqlServer.Types.SqlGeography.set_Srid(Int32 value)
   at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

however it's not going to the db afaik, at least not over ADO.NET

Then, if I fill in a random SRID from the sys.spatial_reference_systems catalog view, and insert the row, the SRID changes in the column of the entity *that's inserted* after the query has been created! So after the insert, `toInsert.GeographyCol.SRID` is 4326, the default. But... the value isn't changed anywhere. This is due to the refetch after the insert, so it fetches the default back.

Tbh, I haven't found any other ways to insert the value other than using the full string representation with convert from the SRID embedded in the string.

Looking into what's going on with the SRID as it shouldn't change regardless.

I have it working by using code like this: https://stackoverflow.com/a/30710449/44991, using reflection and dirty type checks. I can't change the mapping data, but as this is sql server specific, I can fall back to sql server specific types and names.

Will optimize it and push a hotfix build for you to test (so it should be transparent, no need for expressions on fields for inserts for you)

Please see the new 5.12.1 hotfix to fix this issue. You don't have to change your code, it should work transparently for you. See our updated test which now works ok:

[Test]
public void InsertOfSpatialValueTest()
{
    var toInsert = new GeoTableEntity();
    toInsert.Id = 1;
    toInsert.Name = "Foo";
    toInsert.GeometryCol = SqlGeometry.STGeomFromText(new SqlChars(new SqlString("LINESTRING (100 100, 20 180, 180 180)")), 0);
    toInsert.GeographyCol = SqlGeography.STGeomFromText(new SqlChars(new SqlString("POINT(55.9271035250276 -3.29431266523898)")), 4931);
    using(var adapter = new DataAccessAdapter())
    {
        try
        {
            Assert.IsTrue(adapter.SaveEntity(toInsert, false));
            toInsert.Fields.State = EntityState.Fetched;
            // fetch it back.
            var inserted = adapter.FetchNewEntity<GeoTableEntity>(new RelationPredicateBucket(GeoTableFields.Id == 1));
            Assert.AreEqual(1, inserted.Id);
            Assert.AreEqual(toInsert.GeometryCol.ToString(), inserted.GeometryCol.ToString());
            Assert.AreEqual(toInsert.GeographyCol.ToString(), inserted.GeographyCol.ToString());
            Assert.AreEqual(toInsert.GeographyCol.STSrid, inserted.GeographyCol.STSrid);
            
            // test in DTO projection too
            var metaData = new LinqMetaData(adapter);
            var results = metaData.GeoTable.Where(g => g.Id == 1).ProjectToGeoTable().ToList();
            Assert.AreEqual(1, results.Count);
            var dto = results[0];
            Assert.AreEqual(1, dto.Id);
            Assert.AreEqual(toInsert.GeometryCol.ToString(), dto.GeometryCol.ToString());
            Assert.AreEqual(toInsert.GeographyCol.ToString(), dto.GeographyCol.ToString());
            Assert.AreEqual(toInsert.GeographyCol.STSrid, dto.GeographyCol.STSrid);
        }
        finally
        {
            adapter.DeleteEntity(toInsert);
        }
    }
}

I've finally managed to get everything in my project updated and this works wonderfully. Thanks!