How to utilize "IN" statement with Linq <> ASP.NET MVC <> C#

Question

I am having struggles finding the best method to implement an IN statement.

Currently I am using the below code in my controller to return a list of stances specific an individual customer account.

return _context.Stances
               .ToList()
               .Select(Mapper.Map<Stances, StancesDto>)
               .Where(c => c.AccountGUID == userAccountID.CustomerGUID);

I am in the process of creating a partner portal that would allow a Partner user to have access to many other customer accounts that each individual customer provides them access to.

I setup a partnerLink table that stores a PartnerGUID and a CustomerGUID, marrying the relationship. The struggle I have is finding a method to allow a one to many relationship using an "IN" or "contains" option.

What I am looking to do is something like this:

either load the list if it is just a customer "OR" if it is a partner account Load all customer stances in the partnerLink table.

 var partners = _context.PartnerLinks
                        .Where(user => user.PartnerGUID == userAccountID.CustomerGUID)
                        .Select(user => user.AccountGUID) // extract the emails from users
                        .ToList();

  return _context.Stances
                 .ToList()
                 .Select(Mapper.Map<Stances, StancesDto>)
                 .Where(c => c.AccountGUID == userAccountID.CustomerGUID || partners.Contains(c.AccountGUID));

Do you know that you have loaded whole table into the memory? — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Jul 30, 2021 at 18:08
No I am not aware of that I am loading whole table. What part is that? — Steve Wolfe
– Steve Wolfe, Commented Jul 30, 2021 at 18:12
Where you have ToList that immediately loads an entire table into memory. Also, you should map to a DTO after you have filtered what you want to retrieve. Generally, Select should be the last thing you do. — NetMage
– NetMage, Commented Jul 30, 2021 at 18:12
Do you think you can try to re-write what you want to accomplish more clearly? — NetMage
– NetMage, Commented Jul 30, 2021 at 18:13
Thank you, I will make sure to remove that in other areas as well — Steve Wolfe
– Steve Wolfe, Commented Jul 30, 2021 at 18:13

Svyatoslav Danyliv · Accepted Answer · 2021-07-30 18:22:49Z

2

You should not use ToList when combining LINQ queries. Everything should be IQueryable.

var partners = _context.PartnerLinks
    .Where(user => user.PartnerGUID == userAccountID.CustomerGUID)
    .Select(user => user.AccountGUID);

return _context.Stances
    .Where(c => c.AccountGUID == userAccountID.CustomerGUID || partners.Contains(c.AccountGUID))
    .AsEnumerable()
    .Select(Mapper.Map<Stances, StancesDto>);

Also consider to use Automapper's ProjectTo

return _context.Stances
    .Where(c => c.AccountGUID == userAccountID.CustomerGUID || partners.Contains(c.AccountGUID))
    .ProjectTo<StancesDto>(configuration);

answered Jul 30, 2021 at 18:22

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sam Ware · Accepted Answer · 2021-07-30 18:24:03Z

0

I would think you could do it with a Union.

Sometime like:

return _context.Stances.Select(Mapper.Map<Stances, StancesDto>).Where(c => c.AccountGUID == userAccountID.CustomerGUID)
.Union(_context.Stances.Select(Mapper.Map<Stances, StancesDto>).Where(c => partners.Contains(c.AccountGUID)));

answered Jul 30, 2021 at 18:24

Sam Ware

1261 silver badge10 bronze badges

Collectives™ on Stack Overflow

How to utilize "IN" statement with Linq <> ASP.NET MVC <> C#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related