我有以下实体:
public class Company
{
public string CompanyName { get; set; }
public int ID { get; set; }
}
public class CompanyCurrency
{
public int Id { get; set; }
public int CompanyId { get; set; }
public decimal Rate { get; set; }
public int CurrencyId { get; set; }
}
public class Currency
{
public int ID { get; set; }
public string Name { get; set; }
}
我需要获取一个国家的货币清单。如果一个国家没有货币输入,我也需要为该输入缺少一行。
我现在的陈述是:
var currencies =
from c in Currencies
join cc in CompanyCurrency
on c.ID equals cc.CurrencyId
into jointable
from resultiten in jointable.DefaultIfEmpty()
select new {c.Name ,
HasEntry = resultiten == null ? 0:1,
rate = resultiten != null ? resultiten.Rate:0 ,
} ;
这不是由countryID过滤的。我试图通过添加过滤器
from c in Currencies
join cc in CompanyCurrency
on c.ID equals cc.CurrencyId
into jointable
from resultiten in jointable.DefaultIfEmpty()
where resultiten.CompanyId == 1 || resultiten == null
select new {c.Name ,
HasEntry = resultiten == null ? 0:1,
rate = resultiten != null ? resultiten.Rate:0
但是,对于没有公司ID 1的其他公司输入的货币,则没有结果。
对应的SQL查询为
select *
from [dbo].[Currency] c
left outer join [dbo].[CompanyCurrency] cc
on c.id = cc.Currencyid
and cc.[Companyid] = 1
您需要可以应用滤镜前的join
:
join cc in CompanyCurrency.Where(e => e.CompanyId == 1)
或作为联接的一部分
on new { CurrencyId = c.ID, CompanyId = 1 } equals new { cc.CurrencyId, cc.CompanyId }
对于inner join
s来说,它并不重要,但是outer join
很重要(对SQL查询同样适用)。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句