本文介绍了在Linq的外部联接中使用过滤器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下实体:
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; } }我需要获取一个国家的货币清单.如果一个国家没有货币条目,我也需要为该条目输入一行.
I need to get the list of currencies for a country. If a country does not have an entry for a currency I need a line for that missing entry too.
我现在的发言是:
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过滤的.我尝试通过
This is not filtered by a countryID . I tried to add a filter by
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的其他公司输入的货币,则没有结果.
But that does not have a result for a currency that has en entry for a company other then companyID 1.
对应的SQL查询为
select * from [dbo].[Currency] c left outer join [dbo].[CompanyCurrency] cc on c.id = cc.Currencyid and cc.[Companyid] = 1推荐答案
您需要在join之前应用过滤器:
You need to either apply the filter before the 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来说并不重要,但是对于outer join来说很重要(同样的btw适用于SQL查询).
For inner joins it doesn't really matter, but for outer join it's important (the same btw applies to SQL queries).
更多推荐
在Linq的外部联接中使用过滤器
发布评论