sql如何在lambda中加入? [英] How to do sql joins in lambda?

问题描述

有时，我会偶然发现这个问题，因为我使用了lambda连接的子集.鉴于我可以使用任何LINQ扩展，我应该如何实现以下联接:

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

或者如果您更喜欢代码

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

(A与B相交)

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

(工会B)

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

推荐答案

对您来说最重要的是，知道如何执行INNER JOIN和OUTER JOIN.

The most important thing for you, is to know how to perform an INNER JOIN and an OUTER JOIN.

对于 INNER JOIN ，您可以像下面这样使用LINQ中的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 });

您已在示例中显示的外部联接.

现在，您需要混合使用已知的知识，以获得所需的结果.

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

例如，要执行 FULL OUTER JOIN ，请在LINQ中执行类似此伪代码的操作:

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

这将在LINQ中如下所示:

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

像这样，您可以构建所有示例场景.只需在需要时检查表中的 null .

Like this you can construct all your example scenarios. Just check the tables for null when needed.

这篇关于sql如何在lambda中加入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！