LINQ Group by joining multiple tables / classes

Question

I have a table structure in my DB as follows:

Items <=  User  => Transactions

So, in C # mapped classes, the User contains a collection of elements and transactions ... (from 1 to many relationships).

So now I have done the 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 am grouping the data in a transaction table ... Now, what I would like to do here, if possible, is to simply move to the User table in the Items table and select the specific property that I need for this grouped item , i.e. CurrentPrice, and the Transactions table does not contain this data ...

So, in the select clause, I would like to display the CurrentPrice property as follows:

.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?

Answer 1

, , SearchUserId,

.. , UserId User Transactions Items

    var result = (from usr in ctx.User
                 where  usr.UserId == firstRequest.SearchedUserID
                 select new {
                    UserId = usr.UserId,
                    Transactions = usr.Transactions,
                    Items = usr.Items
                 })

, Group,... . , , Items Transactions.