locked
Linq to Sql Join w/ Not Equals? RRS feed

  • Question

  • User2125471824 posted

    Hey guys,

    Is there a way to write a Linq to Sql join with a not equals? Or alternativelly, I need to retrieve rows that aren't associated with a bridging table, so if anyone has suggestion I would appreciate them.

    Thanks in advance!

    Wednesday, June 24, 2009 6:56 PM

Answers

  • User2011918074 posted

    I basically want to write this Sql in Linq:

     

    1. SELECT * FROM [Files] JOIN [EmployeeFiles] ON ([EmployeeFiles].[FileId] <> [Files].[FileId]) JOIN [CustomerFiles] ON ([CustomerFiles].[FileId] <> [Files].[FileId])  
    SELECT * FROM [Files] JOIN [EmployeeFiles] ON ([EmployeeFiles].[FileId] <> [Files].[FileId]) JOIN [CustomerFiles] ON ([CustomerFiles].[FileId] <> [Files].[FileId])

     

    Based on your description, I think you would like to obtain the complementary set of the JOIN operation.

    For example,

    create table #Files (FileID int identity(1,1),FileName nvarchar(50))
    create table #EmployeeFiles (EmployeeFileID int identity(1,1), FileID int)
    create table #CustomerFiles (CustomerFileID int identity(1,1), FileID int)
    
    insert into #Files (FileName) values ('File1')
    insert into #Files (FileName) values ('File2')
    insert into #Files (FileName) values ('File3')
    insert into #Files (FileName) values ('File4')
    
    insert into #EmployeeFiles (FileID) values (1)
    insert into #EmployeeFiles (FileID) values (2)
    
    insert into #CustomerFiles (FileID) values (3)
    
    
    select * from #Files 
    where not exists (select 1 from #EmployeeFiles where #Files.FileID = #EmployeeFiles.FileID)
    and not exists (select 1 from #CustomerFiles where #Files.FileID = #CustomerFiles.FileID)
    
    
    Results:
    
    FileID      FileName
    ----------- --------------------------------------------------
    4           File4
    
    (1 row(s) affected)


     

    If it’s your desired output in T-SQL, we can use the following code in LINQ to SQL

    var query = from file in db.Files
                       where !db.EmployeeFiles.Any(employee => employee.FileID == file.FileID)
                       && !db.CustomerFiles.Any(customer => customer.FileID == file.FileID)
                       select file;

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 30, 2009 11:17 PM

All replies

  • User2011918074 posted

    Hi, 

    Could you please provide some sample data and your desired output? From your description, I think you want something like the following:

    var query = from city in db.Cities
                     where !db.Countries.Any(country => country.ID == city.ID)
                     select city; 

    It returns the cities which don’t exist in Countries table.

    Tuesday, June 30, 2009 3:26 AM
  • User2125471824 posted

    Hi Jian,

    Here's the scenario. I have a Files table, which can store general company files, employee files (via EmployeeFiles table) and customer files (via CustomerFiles table). Now when I'm inside a customer or employee section I can just do a join with the bridging tables and filter out what I don't want. I'm getting stuck on filtering out the employee and customer files when I just want the company files...

    I basically want to write this Sql in Linq:

    SELECT * FROM [Files] JOIN [EmployeeFiles] ON ([EmployeeFiles].[FileId] <> [Files].[FileId]) JOIN [CustomerFiles] ON ([CustomerFiles].[FileId] <> [Files].[FileId])

    I hope that helps you out on what I'm trying to do.

    Thanks in advance for any suggestions!

    Tuesday, June 30, 2009 11:33 AM
  • User2011918074 posted

    I basically want to write this Sql in Linq:

     

    1. SELECT * FROM [Files] JOIN [EmployeeFiles] ON ([EmployeeFiles].[FileId] <> [Files].[FileId]) JOIN [CustomerFiles] ON ([CustomerFiles].[FileId] <> [Files].[FileId])  
    SELECT * FROM [Files] JOIN [EmployeeFiles] ON ([EmployeeFiles].[FileId] <> [Files].[FileId]) JOIN [CustomerFiles] ON ([CustomerFiles].[FileId] <> [Files].[FileId])

     

    Based on your description, I think you would like to obtain the complementary set of the JOIN operation.

    For example,

    create table #Files (FileID int identity(1,1),FileName nvarchar(50))
    create table #EmployeeFiles (EmployeeFileID int identity(1,1), FileID int)
    create table #CustomerFiles (CustomerFileID int identity(1,1), FileID int)
    
    insert into #Files (FileName) values ('File1')
    insert into #Files (FileName) values ('File2')
    insert into #Files (FileName) values ('File3')
    insert into #Files (FileName) values ('File4')
    
    insert into #EmployeeFiles (FileID) values (1)
    insert into #EmployeeFiles (FileID) values (2)
    
    insert into #CustomerFiles (FileID) values (3)
    
    
    select * from #Files 
    where not exists (select 1 from #EmployeeFiles where #Files.FileID = #EmployeeFiles.FileID)
    and not exists (select 1 from #CustomerFiles where #Files.FileID = #CustomerFiles.FileID)
    
    
    Results:
    
    FileID      FileName
    ----------- --------------------------------------------------
    4           File4
    
    (1 row(s) affected)


     

    If it’s your desired output in T-SQL, we can use the following code in LINQ to SQL

    var query = from file in db.Files
                       where !db.EmployeeFiles.Any(employee => employee.FileID == file.FileID)
                       && !db.CustomerFiles.Any(customer => customer.FileID == file.FileID)
                       select file;

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 30, 2009 11:17 PM
  • User2125471824 posted

    Wow, I've been banging my head on this for a couple of days now, and not once did it occur to me to use Any()... I added your code and it worked like a charm! Thanks for all the help!

    Wednesday, July 1, 2009 12:13 AM
  • User-1848863872 posted

    Thanks Jian you saved my day! 

    Wednesday, August 11, 2010 2:35 PM
  • User2070517432 posted

    Hi Jian,

    Can you please provide the VB equivalent code for this solution?

    Thanks!

    Never mind... I figured it out... the syntax is ...Any(Function(x) x.Field = y.Field)

    Tuesday, July 12, 2011 1:47 PM