Linq to Sql: Select only items from DB1-table1 that don't exist on DB2-table2

Question

I'have been working around on how to properly implement the bellow task in a c# project.

It is pretendended to ...

Get all data that exists in a particular database's table(db1) but that does NOT EXISTS on another particular database's table (db2)

both tables have common ids

I've faced lots of posts about this but it seems none solves my problem. Any help?

EDITED:

Select all data 
on table_x from database_x 
Where item_id from table_x are not found inside table_y from database_y

=> return data in list format

You need top use a LINQ query that pulls out the required data. Do your tables have a property that will enable you to Join them? — Derek, Jan 04 '13 at 14:40
DB2 is a mirror of DB1 with slightly different in the table structure. But, both tablets have an Id which allow to join them. — Lothre1, Jan 04 '13 at 14:42
I would say it is precisely the opposite. I want to subtract the content of a datable in DB2 to the content of another table in DB1. The result should be items existing in DB1's table that does not exist in DB2's table — Lothre1, Jan 04 '13 at 14:56
So you need write a LINQ query that will only take items in table 1 where the ID field is not in Table2? Are you using two different DataCOntexts? — Derek, Jan 04 '13 at 15:21

score 5 · Accepted Answer · edited May 23 '17 at 12:09

This were the solution that I was looking for. Based on @user1949706's answer I selected all data from both tables with LINQ (also from different databases) and I placed it on memory.

To fully answer my question on how to do this with LINQ here it is:

//DB1
db1DataContext db1 = new db1DataContext();
//DB2
db2DataContext db2 = new db2DataContext();


//SELECT ALL DATA FROM DB1
var result1 = (from e in db1.Items
               select e
              ).ToList();

//SELECT ALL DATA FROM DB2
var result2 = (from e in db2.Item2s
               select e
              ).ToList();

//SELECT ALL ELEMENTS FROM DB2.TABLE THAT DO NOT EXISTS ON DB1.TABLE BASED ON EXISTING ID's            
var resultFinal = ( from e in result1
                    where !(from m in result2
                            select m.Id).Contains(e.Id)
                    select e
                  ).ToList();

I would also thank Robert Rouse on his anwser to this question and everybody else who tried to help.

Hope it helps someone else!

score 2 · Answer 2 · edited May 23 '17 at 12:31

2

According to this answer you can join tables over different databases with LINQ. Even join accross servers.

Another option is to read all the data you need in memory and join both tables in memory (you can still use LINQ for that).

edited May 23 '17 at 12:31

Community

1
1

answered Jan 04 '13 at 20:28

Milnev

97
1
5

Linq to Sql: Select only items from DB1-table1 that don't exist on DB2-table2

2 Answers2