LINQ to SQL: What is wrong with this method call ?

LINQ to SQL: What is wrong with this method call ?

Postby Wojtek » Sun 15 Nov 2009 20:26

Hi

I used Entity Developer (drag'n drop function from db explorer) to create simple mathod based on set returning function.
Code: Select all
CREATE OR REPLACE FUNCTION sp_log_select(p_id integer)  RETURNS SETOF log AS
$BODY$

DECLARE   row log%rowtype ;

BEGIN
   FOR row IN
   SELECT * FROM "log"
   WHERE ((id=p_id) or (p_id is null))
   LOOP  RETURN NEXT row;
   END LOOP;
END

$BODY$
  LANGUAGE 'plpgsql' VOLATILE


Then I tried to execute following code:

Code: Select all
            InvoiceDataContext.InvoiceDataContext db = new InvoiceDataContext.InvoiceDataContext(_innerConnection);
            db.Log = Console.Out;
            System.Linq.IQueryable result = db.SpLogSelect(null); 

            //set -> Binding source
            bsSearch.DataSource = result;


At lats line (setting BindingSource data source) I get the following System.InvalidOperationException:
"Entity types only can be used with System.Linq.IQueryable interafce."

What is wrong ?
How it should be invoked to get a result ?
How should I properly use postgres functions to return result set ?

Appreciate any hint ...
Wojtek
Wojtek
 
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Postby AndreyR » Mon 16 Nov 2009 17:30

Thank you for the report, we have changed this behaviour in the upcoming build.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Wojtek » Tue 02 Mar 2010 22:01

In recent version 79 there is still error with generation of sql code from set returning functions (LINQ).
Problem pops up when field names of the table contain underscore character eg.: client_id .
Then in resulting query sent to server the field name is changed.
Undercore character is removed so : client_id is converted to clientid in select statement.

This of course generates error as server does not understand the query.
Resulting error: "Error on executing DbCommand."
Inner Exception message: "column t1.clientid does not exist"


Of course it does not ...

This error apears only when set returning functions are being used.

This is very annoying and functions are still not usable in your product.
Can you fix it ?
We lose a lot of time debugging your 'release versions' of your software...
Wojtek
 
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Postby AndreyR » Wed 03 Mar 2010 12:00

The problem is already fixed.
I have tried similar procedure and successfully called it using the following code:
Code: Select all
      using (DataContext1.DataContext1 db = new DataContext1.DataContext1()) {
        var q = db.Getsetofdept().ToList();
      }

The build I was using is the latest 4.85.97 Beta.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Wojtek » Wed 03 Mar 2010 19:52

Well maybe it is fixed but another problem seems to appear now.

I have installed latest version of dotConnect for PostgreSQL Professional 4.85.97 Beta.

After starting Devart LINQ to SQL Model designer in server explorer database server object are not displayed.

When I try to click serwer object tree message box with error pops-up: (Title: Entity Developer for dotConnect)
"No binary output function available for type aclitem"

LINQ classes are not being generated after saving developer project.

I tried installing on 2 machines with VS 2008 Proffessional and both give the same result.
Server is run on Linux (Ubuntu) machine in LAN.
Previous version of dotConnect did not give such an arror on the same database/serwer/connection.

Any solution ?[/img]
Wojtek
 
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Postby AndreyR » Thu 04 Mar 2010 09:08

New build of dotConnect for PostgreSQL 4.90.102 Beta is available for download now.
It contains fix for the problem with aclitem.
However, we have found the problem with underscored characters again.
I will let you know as soon as it is fixed.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Wojtek » Sat 06 Mar 2010 07:43

See example:

Lates version of Beta (...102) being used.

Postgres table:

Code: Select all
CREATE TABLE fakt_test
(
  id bigserial NOT NULL,
  customerid integer,
  sellerid integer,
  doctype character varying(15),
  referenc integer DEFAULT 0,
  shiptoid integer DEFAULT 0,
  daterecord timestamp without time zone DEFAULT now(),
  dateissue timestamp(0) without time zone DEFAULT now(),
  ptermsid integer,
  CONSTRAINT fakttestpk876876 PRIMARY KEY (id)
)
WITH (OIDS=FALSE);


Then test function that returs set of records:

Code: Select all
CREATE OR REPLACE FUNCTION sp_fakt_select_test() RETURNS SETOF fakt_test AS
$BODY$

DECLARE
row fakt_test%rowtype ;

BEGIN
FOR wiersz IN  SELECT * FROM fakt_test ORDER BY id
LOOP RETURN NEXT row;
END LOOP;

END
$BODY$
LANGUAGE 'plpgsql' VOLATILE


Then C# part:

In designer I drop above sql function to designer are.
Method called SpFaktSelectTest() is being created.

Code: Select all
db.Log = Console.Out;
IQueryable result = db.SpFaktSelectTest();
fsBindingSource.DataSource = result;


then I checked Out window for sql code sent to server (OUT window):

Code: Select all
SELECT t1.Id AS "Id", t1.Customerid AS "Customerid", t1.Sellerid AS "Sellerid", t1.Doctype AS "Doctype", t1.Referenc AS "Referenc", t1.Shiptoid AS "Shiptoid", t1.Daterecord AS "Daterecord", t1.Dateissue AS "Dateissue", t1.Ptermsid AS "Ptermsid"
FROM public.sp_fakt_select_test() t1


This query works OK but what puzzless me why column names have been altered ?
Why now each column name starts with upper case ?
I think dotConnect should not interfere in this area.
Naming is a decison of application developer ...
Wojtek
 
Posts: 14
Joined: Sun 20 Nov 2005 18:51
Location: Warsaw

Postby AndreyR » Tue 09 Mar 2010 11:08

Please check your Naming Rules settings (Model Project Properties->Naming Rules tab).
Perhaps they are set to Capitalized (default).
If you wish the names to remain unchanged, select this naming rule and regenerate the necessary DB objects.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL