CODEWITHSUNDEEP
c#entity-frameworklinq
NetDev
NetDev

Reputation: 107

Using AND in JOIN in LINQ

I have such a SQL:

SELECT Items.ItemCode AS code, Items.ItemName AS name, ISNULL(PLID.PriceOverule,Items.ItemPrice) AS price from tblPLItemsDetail PLID
INNER JOIN tblItems Items ON PLID.ItemID = Items.ItemID 
AND Items.ValidityTo IS NULL
WHERE PLItemID = @PLItemID 
AND PLID.ValidityTo IS NULL 
AND (PLID.ValidityFrom >= @LastUpdated OR @LastUpdated IS NULL)

I put this on LINQ:

details = context.TblPlitemsDetail
          .Join(imisContext.TblItems, 
              p => p.ItemId, 
              i => i.ItemId, 
              (p, i) => new { TblPlitemsDetail = p, TblItems = i })
          .Where(r => r.TblPlitemsDetail.PlitemId == PLItemID 
              && r.TblPlitemsDetail.ValidityTo == null 
              && (r.TblPlitemsDetail.ValidityFrom >= Convert.ToDateTime(model.last_update_date) || model.last_update_date == null))
          .Select(x => new CodeNamePrice() 
          { 
              code = x.TblItems.ItemCode, 
              name = x.TblItems.ItemName, 
              price = (x.TblPlitemsDetail.PriceOverule == null) ? x.TblItems.ItemPrice.ToString() : x.TblPlitemsDetail.PriceOverule.ToString() 
          }).ToList();

I've rewritten most of the code but I have not implemented the AND Items.ValidityTo IS NULL predicate in the JOIN.

How to do it correctly?

Upvotes: 0

Views: 65

Answers (1)

strickt01
strickt01

Reputation: 4048

As it is an INNER JOIN you can specify the extra predicate (Items.ValidityTo IS NULL) in a LINQ Where clause:

details = context.TblPlitemsDetail
          .Join(imisContext.TblItems, p => p.ItemId, i => i.ItemId, (p, i) => new { TblPlitemsDetail = p, TblItems = i })
          .Where(r => !r.TblItems.ValidityTo.HasValue) 
          .Where(r => r.TblPlitemsDetail.PlitemId == PLItemID && r.TblPlitemsDetail.ValidityTo == null && (r.TblPlitemsDetail.ValidityFrom >= Convert.ToDateTime(model.last_update_date) || model.last_update_date == null))
          .Select(x => new CodeNamePrice() { code = x.TblItems.ItemCode, name = x.TblItems.ItemName, price = (x.TblPlitemsDetail.PriceOverule == null) ? x.TblItems.ItemPrice.ToString() : x.TblPlitemsDetail.PriceOverule.ToString() }).ToList();

Upvotes: 2

Related Questions