Entity Framework and Esri Geodatabase (in AspNet.Core)


Intro

This blog will cover the basics for wiring up a model through Entity Framework to use with an SDE / Geo-enabled database. The included code is based on AspNetCore running on full framework. AspNetCore has dependency injection that is used to wire up the code.

The new (and good looking) Core version of Entity framework does not support tooling for the spatial types. This means we have to stick with EF6.x for now… As the tooling is not compatible we have to split the data-part in its own standard project – a classic class library.

For SDE I will not go into locking and versioning (nor dive too deep), but will include a light version of Editor Tracking.

So, lets go!

Create a model

The model we are going to create here is very simple, but will illustrate the points. We start by creating a simple table Location that holds a id, title, description and a geometry (the actual location)

image

CREATE TABLE [DEMO].[Location](
[locationId] [int] NOT NULL,
[title] [varchar](100) NULL,
[description] [varchar](500) NULL
[Location] [geometry] NOT NUL
)

In ArcMap we register the database as a geo-base and register the Location-table as a feature table

Time to dive into SDE

When you register a database as geobase and tables geotables (or features) in ArcMap quite a lot happens:

  • GDB – Geo Data Base – tables are added
  • SDE – Spatial Database Engine – tables and stored procedures are added
image image

For SDE it also creates “sequence” tables for the registered tables which shows up as [SCHEMA].i[num]. These are used to keep track of OBJECTIDs for the features and stored procedures (sde.i3_get_ids and sde.i3_return_ids) to retrieve and update these. Luckily there is a stored procedure NextId which encapsulates these things as long as we know the table name.

image

When at table is registered an entry is added to the SDE_table_registry. This entry tells us that the table is registered and that the OBJECTID (key) is in the column OBJECTID.

image

If the table has a geometry (which it probably has since it is being registered) it can be found in the SDE_geometry_columns

image

This tells us that the table Location has a geometry column called “Location”. There is also a SDE_column_registry that tells us the names and datatypes of all the columns in the registered table, but we don’t need that here.

We want to mimic the editor tracking so that it is “compatible” with ArcMap. When we enable the Editor Tracking (in ArcMap) for the table it will add 4 more columns:

  • created_user    nvarchar(255)
  • created_date    datetime2(7)
  • last_edited_user    nvarchar(255)
  • last_edited_date    datetime2(7)

So the table now looks like this:

image

Setting up the test-project(s)

Create a new project – classic class library

image

Add another project – AspNet.Core (on full .Net framework) to host the web project and API controller

image

Now, install Entity Framework from nuget on both projects.

PM>  Install-Package EntityFramework -Version 6.1.3

Then install the Sql Server Types – to support spatial types in the application – get the not vNext version because the tooling is off

PM>  Install-Package Microsoft.SqlServer.Types –Version 13.0.4001

Now a little “trick”; The package won’t install with AspNetCore, but you can simply copy the SqlServerTypes folder from the DataLib project (or configure it to deploy) over to the Web project. Inside the web-project, follow the instructions of how to load the assemblies. For example adding it to the Startup constructor (is Startup.cs):

    public Startup(IHostingEnvironment env)
    {
        System.Data.Entity.SqlServer.SqlProviderServices.SqlServerTypesAssemblyName = "Microsoft.SqlServer.Types, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
        SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

        ...
    }

EF Code First

In the DataLib, select Add New Item –> Data –> ADO.NET Entity Data Model, give it a nice name “DemoModel”. Click Add, select “Code First from Database”, set the connection string and click Next.

Now, select the feature class – DEMO.Location table

To automate things, we will also need some of the SDE-tables. Bring along SDE_geometry_columns and click Finish. Aftger changing the constructor to allow setting the connection string you should have a model like this.

    public partial class DemoModel : DbContext
    {
        public DemoModel(string connectionString = "name=DemoModel") 
          : base(connectionString) 
        { }

        public virtual DbSet Location { get; set; }
        public virtual DbSet SDE_geometry_columns { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity()
                .Property(e => e.title)
                .IsUnicode(false);

            modelBuilder.Entity()
                .Property(e => e.description)
                .IsUnicode(false);
        }
    }

And POCO classes Location.cs and SDE_geometry_column.cs. In these, change the Table attribute like this to make it easier to retrieve later (you’ll see)

    //[Table("DEMO.Location")]
    [Table("Location", Schema = "DEMO")]

Generic repository

