[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

LINQ - Multiple Datasources

Posted on 2014-07-15
3
Medium Priority
?
146 Views
Last Modified: 2016-02-15
Hi,

I need this SQL in LINQ.  Note the different databases, db1 & db2.

INSERT INTO db1.tblB
SELECT * FROM db1.tblA db1tA
INNER JOIN db2.tblA AS db2tA
ON db1tA.ColA = db2tA.ColA

The issues really is the JOIN from DB1 to DB2, LINQ does not like multiple DataContexts.

I am thinking I need to do this in 2 passes

First I will select what I need from DB2.tblA using LINQ and place it somewhere?
And then use what I created above and pass into some more LINQ and join to it.

Would this work?

I should say I am new to C# and this is my first bit of LINQ ever, so very much in at the deep end

Thanks in advance

nutnut
0
Comment
Question by:nutnut
  • 2
3 Comments
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 40196896
Hi nutnut;

You can send the raw SQL command to the SQL server and have the server execute the command. See the Linq to SQL code below.

// Set up the Linq to SQL DataContext
DataContext1 db = new DataContext1();
// Create a string which has the T-SQL command you wish to execute on the SQL server
string insertStatement = "NSERT INTO db1.tblB SELECT * FROM db1.tblA db1tA INNER JOIN db2.tblA AS db2tAON db1tA.ColA = db2tA.ColA";

// Send the command to the server as a raw SQL command for execution
db.ExecuteCommand(insertStatement);

Open in new window

0
 

Author Comment

by:nutnut
ID: 40196985
Thanks I wish I could but company policy states we have to use LINQ, in case the DB is changed in the future.
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 40197010
Well then what you stated in your question then should work. Please note that getting the values on the first pass and then passing it in to the SQL server, the inserts will happen one insert per record to be inserted. In other words if there are 100 records to be inserted into the database then there will be 100 commands sent to the server one record per command.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question