CODEWITHSUNDEEP
c#.netasp.net-mvclinqlinq-to-sql
developer033
developer033

Reputation: 24864

Linq to DataSet

I want to pick up all Sellers that aren't boss of a department.

How can I make this? In this query below, only the Sellers that are bosses of a department are picked up, I want the opposite of thereof.

My query:

var query = db.Sellers
            .Join(db.Departments,
            s => s.Id,
            d => d.BossId,
            (s, d) => new { Seller = s, Department = d })
            .Where(a => a.Seller.Id == a.Department.BossId) ????
            .Select(x => x.Seller).ToList();

In the "Where" part, I tried a => a.Seller.Id != a.Department.BossId, but it's wrong I have 3 sellers that aren't bosses.

I tried with this way too:

var listNonBoss = (from s in db.Sellers
                  join d in db.Departments on s.Id equals d.BossId
                  select s.Id).ToList();

I want just the opposite of these queries.

Upvotes: 4

Views: 127

Answers (2)

Nic
Nic

Reputation: 12846

Join in your code will do an inner join, meaning it'll filter out sellers who don't have a boss.

To do the opposite you can do an outer join, and then remove the ones who have a boss. In fluent LINQ an outer join is done by doing a GroupJoin and then SelectMany.

Something like this:

var query = db.Sellers
    .GroupJoin(db.Departments, s => s.Id, d => d.BossId, (s, d) => new { Seller = s, Department = d })
    .SelectMany(x => x.d.DefaultIfEmpty(), (seller, department) => new { s.seller, department})
    .Where(a => a.department.BossId == null)
    .Select(x => x.Seller).ToList();

Or, using query syntax:

var listNonBoss = (from s in db.Sellers
                  join d in db.Departments on s.Id equals d.BossId into joinedTable
                  from jt in joinedTable.DefaultIfEmpty()
                  where jt.BossId == null
                  select s.Id).ToList();

Upvotes: 1

Grant Winney
Grant Winney

Reputation: 66439

Sometimes it's easier to break it into multiple steps.

First, get the collection of all boss IDs:

var bossIDs = db.Departments.Select(x => x.BossId);

Then get all sellers whose IDs are not in that collection:

var listNonBoss = db.Sellers.Where(x => !bossIDs.Contains(x.Id)).ToList();

Upvotes: 2

Related Questions