在Linq的外部联接中使用过滤器

本文介绍了在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).