Search code examples
c#linq-to-sqlmulti-database

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


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


Solution

  • 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!