[Solved]-Pull data from multiple tables in one SQL query using LINQ and Entity Framework (Core)-LINQ,C#

try following. i models you database _context as a class just so i could test the syntax. remember that one customer may map to more than one transaction. you may want to use groupby id so you get 10 different customers.

    class program
    {


        static void main(string[] args)
        {
            context _context = new context();



            var ids = (from c in _context.customers
                       join t in _context.transactions on c.id equals t.customerid
                       select new { c = c, t = t})
                       .orderbydescending(x => x.c.created)
                       .take(10)
                       .tolist();

        }
    }

    public class context
    {
        public list<customer> customers { get; set; }
        public list<transaction> transactions { get; set; }
    }
    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; }
    }

you may want to try this instead :

            var ids = (from c in _context.customers
                       join t in _context.transactions on c.id equals t.customerid
                       select new { c = c, t = t})
                       .orderbydescending(x => x.c.created)
                       .groupby(x => x.c.id)
                       .selectmany(x => x.take(10))
                       .tolist();

eliminating the join will speed up results. you always can get the customer info in another query.

            var transactions = _context.transactions
                       .orderbydescending(x => x.created)
                       .groupby(x => x.customerid)
                       .select(x => x.take(10))
                       .tolist();

try this:

var customers = customerservice.getall().orderbydescending(c => c.created).take(10).tolist().asqueryable();
var transactions = transactionservice.getall().orderbydescending(t => t.created).take(10).tolist().asqueryable();

transactions = transactions.where(t => customers.any(c => c.customerid  == t.id));

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