Convert function to linq statment

Question

I have three tables in my database Subscriber, Subscription and Branch. Each Subscriber can have one Subscription in a specified Branch. I want to list all subscribers which have no subscription in a defined branch. For this purpose I wrote below function but I know it can be written more shortly and efficiently using LINQ operators but I have not enough knowledge to do this.

Can you change this function to a LINQ statement?

List<Subscriber> subscribersWithNoSubscriptinoInThisBranch(int branchId)
{
    DbSet<Subscriber> allSubscribers = db.Subscriber;
    List<Subscriber> subscribers = new List<Subscriber>();
    foreach (Subscriber s in db.Subscriber)
    {
        ICollection<Subscription> subscriptions = s.Subscription;
        if (subscriptions.Where(su => su.branchId == branchId).Count() == 0)
        {
            subscribers.Add(s);
        }
    }
    return subscribers;
}

How did you identify if above code is less efficient compared to a LINQ one? — Lucifer
– Lucifer, Commented Mar 2, 2020 at 13:07
@Lucifer This does one query for all subscribers, then does one query for each subscriber to get the count of subscriptions which is way less efficient than one query to do all of it on the DB. — juharr
– juharr, Commented Mar 2, 2020 at 13:14

Fabian Claasen · Accepted Answer · 2020-03-02 13:24:03Z

3

I believe the following is what you want:

List<Subscriber> subscribers = allSubscribers
    .Where(s => !s.Subscription.Any(su => su.branchId == branchId))
    .ToList();

edited Mar 2, 2020 at 13:24

answered Mar 2, 2020 at 13:13

Fabian Claasen

3043 silver badges14 bronze badges

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

4 Comments

Fabian Claasen Over a year ago

It depends on what you want to achieve. If you're not done with filtering yet you should not take the results in memory with a ToList. If this is the final result then yes add a ToList

VSB Over a year ago

This one lists all subscribers which have subscription in specified branch. I want those who have not subscriptions.

Crowcoder Over a year ago

I think OP wants the inverse of that, "subscribers which have no subscription in a defined branch"

Fabian Claasen Over a year ago

I will change it to !s.Subscription.Any(..)

Collectives™ on Stack Overflow

Convert function to linq statment

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related