LINQ Group by performing join across multiple tables/classes

Viewed 65

I have a table structure in my DB like this:

Items <=  User  => Transactions

So in C# mapped classes User contains a collection of Items and Transactions... (1 to many relationship)..

So now what I've done is following:

   var filteredProducts = ctx.Transactions.Where(x => x.SearchedUserID == 
    firstRequest.SearchedUserID)
    .OrderByDescending(x => x.TransactionDate)
    .GroupBy(x => x.TransactionID ).Select(x => new ResultItem()
    {
     TransactionID = x.Key,
     SaleNumber = x.Sum(y => y.QuantityPurchased)                 
     })
     .ToList();

So as you can see I'm grouping by data in transactions table... Now what I would like to do here, if it's possible, in an easy manner to go across the User table into Items table and select a specific property that I need from it for that grouped item, which is CurrentPrice, and the table Transactions doesn't contains this data...

So in the select statement I'd like to pull out the CurrentPrice property like this :

.Select(x => new ResultItem()
        {
         TransactionID = x.Key,
         SaleNumber = x.Sum(y => y.QuantityPurchased),
         CurrentPrice = // somehow go to Items table and pick up this data 
         })
         .ToList();

Can someone help me out ?

1 Answers
Related