How to do sql joins in lambda?
The most important thing for you, is to know how to perform an INNER JOIN
and an OUTER JOIN
.
For the INNER JOIN you use JOIN
from LINQ like so:
var result =
TableA
.Join(TableB, left => left.Id, right => right.ForeignKeyToTableA,
(left, right) => new { TableAColumns = left, TableBColumns = right });
The OUTER JOIN you already showed in your example.
Now you need to mix what you know, to get the desired results.
For example to perform a FULL OUTER JOIN do something like this pseudocode in LINQ:
SELECT TableA.*, TableB.* FROM TableA LEFT OUTER JOIN TableB
UNION
SELECT TableA.*, TableB.* FROM TableB LEFT OUTER JOIN TableA
This would be in LINQ as follows:
var fullOuterJoin =
(
TableA
.GroupJoin(TableB,
left => left.Id, right => right.ForeignKeyId,
(left, right) => new { TableA = left, TableB = right })
.SelectMany(p => p.TableB.DefaultIfEmpty(), (x, y) =>
new { TableA = x.TableA, TableB = y })
)
.Union
(
TableB
.GroupJoin(TableA,
left => left.Id, right => right.ForeignKeyId,
(left, right) => new { TableA = right, TableB = left })
.SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) =>
new { TableA = y, TableB = x.TableB })
);
The very last example of your image would then be:
var fullOuterJoinOnlyWithNulls =
fullOuterJoin
.Where(p => p.TableA == null || p.TableB == null);
A RIGHT OUTER JOIN is nothing but a LEFT OUTER JOIN where you swap your result columns like this:
var rightOuterJoin =
(
TableB
.GroupJoin(TableA,
left => left.Id, right => right.ForeignKeyId,
(left, right) => new { TableA = right, TableB = left })
.SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) =>
new { TableA = y, TableB = x.TableB })
);
Like this you can construct all your example scenarios. Just check the tables for null when needed.
Margus
Margus Martsepp got his higher education in the field of Informatics from TalTech (Tallinn University of Technology, class of 2006, 2010, 2013) in Estonia. He started coding in 2001 Spring and has been actively accumulating knowledge since. He has worked as .Net developer in Uptime, AgileWorks and is currently working in Gunvor Services SA. {LinkedIn, Careers} "Figure out what you are doing wrong and stop doing it" - Eric Lippert
Updated on June 01, 2022Comments
-
Margus almost 2 years
From time-to-time, I stumble on this problem that I use a subset of lambda joins. Given that I can use any LINQ extensions how should I go about implementing following joins:
For simplicity sake tables are defined as
CREATE TABLE [dbo].[TableA] ( [Key] INT IDENTITY (1, 1) NOT NULL, [Value] NVARCHAR (MAX) NULL, CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ([Key] ASC) ); CREATE TABLE [dbo].[TableB] ( [Key] INT IDENTITY (1, 1) NOT NULL, [Value] NVARCHAR (MAX) NULL, CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED ([Key] ASC) );
or if you prefer code first
public class TableContext : DbContext { public DbSet<B> TableB { get; set; } public DbSet<A> TableA { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(ConnectionString); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<TableB>().Property(o => o.Key).UseSqlServerIdentityColumn(); modelBuilder.Entity<TableA>().Property(o => o.Key).UseSqlServerIdentityColumn(); } } public class B : IKeyValue { public int Key { get; set; } public string Value { get; set; } } public class A : IKeyValue { public int Key { get; set; } public string Value { get; set; } } public interface IKeyValue { int Key { get; set; } string Value { get; set; } }
As my effort
((A intersect not B) union (A intersect B))
var leftOuterJoin = TableA .GroupJoin( TableB, a => a.Key, b => b.Key, (x, y) => new { TableA = x, TableA = y }) .SelectMany( x => x.TableB.DefaultIfEmpty(), (x, y) => new { TableA = x.TableA, TableB = y});
var innerJoin = TableA .Join( TableB, a => a.Key, b => b.Key, (x, y) => x)
var fullOuterJoin = TableA .FullOuterJoin( TableB, a => a.Key, b => b.Key, (x, y, Key) => new {x, y})