none
Google-type query RRS feed

  • Question

  • I'm trying to run this Google-type query, but it is returning 2 rows. I'm positive it should ONLY be returning 1 row.  Any ideas? I'm kind of a newbie to Linq to Sql.

    (from d in Debtors
                               from da in DebtorAddresses
                               from dp in DebtorPhones
                               from de in DebtorEmails
                               where d.DebtorName.Contains("cori") ||
                                     da.Line1.Contains("cori") ||
                                     da.Line2.Contains("cori") ||
                                     da.City.Contains("cori") ||
                                     da.State.Contains("cori") ||
                                     da.ZipCode.Contains("cori") ||
                                     dp.PhoneNumber.Contains("cori") ||
                                     d.DebtorID.ToString().Contains("cori") ||
                                     d.SSN.Contains("cori") ||
                                     d.OriginalAmount.ToString().Contains("cori") ||
                                     de.EmailAddress.Contains("cori")
                               select d)
                          .Distinct()
                          .OrderBy(n => n.DebtorID)

    I'm running this using LINQPad.

    Thanks for your time!

    Brenda
    Thursday, October 2, 2008 8:04 PM

Answers

  • These are all inner joins.  An inner join *requires* that a match exists or now rows are returned.  So you would have to have matching records in all tables.

     

    You probably want something like this instead.

     

    from d in Debtors

    where d.DebtorID.ToString().Contains("cori")

          || d.SSN.Contains("cori")

          || d.OriginalAmount.ToString().Contains("cori")

          || DebtorAddress.Any(da => da.DebtorID == d.DebtorID

                                    && (da.Line1.Contains("cori")

                                       || da.Line2.Contains("cori")

                                       || da.City.Contains("cori")

                                       || da.State.Contains("cori")

                                       || da.ZipCode.Contains("cori"))) 

          || DebtorPhones.Any(dp => dp.DebtorID == d.DebtorID

                                    && dp.PhoneNumber.Contains("cori"))

          || DebtorEmails.Any(de => de.DebtorID == d.DebtorID

                                    && de.EmailAddress.Contains("cori"))

    select d;

     

     

    Using the 'Any' operator (EXISTS in SQL) allows you to check if any of the records in another table match w/o performing an inner join.  You also end up with just the debtors that matched the conditions w/o duplication.

     

     

    Thursday, October 9, 2008 5:41 PM
    Moderator

All replies

  • It looks like all your joins (additional froms) are based on entire tables w/o any logic to constrain them to just related rows (by matching keys, etc).  So, for example, if there is any address at all that contains "cori" all debtors will be returned.

    Thursday, October 2, 2008 9:11 PM
    Moderator
  • All those tables have DebtorID that links them together. How can I do this query different so it will work the way I am intending it to.

    Thanks!!!
    Thursday, October 2, 2008 9:28 PM
  • Okay, so when I do this, a row is returned:

    (from d in Debtors
                             where d.DebtorName.Contains("brenda")                                
                             select d)

    But when I do this, NO rows are returned:

    (from d in Debtors
                             join da in DebtorAddresses    
                                 on d.DebtorID equals da.DebtorID
                             join dc in DebtorContacts    
                                 on d.DebtorID equals dc.DebtorID
                             join de in DebtorEmails    
                                 on d.DebtorID equals de.DebtorID
                             join dpa in DebtorPayments    
                                 on d.DebtorID equals dpa.DebtorID
                             join dp in DebtorPhones    
                                 on d.DebtorID equals dp.DebtorID
                             where d.DebtorName.Contains("brenda")                                
                             select d)

    Can anyone see why? Thanks!
    Monday, October 6, 2008 5:08 PM
  • Any ideas out there?
    Thursday, October 9, 2008 5:23 PM
  • These are all inner joins.  An inner join *requires* that a match exists or now rows are returned.  So you would have to have matching records in all tables.

     

    You probably want something like this instead.

     

    from d in Debtors

    where d.DebtorID.ToString().Contains("cori")

          || d.SSN.Contains("cori")

          || d.OriginalAmount.ToString().Contains("cori")

          || DebtorAddress.Any(da => da.DebtorID == d.DebtorID

                                    && (da.Line1.Contains("cori")

                                       || da.Line2.Contains("cori")

                                       || da.City.Contains("cori")

                                       || da.State.Contains("cori")

                                       || da.ZipCode.Contains("cori"))) 

          || DebtorPhones.Any(dp => dp.DebtorID == d.DebtorID

                                    && dp.PhoneNumber.Contains("cori"))

          || DebtorEmails.Any(de => de.DebtorID == d.DebtorID

                                    && de.EmailAddress.Contains("cori"))

    select d;

     

     

    Using the 'Any' operator (EXISTS in SQL) allows you to check if any of the records in another table match w/o performing an inner join.  You also end up with just the debtors that matched the conditions w/o duplication.

     

     

    Thursday, October 9, 2008 5:41 PM
    Moderator
  • Thank you, thank you, thank you! That's exactly what I've needed. Thanks SO much for your time! Now I can continue working on this project Smile

    Brenda
    Thursday, October 9, 2008 5:49 PM