Home Ask Login Register

Developers Planet

Your answer is one click away!

Amel Salibasic February 2016

Linq Entity Framework - get all customers that Ids are not in many to many table

I have 2 tables:

Customer

Car

and many-to-many table MM, which stores:

Customer_Id Car_Id

How do I get all Customers which Car_Id's are not in many to many table?

I tried like this:

public async Task<IEnumerable<CustomerModel>> GetNewCustomersForCar(int carId)
        {
            var sentCustomers = await _unit.Repository<Car>().Queryable()
                .SelectMany(a => a.AspNetUsers, (b, a) => new { b, a })
                .Where(b => b.Id == carId)
                .Select(ba => new CustomerModel()
                {
                    Id = ba.a.Id,
                    Email = ba.a.Email
                })
                .ToListAsync();

            var allCustomers = await _unit.Repository<AspNetUser>().Queryable()
                .Select(c => new CustomerModel()
                {
                    Id = c.Id,
                    Email = c.Email
                }).ToListAsync();

            return allCustomers.Where(ac => !sentCustomers.Contains(ac));

So basically I select all customers for selected car, then I check all customers, and in the end I select from all customers that do not contain Id's from many to many customer table for selected customer.

Get all customers which didn't use car yet(all that used car are having id's for selected car in many to many table).

Answers


octavioccl February 2016

if you have a Cars navigation property in your AspNetUser entity, you could do this:

var query= await _unit.Repository<AspNetUser>()
                      .Queryable()
                      .Where(u=>!u.Cars.Any(c=>c.Id==carId))
                      .Select(c => new CustomerModel()
                                    {
                                        Id = c.Id,
                                        Email = c.Email
                                    })
                      .ToListAsync();

Also you can change your Where to .Where(u=>u.Cars.All(c=>c.Id!=carId)),could be more readable

Post Status

Asked in February 2016
Viewed 3,565 times
Voted 12
Answered 1 times

Search




Leave an answer


Quote of the day: live life