Answered by:
Linq to Sql Join w/ Not Equals?

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:
- 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:
- 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