View with Spatial Query explodes dotconnect

[drysg]

I have a view that does a spatial intersection ST_Intersects on a PostGIS database.

The SQL view looks like this:

Code: Select all

CREATE OR REPLACE VIEW
    portal.viewboston AS
SELECT
    cat.idx,
    cat.product,
    cat.size_bytes,
    cat.date,
    cat.type,
    cat.classification,
    cat.distributor,
    cat.egpl_date,
    cat.classification_int,
    cat.handling,
    cat.creator,
    cat.datum,
    cat.elevation_ft,
    cat.description,
    cat.do_location,
    cat.bbox,
    cat.uniq_id::CHARACTER VARYING AS uniq_id
FROM
    portal.catalog cat
WHERE
    st_intersects(st_geomfromtext('POLYGON((-70 42,-71 42,-71 41,-70 41,-70 42))'::text, 4326),
    cat.location);

There was no problem using the entity framework to map this query. I then proceeded to write the following C#. The foreach blows up, but the real error happened on the line above it where I lazy eval'ed the query, (It was only when it tried to make it go concrete in the foreach that the error was recognized.

Code: Select all

        public CatalogEntry[] ViewBoston()
        {
            List values = new List();
            GPLCatalog context = new GPLCatalog();
            var query = from it in context.viewbostons
                        select it;
            foreach (viewboston item in query)

-

Code: Select all

		InnerException	{"function _st_intersects(public.geometry, public.geometry) does not exist"}	System.Exception {Devart.Data.PostgreSql.PgSqlException}
+		[Devart.Data.PostgreSql.PgSqlException]	{"function _st_intersects(public.geometry, public.geometry) does not exist"}	Devart.Data.PostgreSql.PgSqlException
+		Data	{System.Collections.ListDictionaryInternal}	System.Collections.IDictionary {System.Collections.ListDictionaryInternal}
		HelpLink	null	string
+		InnerException	null	System.Exception
		Message	"function _st_intersects(public.geometry, public.geometry) does not exist"	string
		Source	"Devart.Data.PostgreSql"	string
		StackTrace	"   at Devart.Data.PostgreSql.s.e(af A_0)\r\n   at Devart.Data.PostgreSql.af.ah()\r\n   at Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)\r\n   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)\r\n   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at Devart.Data.PostgreSql.Entity.h.a(CommandBehavior A_0)\r\n   at Devart.Common.Entity.aj.b(CommandBehavior A_0)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"	string
+		TargetSite	{Void e(Devart.Data.PostgreSql.af)}	System.Reflection.MethodBase {System.Reflection.RuntimeMethodInfo}

[Shalex]

Please try executing plain ADO.NET like:

Code: Select all

  PgSqlCommand cmd = conn.CreateCommand();
  cmd.CommandText = "select * from portal.viewboston";
  PgSqlDataReader reader = cmd.ExecuteReader();

Does it work?

[drysg]

This does work. So thank you for that.

But one of the reasons I bought the PRO version was your slick ORM tools, which would make the mapping a snap. This way it seems that I have to know which field is Int64, Int32, DateTime and String and the do the conversion myself.

Is that basically true? Or am I missing something.

Here is my current code (which works):

Code: Select all

            String str;
            Devart.Common.DbConnectionStringBuilder csb = new Devart.Common.DbConnectionStringBuilder();
            csb["Host"] = "localhost";
            csb["Port"] = 5432;
            csb["User Id"] = "postgres";
            csb["Password"] = "password"; // not the real password ;-)
            csb["Database"] = "GPL";
            csb["Max Pool Size"] = 150;
            csb["Connection Timeout"] = 30;
            PgSqlConnection conn = new PgSqlConnection(csb.ConnectionString);
            conn.Open();
            PgSqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from portal.viewboston";
            PgSqlDataReader reader = cmd.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    str = reader.GetInt64(0).ToString() +" " +   // DEVART: just and illustration for debug purposes... I have not finished this code
                        reader.GetString(1) + " " +
                        reader.GetString(2);
                }
            }
            finally
            {
                reader.Close();
                conn.Close();
            };

[Shalex]

We have answered you by e-mail (looking for the way how to call the search_box function via Entity Framework).

[drysg]

Let's close this discussion thread -> Since we agree that views are not the way to get the data. But you probably should note in your documentation that PostGIS spatial functions do not work with views.