如何在Entity Framework Core中通过单个查询实现两个查询结果的除法运算？

Hey there! Great question—running two separate queries here is totally avoidable, and combining them will make your code more efficient by cutting down on database round-trips. Let’s walk through a couple of clean, practical ways to do this.

Option 1: Fetch Both Rates in One Query, Calculate in Memory #

This approach pulls both required exchange rates in a single database call, then computes the division in your application code. It’s straightforward and gives you flexibility to handle edge cases like missing rates:

// Get both relevant rates in one go
var relevantRates = await AsyncExecuter.ToListAsync(
    from er in await _exchangeRateRepository.GetQueryableAsync()
    where er.FirmId == input.FirmId
          && er.BeginDate <= input.ProcessDate
          && er.EndDate >= input.ProcessDate
          // Filter only for the two currencies we care about
          && (er.CurrencyId == input.SelectedCurrencyId || er.CurrencyId == input.CurrencyId)
    select new { er.CurrencyId, er.CurrencyRate }
);

// Extract the rates (add null handling if needed for missing data)
var selectedRate = relevantRates.FirstOrDefault(r => r.CurrencyId == input.SelectedCurrencyId)?.CurrencyRate ?? 0;
var baseRate = relevantRates.FirstOrDefault(r => r.CurrencyId == input.CurrencyId)?.CurrencyRate ?? 0;

// Calculate the final rate (always check for division by zero!)
var currencyRate = baseRate != 0 ? selectedRate / baseRate : 0;

Option 2: Let the Database Compute the Result Directly #

If you prefer to have the database handle the division and return just the final value, you can use a JOIN or subqueries to merge the logic into one query:

Using a JOIN (efficient for leveraging shared filter criteria):

var currencyRate = await AsyncExecuter.FirstOrDefaultAsync(
    from selected in await _exchangeRateRepository.GetQueryableAsync()
    // Join on shared firm and date conditions
    join @base in await _exchangeRateRepository.GetQueryableAsync()
        on new { selected.FirmId, input.ProcessDate } 
        equals new { @base.FirmId, input.ProcessDate }
    where selected.CurrencyId == input.SelectedCurrencyId
          && selected.BeginDate <= input.ProcessDate
          && selected.EndDate >= input.ProcessDate
          && @base.CurrencyId == input.CurrencyId
          && @base.BeginDate <= input.ProcessDate
          && @base.EndDate >= input.ProcessDate
    // Compute division directly in the query
    select selected.CurrencyRate / @base.CurrencyRate
);

Or using subqueries for a more readable structure:

var currencyRate = await AsyncExecuter.FirstOrDefaultAsync(
    // Anchor the query with a dummy take(1) to get a single result
    from _ in await _exchangeRateRepository.GetQueryableAsync().Take(1)
    let selectedRate = (
        from er in await _exchangeRateRepository.GetQueryableAsync()
        where er.CurrencyId == input.SelectedCurrencyId
              && er.FirmId == input.FirmId
              && er.BeginDate <= input.ProcessDate
              && er.EndDate >= input.ProcessDate
        select er.CurrencyRate
    ).FirstOrDefault()
    let baseRate = (
        from er in await _exchangeRateRepository.GetQueryableAsync()
        where er.CurrencyId == input.CurrencyId
              && er.FirmId == input.FirmId
              && er.BeginDate <= input.ProcessDate
              && er.EndDate >= input.ProcessDate
        select er.CurrencyRate
    ).FirstOrDefault()
    select baseRate != 0 ? selectedRate / baseRate : 0
);

Key Notes #

• Division by Zero: Don’t skip adding a check for baseRate != 0—neither your original code nor these examples handle this by default, and it’ll cause a runtime exception if the base rate is zero.
• Performance: All these approaches reduce database calls from 2 to 1, which is the main efficiency gain. The JOIN method is often the most database-friendly, as it can use indexes on FirmId, CurrencyId, and date ranges effectively.

内容的提问来源于stack exchange，提问作者orxanmuv