using LINQ on SQL Server with a linked server to oracle db

Question

I was wondering whether I can execute LINQ on an SQL server to speed up my query.

I will make a simple example. Currently I use this to fill my datatable:

        using (var connection = new SqlConnection())
        using (var da = new SqlDataAdapter())
        using (da.SelectCommand = connection.CreateCommand())
        {
            da.SelectCommand.CommandText = newcmd;
            da.SelectCommand.Connection.ConnectionString = connstring;
            da.SelectCommand.CommandTimeout = 0;
            DataTable ds = new DataTable(); //conn is opened by dataadapter
            da.Fill(ds);
        }

with this command:

newcmd = "select * from openquery("LinkedServer", 'select * FROM tbl_oracle p ')";

And then once I have the data in the DataTable I use LINQ to manipulate the data as I see fit. Howerever this means I have to transfer the entire table!

Since this returns a lot of data in the real query, the below (simple sum example) turns out to be much faster (mainly because of interface /transfer rates).

newcmd = "select * from openquery("LinkedServer", 'select p.timestep, SUM (p.position)
          FROM tbl_oracle p GROUP BY p.timestep ')";

Obviously in reality the data manipulation is more complex. So my question:

Can I somehow use LINQ on oracle db or on Linked Server on SQL Server and execute it on the server so that data manipulation is done before data transfer to desktop? I would really like the power of LINQ without tranferring all the raw data.

UPDATE

I set up a view in sql server management studio on the linked oracle server as suggested in the answer below. I then ran a very simple query:

select * from view where ID=1

with an execution plan and this shows that the entire oracle table is scanned first (remote scan 100% cost) an query is not executed on oracle server. The same query executes in split seconds via openquery. This makes this approach unusable due to the size of the data involved. Any other suggestions would be appreciated.

JotaBe · Accepted Answer · 2014-09-23 08:07:18Z

1

You can create views on your tables of interest in the SQL Server, and use EF or LINQ to SQL on that tables. In this way, the query will be transfered to the Oracle Server.

EF or LINQ to SQL don't support the specification of the server part on the fully qulified name of a table. But, if you create a view like this:

 create view MyView as SELECT * FROM LinkedServer.Database.Schema.Table

you can work on MyView as if it was a table in your local server, and the resulting SQL query will be executed directly on the linked Oracle server.

edited Sep 23, 2014 at 8:07

answered Sep 23, 2014 at 8:01

JotaBe

39.3k9 gold badges102 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

nik Over a year ago

thank you. do i have to use schema.table or can I create the view on a normal sql? My issue is that in reality my oracle sql already joins a few tables on the oracle db...

JotaBe Over a year ago

If you create the view using the four part name, the query will be constructed as a "normal" SQL Query and thus it will be sent to the Oracle Server. The Oracle Server will execute it as if it was a local query, so it doesn't matter if it's a real table or a view on the Oracle side, because Oracle will handle it perfectly. If you created the view using openquery, if at all possible, the SQL Server would send the OpenQuery's query to Oracle for execute, get the results, and complete the query (filtering, ordering...) locally, so you'd move the problem from your app to your SQL Server.

nik Over a year ago

I tried this but the view is very slow! It seems to be as if it downloads the entire view from Orcale to SQL first (and this in itself is too much data), so the only option I see with this approach would be to be specific on your view creation and then only do the rest of your manipulation in LINQ - but I was hoping to do all the manipulation in LINQ

JotaBe Over a year ago

Have you tried to execute the query on the remote view directly in SSMS? Is it equally slow? Depending on how your wuery looks like, it can be transferred to Oracle or not. Please, try it.

nik Over a year ago

thats exactly what I did. very simple Select * from view where id=1 directly in SSMS and it didnt finish for a long time - if i do the same via openquery it takes split seconds...

|

Collectives™ on Stack Overflow

using LINQ on SQL Server with a linked server to oracle db

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related