如何将多个带有where子句的SQL左连接语句转换为LINQ

是否有办法将以下SQL语句转换为LINQ?

select ve.EntityID
         , fin1.FinanceStat as FinanceStat_New
         , fin2.FinanceStat as FinanceStat_Old
    from ValuationEvents_PIT_New as ve
    left join FinStat_New as Fin1
    on ve.EntityID = Fin1.EntityID
    left join FinStat_Old as Fin2
    on ve.EntityID = Fin2.EntityID
    where Fin1.FinanceStat ne Fin2.FinanceStat
        and Fin2.FinanceStat is not null
        and charindex(Fin1.FinanceStat, 'abc') < 1
        and charindex(Fin1.FinanceStat, 'xyz') < 1

这是我的版本，但我需要一双额外的眼睛来看待它。

var result = (from ve in valuationEventsPit
       join fsn in finStatNew on ve.EntityId equals fsn.EntityID into veFsn
       from fin1 in veFsn.DefaultIfEmpty()
       join fso in finStatOld on ve.EntityId equals fso.EntityID into veFso
       from fin2 in veFso.DefaultIfEmpty()
       select new
       {
            ve.EntityId,
            FinStatNew1 = fin1 == null ? null : fin1.FinanceStat,
            FinStatNew2 = fin2 == null ? null : fin2.FinanceStat
       }).
       Where(x => x.FinStatNew1 != null &&
            x.FinStatNew2 != null &&
            x.FinStatNew1 != x.FinStatNew2 &&
            !(x.FinStatNew1.Contains("abc")) &&
            !(x.FinStatNew1.Contains("xyz"))).ToList();

我排除x.FinStatNew1 == null的原因是因为charindex(Fin1。金融，'abc') <1，如果x.FinStatNew1不为空，'abc'或'xyz'不存在，则返回0，如果x.FinStatNew1为空，则返回null, condition仍然为假(null <</p> 0)。

非常感谢你的帮助

const string con1 = "abc";
const string con2 = "xyz";
var query =
    from ve in valuationEventPit
    join fsn in finStatNew on ve.EntityId equals fsn.EntityID
    join fso in finStatOld on ve.EntityId equals fso.EntityID
    let FinStatNew = fsn.FinanceStat
    let FinStatOld = fso.FinanceStat
    where FinStatNew != FinStatOld && FinStatOld != null
       && new[]{con1,con2}.All(con => !FinStatNew.Contains(con))
    select new { ve.EntityId, FinStatNew, FinStatOld };