c# - 使用 LINQ 和 Entity Framework (核心)从一个 SQL 查询中的多个表中提取数据

Question

我想在我的 .NET Core 应用程序中使用 LINQ 从多个表中提取数据。这是一个例子:

public class Customer {
    public Guid Id { get; set; }
    public string Name { get; set; }
    public DateTime Created { get; set; }

    public HashSet<Transaction> Transactions { get; set; }
}

public class Transaction {
    public Guid Id { get; set; }
    public decimal Amount { get; set; }
    public DateTime Created { get; set; }

    public Guid CustomerId { get; set; }
    public Customer Customer { get; set; }
}

这些在我的解决方案中具有一对多关系。一个客户有很多交易，一笔交易有一个客户。如果我想在一个 LINQ 查询中获取 10 个最新交易和 10 个最新客户，我该怎么做？我读过 .Union() 应该可以做到，但它对我不起作用。示例:

var ids = _context
    .Customers
    .OrderByDescending(x => x.Created)
    .Take(10)
    .Select(x => x.Id)
    .Union(_context
        .Transactions
        .OrderByDescending(x => x.Created)
        .Take(10)
        .Select(x => x.CustomerId)
    )
    .ToList();

这为我提供了两个 Guid 类型的列表，但它们包含相同的元素。不确定是不是只有我一个人理解错了，但看起来有点奇怪。只要它向数据库询问一次，我就很高兴。

Answer 1

你写道:

I wanted to grab the 10 latest transactions and 10 latest customers in one LINQ query

有点不清楚你想要什么。我怀疑您是否想要一个混合了客户和交易的序列。我猜您想要 10 个最新的客户，每个客户都有他们最近的 10 笔交易？

我想知道你为什么会偏离 entity framework code first conventions .如果您的类 Customer 代表数据库中的一行，那么它肯定没有 HashSet<Transaction> ？

一对多的 Customer和他的 Transactions应该建模如下:

class Customer
{
    public int Id {get; set;}
    ... // other properties

    // every Customer has zero or more Transactions (one-to-many)
    public virtual ICollection<Transaction> Transactions {get; set;}
}
class Transaction
{
    public int Id {get; set;}
    ... // other properties

    // every Transaction belongs to exactly one Customer, using foreign key
    public int CustomerId {get; set;}
    public virtual Customer Customer {get; set;}
}

public MyDbContext : DbContext
{
    public DbSet<Customer> Customers {get; set;}
    public DbSet<Transaction> Transactions {get; set;}
}

这就是 Entity Framework 检测您要创建的表、检测一对多关系以及检测主键和外键所需知道的全部内容。仅当您想要表或列的不同名称时，您才需要属性和/或流畅的 API

我的类和你的类之间的主要区别在于，一对多关系由虚拟 属性表示。 HashSet 是一个 ICollection。毕竟，你的Transactions table 是行的集合，不是 HashSet

In entity framework the columns of your tables are represented by non-virtual properties; the virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)

很多人在使用 Entity Framework 时倾向于(分组)连接表。但是，如果您使用虚拟属性，生活会容易得多

回到你的问题

I want (some properties of) the 10 newest Customers, each with (several properties of) their 10 latest Transactions

var query = dbContext.Customers                           // from the collection of Customer
    .OrderByDescending(customer => customer.Created)      // order this by descending Creation date
    .Select(customer => new                               // from every Customer select the
    {                                                     // following properties
         // select only the properties you actually plan to use
         Id = Customer.Id,
         Created = Customer.Created,
         Name = Customer.Name,
         ...

         LatestTransactions = customer.Transactions        // Order the customer's collection
             .OrderBy(transaction => transaction.Created)  // of Transactions
             .Select(transaction => new                    // and select the properties
             {
                 // again: select only the properties you plan to use
                 Id = transaction.Id,
                 Created = transaction.Created,
                 ...

                 // not needed you know it equals Customer.Id
                 // CustomerId = transaction.CustomerId,
             })
             .Take(10)                                      // take only the first 10 Transactions
             .ToList(),
    })
    .Take(10);                                              // take only the first 10 Customers

Entity Framework 了解一对多关系并认识到为此需要组连接。

查询中速度较慢的部分之一是将所选数据从 DBMS 传输到本地进程。因此，明智的做法是将所选数据限制为您实际计划使用的数据。如果 ID 为 4 的客户有 1000 个交易，则为每个交易传输外键是一种浪费，因为您知道它的值为 4。

如果您真的想自己加入:

var query = dbContext.Customers                 // GroupJoin customers and Transactions
    .GroupJoin(dbContext.Transactions,
    customer => customer.Id,                    // from each Customer take the primary key
    transaction => transaction.CustomerId,      // from each Transaction take the foreign key
    (customer, transactions) => new             // take the customer with his matching transactions
    {                                           // to make a new:
       Id = customer.Id,
       Created = customer.Created,
       ...

       LatestTransactions = transactions
           .OrderBy(transaction => transaction.Created)
           .Select(transaction => new
           {
               Id = transaction.Id,
               Created = transaction.Created,
               ...
           })
           .Take(10)
           .ToList(),
       })
       .Take(10);