locked
Like operator on Join clause RRS feed

  • Question

  • I have posted this on the linq group as well as it as a general question as well.

     

    I have an sql query that I want join onto a subquery, which contains joins. For performance issues I want to put the where clauses on the join. Basically I want the join to filter on the customerkey and do a like on the on the surname but the on clause only allows the equals operator. Is there a way round this (I am also using linq to entities - I don't know whether that is relevent)

     

    Dim cust As IQueryable(Of PeninsulaModel.VW_IMPORTEDCUSTOMERS) = _
    (From c In ctx.VW_IMPORTEDCUSTOMERS _
    Join a In _
    (From cc In ctx.TBLCompanyContacts Select New tmp_union _
    With {.Ckey = cc.CustomerKey, .Surname = cc.Surname, .Forename = cc.Forename, .Tel = cc.TelNoMain}).Union _
         (From sa In ctx.TBLSiteAddresses Select New tmp_union _
         With {.Ckey = sa.CustomerKey, .Surname = "", .Forename = "", .Tel = sa.PhoneMain}) _

    On a.Ckey Equals c.customerkey and a.Surname.StartsWith("a"))

    Thursday, October 9, 2008 3:26 PM

Answers

  • You can only use the join clause to join on the equality of one or many fields. If they're ALL equal, then it's a match, if not, then not. The only operators supported in the join clause are 'and' and 'equals'. If you only use inner join, then you could use a cross join instead to emulate an inner join and filter in the where condition, e.g:

    Code Snippet

    From a in db.Table1 _

    From b in db.Table2 _

    Where a.Something=b.Something And a.SomethingElse.StartsWith("a")

     

     

    This practically results in the same query plan in SQL server as if you were using an inner join with a complex join predicate.
    Thursday, October 9, 2008 5:42 PM

All replies

  • Untested:

     

    You can try a.Surname.IndexOf("a") <> -1

     

    A.D.t.

     

    Thursday, October 9, 2008 3:29 PM
  • No it seems to expect a equals (i.e. it won't compile)

    Thursday, October 9, 2008 4:41 PM
  • You can only use the join clause to join on the equality of one or many fields. If they're ALL equal, then it's a match, if not, then not. The only operators supported in the join clause are 'and' and 'equals'. If you only use inner join, then you could use a cross join instead to emulate an inner join and filter in the where condition, e.g:

    Code Snippet

    From a in db.Table1 _

    From b in db.Table2 _

    Where a.Something=b.Something And a.SomethingElse.StartsWith("a")

     

     

    This practically results in the same query plan in SQL server as if you were using an inner join with a complex join predicate.
    Thursday, October 9, 2008 5:42 PM