Effort does not support the SQL standard type Geography (KeyNotFoundException)

[boylec]

Throws a KeyNotFoundException when creating a context.

(reference this post http://forum.aspnetboilerplate.com/viewtopic.php?p=2713)

[jimmymain]

I am having the same issue with DbGeography. It would be nice to have support, or a workaround.

[ayangassetic]

I have the same issue. There is an existing ticket raised in the codeplex https://effort.codeplex.com/workitem/700

[francesco-scullino]

+1. It would be very important for me to test entities with DbGeography type.

[n1k1t0ss]

Guys, I tried to analyze this error, and found, that we have to add some lines to Provider\EffortProviderManifest.xml to create new property. This lines should something like this: <Type Name="geography" PrimitiveTypeKind="Geography"> <FacetDescriptions> <SRID DefaultValue="2147483647" Constant="false" Minimum="0" Maximum="2147483647"/> <IsStrict DefaultValue="false" Constant="false"/> </FacetDescriptions> </Type> However, that is not the end, I still getting another exception: System.ArgumentException : The type of 'Point' property is a reference type. parametr name: TEntity Result StackTrace:
in NMemory.Tables.Table2.VerifyType() in NMemory.Tables.Table2..ctor(IDatabase database, IKeyInfo2 primaryKey, IdentitySpecification1 identitySpecification) in NMemory.Tables.DefaultTable2..ctor(IDatabase database, IKeyInfo2 primaryKey, IdentitySpecification1 identitySpecification) in Effort.Internal.DbManagement.Engine.ExtendedTable2..ctor(IDatabase database, IKeyInfo2 primaryKey, IdentitySpecification1 identity) in Effort.Internal.DbManagement.Engine.ExtendedTableService.CreateTable[TEntity,TPrimaryKey](IKeyInfo2 primaryKey, IdentitySpecification1 identitySpecification, IDatabase database) in NMemory.Tables.TableCollection.Create[TEntity,TPrimaryKey](IKeyInfo2 primaryKey, IdentitySpecification1 identitySpecification) in Effort.Internal.Common.DatabaseReflectionHelper.WrapperMethods.CreateTable[TEntity,TPrimaryKey](Database database, IKeyInfo2 primaryKeyInfo, Expression1 identity, Object[] constraintFactories)

Any ideas how to solve this are greatly appreciated. Upd. "Point" is the name of my db column, that use geography type

[egmcdonald]

+1 - DbGeography is essential to my project, and accessing a live database for testing is not an option here.

[jimmymain]

I have worked around this by adding the following code to OnModelCreating. It just ignores storing the property, vaguely useful if you want to write unit tests involving the other fields on your entity, rather than having no tests.

// this horrible hack stops the geolocation from being saved in effort.
// https://github.com/tamasflamich/effort/issues/28
if (this.Database.Connection.GetType().Name.StartsWith("Effort", StringComparison.Ordinal))
    modelBuilder.Entity<Building>().Ignore(_ => _.Geolocation);

[lukkia91]

+1

[dlazzarino]

+1

[AntonioDefinaISMB]

+1

[manuelgaetani]

+1

[alexmaie]

+1

[alexmaie]

Is anybody of the Effort team working on this? If not I would start ( try at least :) ) to fix the issue.

[tamasflamich]

Hi @alexmaie - As I am no longer working with .NET technologies on a day to day basis, implementing this would requires a considerable amount of effort for me. However, I am really happy to accept this as a contribution.

[RobertBaldini]

It took me some long time to realize that DbGeography was the thing that wasn't working for EFfort.

@alexmaie any luck by any chance?

[jimmymain]

this is also a problem with byte[] when mapped to varbinary(max)

[Fed29]

+1

[francesco-scullino]

Hi @tamasflamich. I appreciate a lot Effort and since DbGeography is very important for me and my colleagues, we would like to contribute to fix this issue. But we need some suggestions about the structure and the logic of the project, in order to better understand how to start developing. Can you help us?

[jandic]

Hi, I spent some time looking into the code of Effort (and NMemory - see below) only to realise that implementing DbGeography and DbGeometry would be difficult. So in the end I gave up on it and went with the hack suggested by @jimmymain.