Wrap these entities in a repository to hide the extra SDE-logic – more specifically one for our data an one for the SDE. Once added, extract interfaces to use for injection. I also like to create a sepearate class to hold the schema name (“DEMO”) to use with injection.

There are a few special tweaks we need to add.

HttpContext

Add the AspNetCore.http nuget package to the DataLib so we can use the HttpContextAccessor from within the repository (will be injected).

PM>  Install-Package Microsoft.AspNetCore.Http

SDERepository

This has the responsibility/methods for checking table registration and retrieveing OBJECTID’s when requested.

The constructor takes 2 parameters; DemoModel and ISchemaName (which will be injected)

And the main methods are as described in the interface

    public interface ISdeRepository
    {
        bool IsEntityRegistered();
        bool AssignNextIdIfRegistered(TEntity Entity);
    }

 

Datarepository

This is your “standard” repository, for example

    public interface IRepository where TEntity : class
    {
        TEntity Add(TEntity t);
        Task AddAsync(TEntity t);
        int Count();
        Task CountAsync();
        TEntity Delete(TEntity t);
        Task DeleteAsync(TEntity t);
        TEntity Find(Expression<Func> match);
        ICollection FindAll(Expression<Func> match);
        Task<List> FindAllAsync(Expression<Func> match);
        Task FindAsync(Expression<Func> match);
        TEntity Get(object id);
        ICollection GetAll();
        Task<List> GetAllAsync();
        Task GetAsync(object id);
        int Update(TEntity Entity);
        Task UpdateAsync(TEntity Entity);
        int Update(TEntity updated, object key);
        Task UpdateAsync(TEntity updated, object key);
    }

The implementation will take a few parameters to the constructor and also map the TEntity to a DbSet<>

    public class Repository : IRepository where TEntity : class
    {
        private readonly DemoModel context;
        private readonly DbSet entities;
        private readonly ISdeRepository SDERepository;
        private HttpContext CurrentHttpContext;

        public Repository(DemoModel dbContext, ISdeRepository SdeRepository, Microsoft.AspNetCore.Http.IHttpContextAccessor HttpContextAccessor)
        {
            CurrentHttpContext = HttpContextAccessor.HttpContext;
            System.Diagnostics.Trace.WriteLine($"Called repository constructor for type {typeof(TEntity)} by user: {CurrentHttpContext.User.Identity.Name}");
            context = dbContext;
            entities = context.Set();
            SDERepository = SdeRepository;
        }

        ...
    }

Set up Dependency Injection

First, add the DataLib as a refernece to the Web project. Then inside the Startup.cs ass the following to the ConfigureServices method:

Add dependecy injection configuration for

  • HttpContextAccessor,
  • Schema name (read from appsettings.json),
  • the Entity Context – initialized with connectionstring from the appsettings.json
  • Type mapping for entities,
  • and the SDE-repository (as a singleton)
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddMvc();

            services.AddSingleton();

            // map schema and DemoModel
            services.AddScoped(_ => new SchemaName(Configuration.GetValue("GeoTableSchemaName")));
            services.AddScoped(_ => new DemoModel(Configuration.GetConnectionString("DemoModel")));
            // entity repositories
            services.AddTransient(typeof(IRepository), typeof(Repository));

            // setup sde as singleton - it will cache the lookups and wire up the sp for NextId
            services.AddSingleton();
        }

 

DataRepository

Now, add the “magic”. Inside the repository class, whenever making an update to an entity or creating a new one we need to check:

  • is the table registered – if so, we need to fix the id (normally OBJECTID)
  • are we using editor tracking – if so, we need to insert/update the created and last_edited columns

