CODEWITHSUNDEEP
c#asp.net-mvcentity-framework
Ethan Pelton
Ethan Pelton

Reputation: 1796

Linq Group By or Distinct with Join

I may be going about this incorrectly, but I have a bookings table and a contacts table with a jointable connecting the two.

I need to get all bookings that have a related contact with an email containing a given string.

In other words, users want to search bookings by contact email.

this is what I've come up with. It works, but returns duplicate rows for each booking. I haven't been able to figure out how to group the rows or use a distinct method as in T-SQL...

if (!String.IsNullOrWhiteSpace(form["contactemail"]))
{
    string contactemail = form["contactemail"];
    IList<int> bookingids = bookings.Select(x => x.bookingid).ToList();
    IQueryable<contact> con = (from y in db.bookingscontacts where bookingids.Contains(y.bookingid) select y.contact);
    //EDIT:        I hadn't included the email filter...
    IQueryable<contact> conmatches = (from c in con where c.email1.Contains(contactemail) select c);
    IList<int> contactids = conmatches.Select(x => x.contactsid).ToList();

    bookings = (from r in bookings from c in db.bookingscontacts where contactids.Contains(c.contactsid) && bookingids.Contains(r.bookingid) select r);
}

Upvotes: 1

Views: 74

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109165

Let me assume that you have navigation properties, or otherwise you will start using them:

var bookings = from b in bookings
               where b.bookingscontacts
                      .Any(bc => bc.contact.email == contactemail)
               select b;

This will generate an EXISTS query, so the bookings are not duplicated.

Upvotes: 1

Related Questions