LINQ query with Multiple Tables?

Question

I am joining the two tables using LINQ to match model class like below

    lstOptInInterest = new LinkedList<OptInInterestArea>
                       ((from a in dbEntities.SUBCODE
                       from appCode in dbEntities.CODE.Where(
                          x => x.CODE == a.CODE  && x.TYPE == a.TYPE)
                        select new OptInInterestArea()
                        {
                               Code = a.CODE,
                               SubCode = a.SUBCODE,
                               SubCodeDescription = a.DESCR,
                               CodeDescription = appCode.DESCR
                          }).ToList());

Model class

public class OptInInterestArea
{      
        [DisplayName("Code")]
        public string Code { get; set; }

        [DisplayName("Sub Code")]
        public string SubCode { get; set; }

        DisplayName("Sub Code Description")]
        public string SubCodeDescription { get; set; }

        [DisplayName("Code Description")]
        public string CodeDescription { get; set; }

        [DisplayName("Previous OptIn")]
        public bool PrevOptIn { get; set; }
}

Table B

Now my question is I need to assign PrevOptIn values of lstOptInInterest from Table B (see above). Table B may or may not contains all of CODE and SUBCODE of lstOptInInterest.

If CODE and SUBCODE of lstOptInInterest exists on the table B assign to PrevOptIn in else PrevOptIn = N

How can I do LINQ to get this?

Answer 1

first, I suggest you use INNER JOIN instead of CROSS JOIN to query your lstOptInInterest :

lstOptInInterest = new LinkedList<OptInInterestArea>
                       ((from a in dbEntities.SUBCODE
                         join appCode in dbEntities.CODE
                         on new {CODE=a.CODE,TYPE=a.TYPE} equals new {CODE=x.CODE,TYPE=x.TYPE}
                         select new OptInInterestArea()
                          {
                               Code = a.CODE,
                               SubCode = a.SUBCODE,
                               SubCodeDescription = a.DESCR,
                               CodeDescription = appCode.DESCR
                          }).ToList());

seconde, use LEFT OUT JOIN to assign PrevOptIn values of lstOptInInterest from Table B :

lstOptInInterest = new LinkedList<OptInInterestArea>
            (
                  (from a in lstOptInInterest
                          join b in dbEntities.TableB
                          on new {CODE=a.Code,SUBCODE=a.SubCode} equals new {CODE=b.CODE,SUBCODE=b.SUBCODE}
                          into leftGroup
                          from b in leftGroup.DefaultIfEmpty()
                          select new OptInInterestArea()
                          {
                               Code = a.Code,
                               SubCode = a.SubCode,
                               SubCodeDescription = a.SubCodeDescription,
                               CodeDescription = a.CodeDescription,
                               PrevOptIn=b==null? false : b.OPTIN=="Y"
                          }).ToList()
            );

UPDATE: Try to test this only 1 steps to get your result:

lstOptInInterest = new LinkedList<OptInInterestArea>
                       ((from a in dbEntities.SUBCODE
                         join appCode in dbEntities.CODE
                         on new {CODE=a.CODE,TYPE=a.TYPE} equals new {CODE=x.CODE,TYPE=x.TYPE}
                         join b in dbEntities.TableB
                         on new {CODE=a.CODE,SUBCODE=a.SUBCODE} equals new {CODE=b.CODE,SUBCODE=b.SUBCODE}
                         into leftGroup
                         from b in leftGroup.DefaultIfEmpty()
                         select new OptInInterestArea()
                          {
                               Code = a.CODE,
                               SubCode = a.SUBCODE,
                               SubCodeDescription = a.DESCR,
                               CodeDescription = appCode.DESCR,
                               PrevOptIn=b==null?false : b.OPTIN=="Y"
                          }).ToList());