Custom EdmFunctions (MDF) in .EDML model

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
israeld
Posts: 10
Joined: Wed 19 Dec 2012 23:04

Custom EdmFunctions (MDF) in .EDML model

Post by israeld » Wed 19 Dec 2012 23:15

Hi,
We are using Entity Framework and dotConnect for Oracle.
We have legacy data which uses CHAR(1) fields with Y/N values to store booleans and require a way in which we can map these to .Net native bool properties, and importantly we need to perform queries based on these fields.
  • Option 1: Create a partial class and add a bool wrapper property. Not acceptable because cannot query
  • Option 2: Create views over the top of our tables to translate Y/N into 1/0 and map our entities to the views. Not acceptable because we have many tables and do not want the overhead of managing these extra views, with associated insert/update triggers.
  • Option 3: Create Model-Defined Functions (MDF) as per http://msdn.microsoft.com/en-us/library/dd456845.aspx
As I've explained, options 1 and 2 are not acceptable for our requirements.
Option 3 works perfectly, however we have found that performing any action using Entity Developer on the model deletes our functions from the .EDML file.

Is there any way to prevent Entity Developer from deleting these functions from the .EDML? Is there any chance that dotConnect for Oracle will natively support Y/N bool fields?

Regards,
Israel

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Custom EdmFunctions (MDF) in .EDML model

Post by Shalex » Thu 20 Dec 2012 16:31

israeld wrote:Option 1: Create a partial class and add a bool wrapper property. Not acceptable because cannot query
This is correct. Only LINQ to Objects can be used in this case (no LINQ to Entities).
israeld wrote:Option 2: Create views over the top of our tables to translate Y/N into 1/0 and map our entities to the views. Not acceptable because we have many tables and do not want the overhead of managing these extra views, with associated insert/update triggers.
You can avoid using views by setting the Defining Query properties of tables in the storage part of the model via interface of Entity Developer. Here is an example of the corresponding defining query:

Code: Select all

SELECT 
  numbercolumn, 
  decode(charcolumn,'Y',1,'N',0) AS charcolumn
FROM booltable
OR (the same)

Code: Select all

SELECT 
  numbercolumn, 
  CASE 
    WHEN charcolumn = 'Y' THEN 1
    WHEN charcolumn = 'N' THEN 0
  ELSE NULL
  END as charcolumn
FROM booltable
We are investigating the possibility of implementing the feature of user-defined conversion rules (including "boolean as string").
israeld wrote:Option 3: Create Model-Defined Functions (MDF) as per http://msdn.microsoft.com/en-us/library/dd456845.aspx
We are investigating the possibility of serializing/deserializing Model-Defined Functions with *.edml.

israeld
Posts: 10
Joined: Wed 19 Dec 2012 23:04

Re: Custom EdmFunctions (MDF) in .EDML model

Post by israeld » Thu 20 Dec 2012 19:51

Interesting information Shalex, thank you.
I will investigate the defining query option you have suggested.

Thank you mate,
Israel.

israeld
Posts: 10
Joined: Wed 19 Dec 2012 23:04

Re: Custom EdmFunctions (MDF) in .EDML model

Post by israeld » Fri 04 Jan 2013 04:31

Shalex and others,

I have encountered a problem with using your Defining Query solution.
When I modify an entity which is based on a defining query I'm getting the following error: ORA-01733: virtual column not allowed here

The resulting SQL looking like this:

Code: Select all

UPDATE(
    SELECT id,
           numbercolumn,
           decode(charcolumn,'Y',1,'N',0) AS charcolumn
    FROM booltable
) SET charcolumn = 1
WHERE id = 123
Can you please confirm, is it possible to update an entity based on a Defining Query?

Cheers,
Israel.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Custom EdmFunctions (MDF) in .EDML model

Post by Shalex » Fri 04 Jan 2013 17:38

Please try a workaround:
1. Create a table in your database:

Code: Select all

CREATE TABLE BOOLTABLE (
  ID NUMBER(38),
  NUMBERCOLUMN NUMBER(38),
  CHARCOLUMN CHAR(1 BYTE),
  CONSTRAINT PK_CHARTABLE PRIMARY KEY (ID));
2. Drag&drop the table on the model designer and make the following settings for CHARCOLUMN
-> CSDL: set Type to Boolean
-> SSDL: Type=bool, Max Length=-1 (remove the existing value)

3. Select BOOLTABLE in Store part of the model and specify the folowing SQL in the Defining Query property of BOOLTABLE in SSDL:

Code: Select all

SELECT
  id,
  numbercolumn,
  decode(charcolumn,'Y',1,'N',0) AS charcolumn
FROM booltable
Press OK and DO NOT regenerate Columns collection (otherwise, redo the second step and right click on entity on model designer > Mapping Details > select BOOLTABLE in the Storage Entities list and press OK).

4. Right click on the Stored Procedures node in Store part of the model > Add > New Command Text:

a) Stored Procedure Name=insert_procedure
SQL Script=insert into booltable (id, numbercolumn, charcolumn) values (:p_id, :p_numbercolumn, :p_charcolumn)
Parameters' data types:
p_id=decimal
p_numbercolumn=decimal
p_charcolumn=bool

b) Stored Procedure Name=update_procedure
SQL Script=update booltable set
numbercolumn = :p_numbercolumn,
charcolumn = decode(:p_charcolumn,1,'Y',0,'N')
where id = :p_id
Parameters' data types:
p_id=decimal
p_numbercolumn=decimal
p_charcolumn=bool

c) Stored Procedure Name=delete_procedure
SQL Script=delete from booltable where id=:p_id
Parameter's data type:
p_id=decimal

After this, it should be possible to update an entity based on a Defining Query.

israeld
Posts: 10
Joined: Wed 19 Dec 2012 23:04

Re: Custom EdmFunctions (MDF) in .EDML model

Post by israeld » Sun 06 Jan 2013 23:02

Thank you Shalex, yes this solution does work. But it hurts me writing those stored procs, one reason we use an ORM is to avoid writing SQL :)

Do you have any info on how far away you are from properly supporting user defined conversion rules which would allow us to natively treat 'Y'/'N' as bool and avoid the need for workarounds?

Cheers,
Israel.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Custom EdmFunctions (MDF) in .EDML model

Post by Shalex » Tue 08 Jan 2013 14:39

israeld wrote:Do you have any info on how far away you are from properly supporting user defined conversion rules which would allow us to natively treat 'Y'/'N' as bool and avoid the need for workarounds?
Please post the request at http://devart.uservoice.com/forums/1051 ... rk-support.

Post Reply