Hello, have you used the let statement in the query? This should solve the problem. Let me create simple example for you.
This should be it:
IQueryable<VE> ve = new List<VE>()
{
new VE()
{
Ac = "Ac1",
Ope = "Ope1",
SM = "SM1",
X = "X1"
},
new VE()
{
Ac = "X1",
Ope = "Ope2",
SM = "SM2",
X="X2"
},
new VE()
{
Ac = "X1",
Ope = "Ope2",
SM = "SM2",
X="X2"
},
new VE()
{
Ac = "X1",
Ope = "Ope2",
SM = "SM2",
X="X2"
}
}.AsQueryable();
IQueryable<VDA> vda = new List<VDA>()
{
new VDA()
{
MCode = "MCode1",
SMSe = "SM2",
Time = "Time1",
X="X1"
},
new VDA()
{
MCode = "MCode1",
SMSe = "SMSe1",
Time = "Time2",
X="X2"
}
}.AsQueryable();
IQueryable<CT> ct = new List<CT>()
{
new CT()
{
MCode = "MCode1",
Time = "Time1",
Mkt = "Mkt1",
Tkt = "SM1"
},
new CT()
{
MCode = "MCode1",
Time = "Time2",
Mkt = "Mkt2",
Tkt = "SM2"
},
new CT()
{
MCode = "MCode1",
Time = "Time2",
Mkt = "Mkt2",
Tkt = "SM2"
},
new CT()
{
MCode = "MCode1",
Time = "Time2",
Mkt = "Mkt2",
Tkt = "SM2"
}
}.AsQueryable();
IQueryable<LM> lm = new List<LM>()
{
new LM()
{
MCode = "MCode1",
Time = "Time1",
Mkt = "Mkt1",
MarketEsigi = "Mkt1"
},
new LM()
{
MCode = "MCode1",
Time = "Time2",
Mkt = "Mkt2",
MarketEsigi = "Mkt2"
},
new LM()
{
MCode = "MCode1",
Time = "Time2",
Mkt = "Mkt2",
MarketEsigi = "Mkt2"
},
new LM()
{
MCode = "MCode1",
Time = "Time2",
Mkt = "Mkt2",
MarketEsigi = "Mkt2"
},
new LM()
{
MCode = "MCode1",
Time = "Time2",
Mkt = "Mkt2",
MarketEsigi = "Mkt2"
}
}.AsQueryable();
var queryResult = from v in ve
join vax in vda on v.X equals vax.X
let ccc = ct.FirstOrDefault(c => c.Tkt == v.SM || c.Tkt == vax.SMSe)
join l in lm on ccc.Mkt equals l.MarketEsigi
where vax.Time == "Time2" && vax.MCode == "MCode1"
select v;
int totalSelected = queryResult.Count();
Let me know if it worked for you.