CODEWITHSUNDEEP
c#asp.netlinq
micha
micha

Reputation: 69

linq query for no match rows

I would like to know how to do a query that returns rows that have NO match.

For example: The query should return the list of people that have not rented any movies since a given date.

I wrote this code but I was helped by a foreach sentence.

Thanks in advance, micha.

// dt is a date from the user
public IEnumerable inActiveRenters(DateTime dt)
    {
        var rents = from r in myDb.Rents
                    where r.RentStart > dt
                    select r;

        List<Member> memberList = new List<Member>();

        foreach (Member m in myDb.Members)
        {
            bool notRent = true;
            foreach (Rent r in rents)
            {
                if (r.MemberID == m.MemberID)
                {
                    notRent = false;
                }
            }
            if (notRent)
            {
                memberList.Add(m);
            }
        }

        var list = from m in memberList
                   select new { m.MemberID, m.FirstName };

        return list;
    }

Upvotes: 0

Views: 1927

Answers (4)

Homam
Homam

Reputation: 23841

This should work:

from r in myDb.Rents.Where(p => !myDB.Members.Any(m => m.MemberID == p.MemberID))
where r.RentStart > dt
select r;

Upvotes: 1

k.m
k.m

Reputation: 31444

Members that have not rented any movie:

var rentsSinceDate = myDb.Rents.Where(r => r.RentStart > dt);
var notRentedAnyMovie = myDb.Members
    .Where(m => !rentsSinceDate.Any(r => r.MemberID == m.MemberID))
    .Select(m => new { m.MemberID, m.FirstName });

Upvotes: 2

corlettk
corlettk

Reputation: 13574

Micha,

AFAIK there's "a few" ways to do this, dependant upon which database you're using.

  1. LEFT JOIN WHERE NULL. For example 

    SELECT c.* 
FROM customer c 
LEFT JOIN rentals r 
ON r.cutomer_id is null 
WHERE r.date >= ${theCutOffDate}

  2. NOT EXISTS

    SELECT c.* 
FROM customer c 
WHERE NOT EXISTS(
  SELECT 1 
  FROM rentals 
  WHERE date >= ${theCutOffDate}
)

  3. NOT IN

    SELECT c.* 
FROM customer c 
WHERE c.customer_id NOT IN(
  SELECT customer_id
  FROM rentals 
  WHERE date >= ${theCutOffDate}
)

Please note: There very well may be ways I haven't seen (yet).

Option 1: The left join is probably the most uniformly well supported, so I'd recommend it (in the absence of other pertinant information, like which freeken RDBMS you're using.)

Cheers. Keith.

Upvotes: 1

Femaref
Femaref

Reputation: 61427

myDb.Members
       .Join(myDb.Rents
                   .Where(r => r.RentStart < dt), 
             m => m.MemberID, 
             r => r.MemberID, 
             (m, r) => m);

Upvotes: 1

Related Questions