在Linq的外部联接中使用过滤器 [英] Use a filter in an outer join in 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的外部联接中使用过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！