CODEWITHSUNDEEP
c#linqlinq-to-sql
Primico
Primico

Reputation: 2445

JOIN using LINQ LAMBDA

I am trying to get a list of customers with their addresses. In my database, Customer1 has 1 address and Customer2 has 2 addresses.

I am joining Customer with Address, like this:

var customers = _dbContext.Customer.Join(_dbContext.Address, c => c.Id, a => a.EntityId, (c, a) => new { Customer = c, Address = a });

I would like the result to be 2 Customers with addresses nested inside each. Instead, I get 3 records (a record for each address), as shown below. Does anyone know how I can adjust my query to get the result I am looking for? Thanks!

[{"customer":{"id":1,"companyName":"Customer2","firstName":"Donald","lastName":"Trump"},"address":{"id":5,"entityId":1,"city":"Atlanta","state":"GA","zip":"33333"}},

{"customer":{"id":1,"companyName":"Customer2","firstName":"Bill","lastName":"Clinton"},"address":{"id":7,"entityId":1,"city":"Gainesville","state":"FL","zip":"33333"}},

{"customer":{"id":2,"companyName":"Customer1","firstName":"Tom","lastName":"Hanks"},"address":{"id":9,"entityId":2,"city":"Miami","state":"FL","zip":"33333"}}]

Upvotes: 0

Views: 63

Answers (2)

Raghu
Raghu

Reputation: 136

This will solve your problem. You need to Group customers and loop for addresses of each customer.

var customers = _dbContext.Customer
                    .Join(_dbContext.Address
                    , c => c.Id
                    , a => a.EntityId, (c, a) => new {  c.Id, Address = a }).GroupBy(j => j.Id);

                foreach (var customer in customers)
                {
                    Console.WriteLine($"Customer :{customer.Key} , Address : { customer.Count() }");
                    foreach (var Addr in customer)
                    {
                        Console.WriteLine(Addr.xxxx);
                    }
                }

Upvotes: 0

Niyoko
Niyoko

Reputation: 7662

Try code below

var customers = _dbContext.Customer
    .GroupJoin(
        _dbContext.Address,
        c => c.Id,
        a => a.EntityId,
        (c, a) => new
        {
            c.Id,
            c.CompanyName,
            c.FirstName,
            c.LastName,                        
            Address = a.ToList()
        }
    );

Or using query style

var customers = from c in _dbContext.Customer
    join a in _dbContext.Address on c.Id equals a.EntityId into g
    select new
    {
        c.Id,
        c.CompanyName,
        c.FirstName,
        c.LastName,
        Address = g.ToList()
    };

Upvotes: 1

Related Questions