Whenever insterting we call the AssignNextIdIfRegistered in the SDERepository with the entity as the parameter to get the OBJECTID.
Then we call the Set-extention method to insert the created-columns before saving the changes.

        public TEntity Add(TEntity t)
        {
            // check if entity has geometry (is GeoEnabled in SDE)
            SDERepository.AssignNextIdIfRegistered(t);
            context.Set().Add(t);
            t.Set(EditorTrackingType.Create, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            context.SaveChanges();
            return t;
        }

And for every update we just need to call the Set-method

        public int Update(TEntity Entity)
        {
            if (Entity == null)
                throw new ArgumentNullException(nameof(Entity));

            Entity.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return context.SaveChanges();
        }

In our controller class we now only have to set up so we receive the injected repository class and the rest wil lhappen by itself…

    public class LocationController : Controller
    {
        private IRepository _LocationRepository;
        public LocationController(IRepository LocationRepository)
        {
            _LocationRepository = LocationRepository;
        }
        [HttpGet]
        public IActionResult Index()
        {
            return Ok(_LocationRepository.GetAll().ToList());
        }

        ...
    }

Not covered / limitations

This is a straight forward example, and it will not handle or care about among other things like

  • Geometries – are hard to map directly, so should be handled by contract or whatever forexample as Well-Known-Text
  • Advanced geometries – like splines that are stored in the GEOMATTR column
  • Any relationships that are also feature classes – those need to be handled one-by-one to get the OBJECTIDs correctly

I would also suggest

  • using something like AutoMapper to map between ViewModels and Entites
  • if you need more control / more complex injection switch to something like StructureMap
  • Add Swagger to easily test your APIs
  • splitting context and models between SDE and the [SCHEMA]

Location controller listing

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using GeoBaseDemo.DataLib;
using GeoBaseDemo.DataLib.Implementation;
using Microsoft.AspNetCore.JsonPatch;

namespace GeoBaseDemo.Web.Controllers
{
    [Produces("application/json")]
    [Route("api/Location")]
    public class LocationController : Controller
    {
        private IRepository _LocationRepository;
        public LocationController(IRepository LocationRepository)
        {
            _LocationRepository = LocationRepository;
        }
        [HttpGet]
        public IActionResult Index()
        {
            return Ok(_LocationRepository.GetAll().ToList());
        }
        [HttpPatch("{id}")]
        public async Task PatchLocation(int id, [FromBody] JsonPatchDocument patchDocument)
        {
            if (patchDocument == null)
            {
                return BadRequest();
            }

            var dbCopy = await _LocationRepository.FindAsync(l => l.locationId == id);
            if (dbCopy == null)
            {
                return NotFound($"No object with id: {id}");
            }

            //Apply changes to object
            patchDocument.ApplyTo(dbCopy, ModelState);

            // validate model
            if (!ModelState.IsValid)
            {
                return new BadRequestObjectResult(ModelState);
            }

            // update database
            await _LocationRepository.UpdateAsync(dbCopy, dbCopy.OBJECTID);
            return Ok();
        }
        [HttpPost]
        public async Task PostLocation([FromBody] Location location)
        {
            if (ModelState.IsValid)
            {
                await _LocationRepository.AddAsync(location);
                return CreatedAtAction("Get", new { id = location.locationId }, location);
            }

            // TODO: validation errors, etc
            return BadRequest();
        }
    }
}

Model listings

namespace GeoBaseDemo.DataLib
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Data.Entity.Spatial;

    public partial class DemoModel : DbContext
    {
        public DemoModel(string connectionString = "name=DemoModel") : base(connectionString) { }

        public virtual DbSet Location { get; set; }
        public virtual DbSet SDE_geometry_columns { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity()
                .Property(e => e.title)
                .IsUnicode(false);

            modelBuilder.Entity()
                .Property(e => e.description)
                .IsUnicode(false);
        }
    }

    [Table("sde.SDE_geometry_columns")]
    public partial class SDE_geometry_columns
    {
        [Key]
        [Column(Order = 0)]
        [StringLength(32)]
        public string f_table_catalog { get; set; }

        [Key]
        [Column(Order = 1)]
        [StringLength(32)]
        public string f_table_schema { get; set; }

        [Key]
        [Column(Order = 2)]
        public string f_table_name { get; set; }

        [Key]
        [Column(Order = 3)]
        [StringLength(32)]
        public string f_geometry_column { get; set; }

        [StringLength(32)]
        public string g_table_catalog { get; set; }

        [Required]
        [StringLength(32)]
        public string g_table_schema { get; set; }

        [Required]
        [StringLength(128)]
        public string g_table_name { get; set; }

        public int? storage_type { get; set; }

        public int? geometry_type { get; set; }

        public int? coord_dimension { get; set; }

        public int? max_ppr { get; set; }

        public int srid { get; set; }
    }

    //[Table("DEMO.Location")]
    [Table("Location", Schema = "DEMO")]
    public partial class Location
    {
        public int locationId { get; set; }

        [StringLength(100)]
        public string title { get; set; }

        [StringLength(500)]
        public string description { get; set; }

        [Column("Location")]
        [Required]
        public DbGeometry Location1 { get; set; }

        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int OBJECTID { get; set; }

        public byte[] GDB_GEOMATTR_DATA { get; set; }

        [StringLength(255)]
        public string created_user { get; set; }

        [Column(TypeName = "datetime2")]
        public DateTime? created_date { get; set; }

        [StringLength(255)]
        public string last_edited_user { get; set; }

        [Column(TypeName = "datetime2")]
        public DateTime? last_edited_date { get; set; }
    }
}

