CODEWITHSUNDEEP
c#sqlsql-serveroracle-databaselinq
nik
nik

Reputation: 1784

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

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.

Upvotes: 0

Views: 1761

Answers (1)

JotaBe
JotaBe

Reputation: 39025

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.

Upvotes: 1

Related Questions