Following comment from @n1k1t0ss, I updated Effort to get it working with DbGeometry and DbGeography only to realise that the issue is actually within NMemory library. So I had a look at NMemory and tried to implement DbGeometry and DbGeography data types based on code within EF6. I got a stub working but most methods are not actually implemented and will throw an error. Also data is not being stored anywhere. If anybody would like to pick this up and continue, have a look at:

https://github.com/jandic/effort https://github.com/jandic/nmemory

All plumbing should be done, only the actual in-memory implementation of spatial data types needs to be done in NMemory by implementing all remaining methods in https://github.com/jandic/nmemory/blob/master/Main/Source/NMemory/Spatial/DefaultSPatialServices.cs . But as I mentioned, I expect this to take a lot of effort.

[martin1cerny]

Is there any reason why System.Data.Spatial.DbSpatialServices could not be used directly?

[aalho]

Wouldn't it be more reasonable to ignore an unknown data type from the edmx file rather than throwing an exception and thus not blocking the test to be run?

Or Just add these lines to the EffortProviderManifest.xml -file as @n1k1t0ss mentioned earlier? <Type Name="geography" PrimitiveTypeKind="Geography"> <FacetDescriptions> <SRID DefaultValue="2147483647" Constant="false" Minimum="0" Maximum="2147483647"/> <IsStrict DefaultValue="false" Constant="false"/> </FacetDescriptions> </Type> <Type Name="geometry" PrimitiveTypeKind="Geometry"> <FacetDescriptions> <SRID DefaultValue="2147483647" Constant="false" Minimum="0" Maximum="2147483647"/> <IsStrict DefaultValue="false" Constant="false"/> </FacetDescriptions> </Type>

Atleast then one could run tests without manually removing them types from the .edmx file every time you update your model from the database... Yes there are still a few of us who are using edmx files?

[JonathanMagnan]

Hello @aalho ,

We will look at your suggestion about either adding an option to ignore unknown data type and provide a way to specify them.

We will look also at @n1k1t0ss solution to add support to spatial type.

Best Regards,

Jonathan

---

Performance Libraries context.BulkInsert(list, options => options.BatchSize = 1000); Entity Framework Extensions • Entity Framework Classic • Bulk Operations • Dapper Plus • LinqToSql Plus

Runtime Evaluation Eval.Execute("x + y", new {x = 1, y = 2}); // return 3 C# Eval Function • SQL Eval Function

[iaingalloway]

I'm also experiencing this issue.

[DreamRunnerMoshi]

I have worked around this by adding the following code to OnModelCreating. It just ignores storing the property, vaguely useful if you want to write unit tests involving the other fields on your entity, rather than having no tests.

// this horrible hack stops the geolocation from being saved in effort.
// https://github.com/tamasflamich/effort/issues/28
if (this.Database.Connection.GetType().Name.StartsWith("Effort", StringComparison.Ordinal))
    modelBuilder.Entity<Building>().Ignore(_ => _.Geolocation);

This is a quick solution but it is kind of weird to add code which is only needed for testing in production.

[brennon]

Has this fallen off the radar? The workaround may work for some people, but I'm not sure if there's a similar approach for those of us using ObjectContext. Is anyone aware of a way to do this?

[Jonno12345]

Hello @aalho ,

We will look at your suggestion about either adding an option to ignore unknown data type and provide a way to specify them.

We will look also at @n1k1t0ss solution to add support to spatial type.

Best Regards,

Jonathan

Performance Libraries context.BulkInsert(list, options => options.BatchSize = 1000); Entity Framework Extensions • Entity Framework Classic • Bulk Operations • Dapper Plus • LinqToSql Plus

Runtime Evaluation Eval.Execute("x + y", new {x = 1, y = 2}); // return 3 C# Eval Function • SQL Eval Function

Hi @JonathanMagnan

Has there been any updates on this issue?

Unfortunately we have some quite old models using a database-first EDMX model, which means we can't use any of the workarounds above to ignore the spatial type columns in an 'Effort' context. We don't need any of our tests to be performed on a geography type, ignoring would be sufficient.

Any advice would be greatly appreciated.

Thanks,

Jonno