How to make sql connections in lambda?

Question

From time to time I stumble upon this problem that I use a subset of lambda connections. Given that I can use any LINQ extensions, how should I implement the following associations:

For simplicity, the claims 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; } } 

---

Like my effort

((intersection not B) union (A intersects 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}); 

(A crosses B)

 var innerJoin = TableA .Join( TableB, a => a.Key, b => b.Key, (x, y) => x) 

(union B)

 var fullOuterJoin = TableA .FullOuterJoin( TableB, a => a.Key, b => b.Key, (x, y, Key) => new {x, y}) 

Answer 1

The most important thing for you is to know how to execute INNER JOIN and OUTER JOIN .

For an INNER JOIN, you use a JOIN from LINQ, for example:

 var result = TableA .Join(TableB, left => left.Id, right => right.ForeignKeyToTableA, (left, right) => new { TableAColumns = left, TableBColumns = right }); 

TOP COMPONENTS that you have already indicated in your example.

Now you need to mix what you know to get the desired results.

For example, to execute 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 

It will 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 }) ); 

Last example of your image:

 var fullOuterJoinOnlyWithNulls = fullOuterJoin .Where(p => p.TableA == null || p.TableB == null); 

A RIGHT OUTER JOIN is nothing more than a LEFT OUTER JOIN, where you change your result columns as follows:

 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 }) ); 

Similarly, you can build all the scripts of your example. Just check the tables for zero if necessary.