Custom EdmFunctions (MDF) in .EDML model

Custom EdmFunctions (MDF) in .EDML model

Postby 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
israeld
 
Posts: 10
Joined: Wed 19 Dec 2012 23:04

Re: Custom EdmFunctions (MDF) in .EDML model

Postby 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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Custom EdmFunctions (MDF) in .EDML model

Postby 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

Postby 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.
israeld
 
Posts: 10
Joined: Wed 19 Dec 2012 23:04

Re: Custom EdmFunctions (MDF) in .EDML model

Postby 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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Custom EdmFunctions (MDF) in .EDML model

Postby 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.
israeld
 
Posts: 10
Joined: Wed 19 Dec 2012 23:04

Re: Custom EdmFunctions (MDF) in .EDML model

Postby 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/105163-ado-net-entity-framework-support.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle