LINQ multiple condition on "on" clause

Question

I have a query which have multiple conditions on on clause

SELECT * FROM
CATALOGITEM with (nolock) INNER JOIN CATALOG with (nolock) ON CATALOGITEM.catalog_id = CATALOG.catalog_id and not(catalog.catalog_id = 21) AND NOT(catalog.catalog_id = 20)

INNER JOIN PRODUCT with (nolock) ON
CATALOGITEM.s_num = PRODUCT .s_num
LEFT OUTER JOIN PRODUCT_DETAIL with (nolock) ON PRODUCT_DETAIL.s_num = PRODUCT.s_num
WHERE (
CATALOGITEM.publish_code = 'upd' OR
CATALOG_ITEM.publish_code = 'ins' OR
PRODUCT.publish_code = 'upd' OR
PRODUCT.publish_code = 'ins'
)

and (CATALOG.unit_id = bu.unit_id)

How to write this in LINQ.

Please advice.

Answer 1

Assume you're missing a join to PRODUCT table?

Anyway, this should get you started.

var query = (from ci in db.catalogitem
         join c in db.catalog on ci.catalog_id equals c.catalog_id
         join p in db.products on ci.s_num equals p.s_num
         join pd in db.productdetail on p.s_num equals pd.s_num into tempprods
         from prods in tempprods.DefaultIfEmpty()
         where !(c.catalog_id.Contains(21, 20))
         && (ci.publish_code.Contains('upd','ins')) ||
            (p.publish_code.Contains('upd','ins'))
         select ci)

Answer 2

If you want to preserve the (NOLOCK) hints, I have blogged a handy solution using extension methods in C#. Note that this is the same as adding nolock hints to every table in the query.