How to convert Oracle/SQL Query to Linq.

Question

Hi All,

I have Oracle long query, which is in string format, i want to convert in linq, but i dont want to chnage by if and else conditon,

looking some kind of builder, below is my query, this is one query i have more then 100 query which hitting DB.

this is query.

(SUM(CASE WHEN (TO_CHAR(LN_AQSN_DT, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY')-2) AND (TO_CHAR(LN_AQSN_DT, 'MM') IN ('07','08','09')) THEN LN_CURR_SFEE_RT*LN_TRUE_NEW_AQSN_AMT END) / SUM(CASE WHEN LN_CURR_SFEE_RT > 0 AND (TO_CHAR(LN_AQSN_DT, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY')-2) AND (TO_CHAR(LN_AQSN_DT, 'MM') IN ('07','08','09')) THEN LN_TRUE_NEW_AQSN_AMT END)) AS WAVG_SFEE_TRND_Q32PY_TNA

any help. Thanks

---

Ashok

Answer 1

Hi ashokapex,

Welcome to MSDN Forum.

Based on such a complex query, I suggest you to execute the Oracle/SQL in Linq to SQL directly. Because when the Linq query being executed, it will be translated to the Oracle/SQL, but the translation is not as the optimal way, it may generate a more complex Oracle/SQL than the original one you had, it will certainly affect the performance. So, in the common way, if the SQL is too complex, we often execute directly in Linq to SQL, this is more efficient than translate it to Linq query and execute the Linq query. Here's the document which introduces how to execute SQL directly in Linq to SQL, please refer here. : )

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Thanks for your reply,

but i think my problem in different way,

i have application which is currently on production and end user write query on screen, so they familer Oracle Query,

now my problem, i dont know what query they going to write , i gave u one example on above but it can be different, and N number query.

what iwas thinking , i can take this string and if i would have such kind of tools or linq which can convert and execute on collecetion and return back object.

is this possible if yes can you give me example.

Thanks

A-

---

Ashok

Answer 3

You need a Oracle Data Provider, this is easier to do in EF I think, you can either use the one provided by VS, I think there might be one by Oracle or a third party one like this one http://oracleef.codeplex.com/

Regards

Answer 4

Thanks Surgey,

u r answer is related to my last reply?

if yes so, i  am not using EF, my question, how i can translate my SQL/Oracle Query to Linq,

and that query written by end user on UI, i am thinking some convertor which enough to understand and convert my query to Linq format.

so evey time i dont need to check my query what inside.

Thanks

A-

---

Ashok

Answer 5

Look, here is a nice walkthrough from Oracle that I think serves your purposes, check the "Code Listing 1" part

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51odt-453447.html

LINQ TO SQL is deprecated, you should use Entity Framework, more so if you plan to use an Oracle backend as LINQ TO SQL was designed primary with T-SQL in mind, not PL/SQL and EF allows easier work with a more diverse breath of dataproviders.

Lastly I think that you can mix and match if you want and only use EF for the conversion part and then keep using linq to sql for the rest (although I really don't see why you would want that and I really encourage you to try EF).

Regards

Answer 6

Thanks for reply,

i saw example :

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51odt-453447.html

but i think may be i did not understand what u said.

my question: if i do use EF  and i have query which in "select * from table1 where aa=""

then EF automattilcay convert my query? if no then i am looking such kind of thing.

because in my case user always right SQL query not Linq Query so i want to convert from sql query to Linq, and i dont know how query look like.

no matter sql or Oracle.

so if you have such kind of example where Query get translated from SQL to Query. pls share with me.

Thanks.

A-

---

Ashok

Answer 7

Thanks for reply,

You are welcome

i saw example :

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51odt-453447.html

but i think may be i did not understand what u said.

my question: if i do use EF  and i have query which in "select * from table1 where aa=""

then EF automattilcay convert my query?

Yes, did you really check the example?

From the website 

Code Listing 1: Entity SQL code for Program.cs 

// Entity SQL  -- Retrieve employees with ID number less than max_id
int max_id = 110;
string esql = "select e.EMPLOYEE_ID, e.FIRST_NAME, e.SALARY from HREntities.EMPLOYEEs as e 
where e.EMPLOYEE_ID < " + max_id;
EntityConnection econn = new EntityConnection("name=HREntities");

econn.Open();
EntityCommand ecmd = econn.CreateCommand();
ecmd.CommandText = esql;
EntityDataReader ereader = ecmd.ExecuteReader(CommandBehavior.SequentialAccess);
Console.WriteLine("Entity SQL Result");
while (ereader.Read())
{
    Console.WriteLine("ID: " + ereader.GetValue(0) +
        "    Name: " + ereader.GetValue(1) +
        "    Salary: " + ereader.GetValue(2));
}

See how they pick a string that has a query in it and pass it to a property that convert it to proper SQL

Answer 8

Thanks for reply,

i am unable to move forward, my DB is Oracle and i can not download provider, because security reason.

dont know how to move forward.

Thanks

A-

---

Ashok

Answer 9

I think there is a provider for Oracle that comes shipped with VS, that one should work, if not you need to talk to your employers and explain the situation to them.

Regards