LINQ Join On Between Clause

Question

I am having some issues throwing together a LINQ query that will join a table based on a zip code. I need to join the table based on whether the customer's zip code lies with in a range of zip codes that is determined by ZIPBEG and ZIPEND columns.

The T-SQL would look something like this:

JOIN [ZipCodeTable] [zips] 
    ON [customer].[zipcode] BETWEEN [zips].[ZIPBEG] AND [zips].[ZIPEND]

-- or

JOIN [ZipCodeTable] [zips] 
    ON [zips].[ZIPBEG] <= [customer].[zipcode] 
        AND [zips].[ZIPEND] >= [customer].[zipcode]

Answer 1

You can't specifically join on this condition, the only kind of join that is offically supported is one based on equality, which your condition in T-SQL doesn't conform to.

Instead, you will have to perform a cartesian product and then filter on the appropriate conditions:

from c in customers
from z in zips
where
  z.ZipBeg <= c.ZipCode && c.ZipCode <= z.ZipEnd
select
  c