I want to pull data from multiple tables using LINQ in my .NET Core application. Here's an example:
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; }
}
These have a one-to-many relation in my solution. One customer has many transactions and one transaction has one customer. If I wanted to grab the 10 latest transactions and 10 lastest customers in one LINQ query, how would I do that? I've read that .Union()
should be able to do it, but it won't work for me. Example:
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();
This gives me two lists of type Guid
, but they contain the same elements. Not sure if it's just me who understands this wrong, but it seems a bit weird. I am happy as long as it asks the database once.
You wrote:
I wanted to grab the 10 latest transactions and 10 latest customers in one LINQ query
It is a bit unclear what you want. I doubt that you want one sequence with a mix of Customers and Transactions. I guess that you want the 10 newest Customers, each with their last 10 Transactions?
I wonder why you would deviate from the entity framework code first conventions. If your class Customer represents a row in your database, then surely it doesn't have a HashSet<Transaction>
?
A one-to-many of a Customer
with his Transactions
should be modeled as follows:
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;}
}
This is all that entity framework needs to know to detect the tables you want to create, to detect your one-to-many relationship, and to detect the primary keys and foreign keys. Only if you want different names of tables or columns, you'll need attributes and/or fluent API
The major differences between my classes and yours, is that the one-to-many relation is represented by virtual properties. The HashSet is an ICollection. After all, your Transactions
table is a collection of rows, not a 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, ...)
Quite a lot of people tend to (group-)join tables, when they are using entity framework. However, life is much easier if you use the virtual properties
Back to your question
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 knows the one-to-many relationship and recognizes that a group-join is needed for this.
One of the slower parts of your query is the transfer of the selected data from the DBMS to your local process. Hence it is wise to limit the selected data to the data you actually plan to use. If Customer with Id 4 has 1000 Transactions, it would be a waste to transfer the foreign key for every Transaction, because you know it has value 4.
If you really want to do the join yourself:
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);