CODEWITHSUNDEEP
linq
pita
pita

Reputation: 537

linq to sql how to join on multiple columns and some condition

var recordhoursQuery = from  w in crnnsupContext.WorkHoursHistories
                               join p in crnnsupContext.ProvStates on w.Province equals p.ProvinceStateID
                               join r in crnnsupContext.Renewals on new {w.WorkYear+1,w.RegNumber} equals new{r.RenewalYear, r.RegNumber}
                               where r.RegNumber == _username
                               select new CRecordHours
                               {
                                   WorkHoursHistory = w,
                                   Renewal = r
                               };

How to implement a+1 equals b? Even when i remove +1, error still came said "The type of one of the expressions in the join clause is incorrect...."

Upvotes: 1

Views: 1019

Answers (2)

Ahmad Mageed
Ahmad Mageed

Reputation: 96477

You're receiving that error because your anonymous types are different. For WorkHoursHistories you're using WorkYear, but for Renewals you're using RenewalYear. For both, RegNumber is the same, so it doesn't need to be given a new name.

To fix this change your anonymous types to this:

new { Year = w.WorkYear + 1, w.RegNumber } equals
new { Year = r.RenewalYear, r.RegNumber }

Normally you could change one of the types to ensure the names are identical, but in your case Year is clearer. Also, if you omit assigning a name to w.WorkYear + 1 you would get an error.

Upvotes: 3

Arion
Arion

Reputation: 31239

Something like this:

from  w in crnnsupContext.WorkHoursHistories
join p in crnnsupContext.ProvStates on w.Province equals p.ProvinceStateID
from r in crnnsupContext.Renewals
          .Where(a=>a.RegNumber==w.RegNumber && (w.WorkYear+1)==r.RenewalYear)
where r.RegNumber == _username
select new CRecordHours
{
    WorkHoursHistory = w,
    Renewal = r
    };

Upvotes: 0

Related Questions