Startup.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.AspNetCore.Http;
using GeoBaseDemo.DataLib;
using GeoBaseDemo.DataLib.Implementation;

namespace GeoBaseDemo.Web
{
    public class Startup
    {
        public Startup(IHostingEnvironment env)
        {
            System.Data.Entity.SqlServer.SqlProviderServices.SqlServerTypesAssemblyName = "Microsoft.SqlServer.Types, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
            SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);

            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
                .AddEnvironmentVariables();
            Configuration = builder.Build();
        }

        public IConfigurationRoot Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddMvc();

            services.AddSingleton();

            // map schema and DemoModel
            services.AddScoped(_ => new SchemaName(Configuration.GetValue("GeoTableSchemaName")));
            services.AddScoped(_ => new DemoModel(Configuration.GetConnectionString("DemoModel")));
            // entity repositories
            services.AddTransient(typeof(IRepository), typeof(Repository));

            // setup sde as singleton - it will cache the lookups and wire up the sp for NextId
            services.AddSingleton();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            loggerFactory.AddDebug();

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseBrowserLink();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }

            app.UseStaticFiles();

            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

Repository listings

using Microsoft.AspNetCore.Http;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace GeoBaseDemo.DataLib.Implementation
{
    public interface IRepository where TEntity : class
    {
        TEntity Add(TEntity t);
        Task AddAsync(TEntity t);
        int Count();
        Task CountAsync();
        TEntity Delete(TEntity t);
        Task DeleteAsync(TEntity t);
        TEntity Find(Expression<Func> match);
        ICollection FindAll(Expression<Func> match);
        Task<List> FindAllAsync(Expression<Func> match);
        Task FindAsync(Expression<Func> match);
        TEntity Get(object id);
        ICollection GetAll();
        Task<List> GetAllAsync();
        Task GetAsync(object id);
        int Update(TEntity Entity);
        Task UpdateAsync(TEntity Entity);
        int Update(TEntity updated, object key);
        Task UpdateAsync(TEntity updated, object key);
    }

    public class Repository : IRepository where TEntity : class
    {
        private readonly DemoModel context;
        private readonly DbSet entities;
        private readonly ISdeRepository SDERepository;
        private HttpContext CurrentHttpContext;

        public Repository(DemoModel dbContext, ISdeRepository SdeRepository, Microsoft.AspNetCore.Http.IHttpContextAccessor HttpContextAccessor)
        {
            CurrentHttpContext = HttpContextAccessor.HttpContext;
            System.Diagnostics.Trace.WriteLine($"Called repository constructor for type {typeof(TEntity)} by user: {CurrentHttpContext.User.Identity.Name}");
            context = dbContext;
            entities = context.Set();
            SDERepository = SdeRepository;
        }

        public ICollection GetAll()
        {
            return context.Set().ToList();
        }

        public Task<List> GetAllAsync() => context.Set().ToListAsync();

        public TEntity Get(object id) => context.Set().Find(id);

        public Task GetAsync(object id) => context.Set().FindAsync(id);

        public TEntity Find(Expression<Func> match) => context.Set().SingleOrDefault(match);

        public Task FindAsync(Expression<Func> match) => context.Set().SingleOrDefaultAsync(match);

        public ICollection FindAll(Expression<Func> match) => context.Set().Where(match).ToList();

        public Task<List> FindAllAsync(Expression<Func> match) => context.Set().Where(match).ToListAsync();

        public TEntity Add(TEntity t)
        {
            // check if entity has geometry (is GeoEnabled in SDE)
            SDERepository.AssignNextIdIfRegistered(t);
            context.Set().Add(t);
            t.Set(EditorTrackingType.Create, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            context.SaveChanges();
            return t;
        }

        public async Task AddAsync(TEntity t)
        {
            // check if entity has geometry (is GeoEnabled in SDE)
            context.Set().Add(t);
            SDERepository.AssignNextIdIfRegistered(t);
            t.Set(EditorTrackingType.Create, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            await context.SaveChangesAsync().ConfigureAwait(false);
            return t;
        }
        public int Update(TEntity Entity)
        {
            if (Entity == null)
                throw new ArgumentNullException(nameof(Entity));
            Entity.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return context.SaveChanges();
        }
        public Task UpdateAsync(TEntity Entity)
        {
            if (Entity == null)
            {
                throw new ArgumentNullException(nameof(Entity));
            }
            Entity.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return context.SaveChangesAsync();
        }

        public int Update(TEntity updated, object key)
        {
            if (updated == null)
                throw new ArgumentException("No value passed", nameof(updated));

            TEntity existing = context.Set().Find(key);
            if (existing == null)
                throw new ArgumentException("No entity with specified key was found", nameof(key));

            context.Entry(existing).CurrentValues.SetValues(updated);
            existing.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return context.SaveChanges();
        }

        public async Task UpdateAsync(TEntity updated, object key)
        {
            if (updated == null)
                throw new ArgumentException("No value passed", nameof(updated));

            TEntity existing = await context.Set().FindAsync(key).ConfigureAwait(false);
            if (existing == null)
                throw new ArgumentException("No entity with specified key was found", nameof(key));

            context.Entry(existing).CurrentValues.SetValues(updated);
            existing.Set(EditorTrackingType.Update, CurrentHttpContext.User.Identity.Name, DateTime.Now);
            return await context.SaveChangesAsync().ConfigureAwait(false);
        }

        public TEntity Delete(TEntity t)
        {
            context.Set().Remove(t);
            context.SaveChanges();
            return t;
        }

        public async Task DeleteAsync(TEntity t)
        {
            context.Set().Remove(t);
            await context.SaveChangesAsync().ConfigureAwait(false);
            return t;
        }

        public int Count()
        {
            return context.Set().Count();
        }

        public Task CountAsync()
        {
            return context.Set().CountAsync();
        }

    }

    public enum EditorTrackingType
    {
        Unknown = 0,
        Create = 1,
        Update = 2
    }

    public enum EditorTrackingColumns
    {
        Unknown = 0,
        LastEditedBy = 1, LastEditedDate = 2,
        CreatedBy = 3, CreatedDate = 4
    }

    public static class EditorTrackingExtensions
    {
        public static void Set(this object Entity, EditorTrackingType type, string ChangeBy, DateTime ChangeDate)
        {
            if (type == EditorTrackingType.Unknown) throw new ArgumentException("Illegal Editor Tracking type", nameof(type));

            if (type == EditorTrackingType.Create)
            {
                Entity.Set(EditorTrackingColumns.CreatedBy, ChangeBy);
                Entity.Set(EditorTrackingColumns.CreatedDate, ChangeDate);
            }
            else if (type == EditorTrackingType.Update)
            {
                Entity.Set(EditorTrackingColumns.LastEditedBy, ChangeBy);
                Entity.Set(EditorTrackingColumns.LastEditedDate, ChangeDate);
            }
        }
        public static void Set(this object Entity, EditorTrackingColumns Column, object NewValue)
        {
            var editdateprop = Entity.GetType().GetProperties().SingleOrDefault(p => p.Name.Equals(Column.Name(), StringComparison.CurrentCultureIgnoreCase));
            editdateprop.SetMethod.Invoke(Entity, new object[] { NewValue });
        }
        /*
         Expected fields in model are:

            [StringLength(255)]
            public string created_user { get; set; }

            [Column(TypeName = "datetime2")]
            public DateTime? created_date { get; set; }

            [StringLength(255)]
            public string last_edited_user { get; set; }

            [Column(TypeName = "datetime2")]
            public DateTime? last_edited_date { get; set; }

        */
        public static string Name(this EditorTrackingColumns column)
        {
            switch (column)
            {
                case EditorTrackingColumns.LastEditedBy:
                    return LastEditedByColumn;
                case EditorTrackingColumns.LastEditedDate:
                    return LastEditedDateColumn;
                case EditorTrackingColumns.CreatedBy:
                    return CreatedByColumn;
                case EditorTrackingColumns.CreatedDate:
                    return CreatedDateColumn;
            }
            throw new ArgumentException("Illegal value for Editor Tracking Column", nameof(column));
        }
        public static string LastEditedDateColumn => "last_edited_date";
        public static string LastEditedByColumn => "last_edited_user";
        public static string CreatedDateColumn => "created_date";
        public static string CreatedByColumn => "created_user";
    }

    public interface ISchemaName
    {
        string Name { get; }
    }

    public class SchemaName : ISchemaName
    {
        public SchemaName(string Name)
        {
            this.Name = Name;
        }
        public string Name { get; }
    }

    public interface ISdeRepository
    {
        bool IsEntityRegistered();
        bool AssignNextIdIfRegistered(TEntity Entity);
    }

    public class SDERepository : ISdeRepository
    {
        readonly private string Schema;
        readonly private Dictionary TableGeometryColumns;
        readonly private Dictionary TableRowIds;
        readonly private DemoModel SDEDbContext;

        public SDERepository(DemoModel DbContext, ISchemaName DefaultSchema)
        {
            SDEDbContext = DbContext;
            Schema = DefaultSchema.Name.ToUpper();

            // cache geometry registrations
            TableGeometryColumns = SDEDbContext.SDE_geometry_columns
                .Where(t => t.f_table_schema.ToUpper() == Schema)
                .ToDictionary(
                    k => k.f_table_name.ToUpper(),
                    v => v.f_geometry_column.ToUpper()
                 );
        }
        /// 
        /// If entity is registered, assign next id to its ObjectID property
        /// 
        /// 
        /// 
        /// 
        public bool AssignNextIdIfRegistered(TEntity Entity) => IsEntityRegistered() ? GetSetNextId(Entity) : false;
        /// 
        /// Test if table is registered with geometry in SDE - in which we need to handle ObjectIDs
        /// 
        /// 
        /// 
        public bool IsEntityRegistered() => TableGeometryColumns.ContainsKey(GetTableName().ToUpper());

        /// 
        /// Get next object id for type and assign it to the entity's object id property
        /// 
        /// 
        /// 
        /// 
        private bool GetSetNextId(TEntity Entity)
        {
            // get table for TEntity
            var tableName = GetTableName();
            // get name of column for rowID
            var rowIdColumn = TableRowIds[tableName];
            // get next available object id for entity
            var rowid = GetNextObjectId(tableName);
            // get ObjectID property and assign value
            //var prop = Entity.GetType().GetProperty("Objectid", System.Reflection.BindingFlags.IgnoreCase | System.Reflection.BindingFlags.IgnoreReturn | System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.FlattenHierarchy);
            var prop = Entity.GetType().GetProperties().SingleOrDefault(p => p.Name.Equals(rowIdColumn, StringComparison.CurrentCultureIgnoreCase));
            prop.SetMethod.Invoke(Entity, new[] { rowid });

            return true;
        }
        /// 
        /// Get the table name from an entity
        /// 
        /// 
        /// 
        // EFCore
        //private string GetTableName() => db.Model.FindEntityType(typeof(TEntity)).SqlServer().TableName;
        private string GetTableName()
        {
            var TableAttribute = Attribute.GetCustomAttributes(typeof(TEntity)).FirstOrDefault(a => a.GetType() == typeof(System.ComponentModel.DataAnnotations.Schema.TableAttribute)) as System.ComponentModel.DataAnnotations.Schema.TableAttribute;
            return TableAttribute.Name.ToUpper();
        }
        /// 
        /// Execute SDE stored procedure to produce new ObjectID
        /// 
        /// 
        /// 
        private object GetNextObjectId(string tableName = null)
        {
            tableName = tableName ?? GetTableName();
            if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException(nameof(tableName), "Table name must be specified. For Schema, \"dbo\" will be used if omitted.");
            var rowid = new SqlParameter("@1", -1) { Direction = System.Data.ParameterDirection.Output, SqlDbType = System.Data.SqlDbType.Int };
            var owner = new SqlParameter("@2", Schema);
            var table = new SqlParameter("@3", tableName);
            var r = SDEDbContext.Database.ExecuteSqlCommand("exec sde.next_rowid @owner=@2, @table=@3, @rowid=@1 output ", owner, table, rowid);
            return rowid.Value;
        }
    }
}

Works for me! Smile

Advertisements

About skattefuten

Check my about page :-)
This entry was posted in .Net, ArcGIS, EF, SDE and tagged , , , , , , , , . Bookmark the permalink.

5 Responses to Entity Framework and Esri Geodatabase (in AspNet.Core)

  1. stackopost says:

    Nice write sir.
    Can I feature your article on my website? It’s an information sharing website, users can share interesting scientific discoveries and related resources. And yeah sometimes reading feels boring, therefore we decided to make our website talk and listen for real. Check out our Audio readouts and voice comment integration. Here it is: stackopost.com.

  2. stackopost says:

    Can I feature your article on my website? It’s an information sharing website, users can share interesting scientific discoveries and related resources. And yeah sometimes reading feels boring, therefore we decided to make our website talk and listen for real. Check out our Audio readouts and voice comment integration.

  3. Pingback: Entity Framework and Esri Geodatabase (in AspNet.Core) By skattefuten – Stack of Post

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s