LINQ或Navigation Properties命令检索1到多个数据

Question

我正在寻求有关LINQ SQL查询的帮助。

我有一个blazor应用程序，它从Azure SQL数据库获取数据。我正在寻求从数据库中获得一个数据集，用于链接到数据网格，其中每一行都是主表中的一条记录，与第二个表中的记录连接。第二个表有数百万条记录，它需要连接一个具有相同密钥(securityId)且日期为指定日期的记录，或者指定日期之前的最后一个日期的记录。

由于第二个文件的大小，我需要一个有效的查询。目前我正在使用以下方法，但我相信必须有更有效的方法来做到这一点而不滞后。也尝试了导航属性，但无法使用

reviewdateS是我希望第二条记录匹配的日期，或者是该日期之前的最新日期

result = (from cmpn in _ctx.MstarCompanies
join prcs in _ctx.MstarPrices
on cmpn.securityId equals prcs.securityId into cs
from c in cs.DefaultIfEmpty()
where c.date01 == reviewDateS
select new ClsMarketPrices { })

以下是3个相关类别。ClsMarketPrices与数据库表无关，它是一个简单的类，它结合了其他两个类，这可能不是必要的，但据我所知，这就是它的工作原理。

_ctx是一个链接到数据上下文的存储库。

public MySQLRepositories(ApplicationDbContext ctx)
{
_ctx = ctx;
}
public class ClsMarket
{
[Key]
public int CompanyId { get; set; } = 0;
public string securityId { get; set; } = "";
public string companyName { get; set; } = "";
public string mic { get; set; } = "";
public string currency { get; set; } = "";
[ForeignKey("securityId")]
public virtual ICollection<ClsPrices> Prices { get; set; }    
}
public class ClsMarketPrices
{
[Key]
public int CompanyId { get; set; } = 0;    
public string companyName { get; set; } = "";
public string period { get; set; } = "";
public string mic { get; set; } = "";   
}
public class ClsPrices
{        
[Key]
public int PricesId { get; set; }
[ForeignKey("securityId")]
public string securityId { get; set; } = "";
public string mic { get; set; } = "";
public string date01 { get; set; } = "";


public virtual ClsMarket ClsMarket {get; set;}
}

Answer 1

我想从第一个文件中获取一条记录，并将其与第二个文件中的一条记录连接起来，其中第二个档案中的记录的日期等于或是指定日期之前的最后一条。

所以我们谈论的是文件，而不是数据库
这很重要，因为这意味着您的本地进程将执行LINQ，而不是数据库管理系统。换句话说：LINQ将是IEnumerable，而不是IQueryable。

这一点很重要，因为作为Enumerable，您将能够定义自己的LINQ扩展方法。

尽管你提供了大量不相关的属性，但你忘了给我们最重要的东西：你说的是两个文件，你告诉我们你有两个具有一对多关系的类，但你给了我们三个类。哪些人确实与你所说的有关系？

我认为ClsMarketPrices的每个对象都有零个或多个ClsPrices，并且每个ClsPrice都是ClsMarketPrices的价格之一，即外键SecurityId(相当混乱的名称)所指的ClsMarketPrices

首先，让我们假设您已经有了从文件中读取这两个序列的过程。当然，这些过程不会读取超出需要的内容(因此，如果只使用第一个ClsMarket，请不要读取整个文件)。我想你已经知道怎么做了：

IEnumerable<ClsMarketPrices> ReadMarketPrices();
IEnumerable<ClsPrices> ReadPrices();

所以你已经去了DateTime reviewDate。每个CCD_ 9具有零个或多个CCD_。每个Price都有一个DateTime属性DateStamp。您希望每个MarketPrice的Price具有DateStamp的最大值，该值小于或等于reviewDate。

如果一个MarketPrice没有这样的Prices，例如，因为它根本没有Price，或者它的所有Prices都有一个大于reviewDate的DateStamp，则需要一个null值。

如果MarketPrice有多个具有相同最大DateStamp的价格<=review日期。我想你不在乎选哪一个。

Straighborward LINQ方法将使用GroupJoin、Where、Orderby和FirstOrDefault:

DateTime reviewDate = ...
IEnumerable<ClsMarketPrices> marketPricess = ReadMarketPrices();
IEnumerable<ClsPrices> prices = ReadPrices().Where(price => price.DateStamp <= reviewDate);
// GroupJoin marketPrices with prices:
var result = markets.GroupJoin(prices,
marketPrice => marketPrice.CompanyId,  // from every MarketPrice take the primary key
price => price.CompanyId,          // from every price take the foreign key to its market
// parameter resultSelector: from every market, with its zero or more matching prices
// make one new:
(marketPrice, pricesOfThisMarketPrice) => new
{
// select the marketPrice properties that you plan to use:
Id = marketPrice.CompanyId,
Name = ...
...
// from all prices of this marketPrice, take the one with the largest DateStamp
// we know there are no marketPrices with a DataStamp larger than reviewData
LatestPrice = pricesOfThisMarketPrice.OrderbyDescending(price => price.DateStamp)
.Select(price => new
{
// Select the price properties you plan to use;
Id = price.PricesId,
Date = price.DateStamp,
...
})
.FirstOrDefault(),
});

问题是：这必须有效地完成，因为你有大量的Markets和MarketPrices。

尽管我们已经通过删除reviewDate之后的价格来限制要排序的价格数量，但如果您只使用第一个日期，那么订购所有日期仍然是浪费。

我们可以通过使用pricesOfThisMarketPrice的Aggregate对此进行优化。这将断言pricesOfThisMarketPrice将仅被枚举一次。

附带说明：Aggregate只适用于IEnumerable，而不适用于IQueryable，因此它不适用于数据库。此外，pricesOfThisMarketPrice可能是一个空序列；我们必须处理好这件事。

LatestPrice = pricesOfThisMarketPrice.Any() ?
pricesOfThisMarketPrice.Aggregate(
// select the one with the largest value of DateStamp:
(latestPrice, nextPrice) => nextPrice.DateStamp >= latesPrice.DateStamp) ? nextPrice : latestPrice)
// do not do the aggregate if there are no prices at all:
: null,

尽管此Aggregate比OrderBy更高效，但您的第二个序列仍将被枚举多次。请参阅Enumerable的源代码。GroupJoin。

如果您真的想枚举第二个源一次，并限制第一个源的枚举次数，请考虑创建一个扩展方法。通过这种方式，您可以将其用作任何LINQ方法。如果您不熟悉扩展方法，请参阅扩展方法解密。

你可以为你的ClsPrices和ClsPrice创建一个扩展方法，然而，如果你认为你需要"；找到属于另一个元素的最大元素"；更常见的情况是，为什么不像LINQ那样创建一个通用方法呢。

下面我创建了最广泛的扩展方法，一个带有resultSelector和equityComparers的方法。如果要使用标准相等，请考虑添加一个没有这些比较器的扩展方法，并让此扩展方法为比较器调用另一个具有null值的扩展方法。

有关带有和不带有相等比较器的重载的示例，请参阅几个LINQ方法，如ToDictionary：有一个方法没有比较器，也有一个有比较器。第一个调用具有空值的第二个比较器。

我会用小步，这样你就能了解发生了什么。这可以稍微优化一下。最重要的是，你只会列举一次你最大的收藏。

IEnumerable<TResult> TakeLargestItem<T1, T2, TKey, Tproperty, TResult>(
this IEnumerable<T1> t1Sequence,
IEnumerable<T2> t2Sequence,
// Select primary and foreign key:
Func<T1, TKey> t1KeySelector,
Func<T2, TKey> t2KeySelector,
// Select the property of T2 of which you want the largest element
Func<T2, TProperty> propertySelector,
// The largest element must be <= propertyLimit:
TProperty propertyLimit,
// From T1 and the largest T2 create one TResult
Func<T1, T2, TResult> resultSelector,
// equality comparer to compare equality of primary and foreign key
IEqualityComparer<TKey> keyComparer,
// comparer to find the largest property value
IComparer<TProperty> propertyComparer)
{
// TODO: invent a property method name
// TODO: decide what to do if null input
// if no comparers provided, use the default comparers:
if (keyComparer == null) keyComparer = EqualityComparer<TKey>.Default;
if (propertyComparer == null) propertyComparer = Comparer<TProperty>.Default;
// TODO: implement
}

实现很简单：

• 将字典中的所有T1 t1Key作为关键字，{T1，T2}作为值，keyComparer作为比较器

• 则仅枚举T2一次。

  • 检查属性<=propertyLimit
  • 如果是，请在字典中搜索具有相同关键字的{T1，T2}组合
  • 检查当前t2Item是否大于{T1，T2}组合中的T2
  • 如果是：更换

  我们需要一个内部类：

  类DictionaryValue{公共T1 T1｛get；set；｝公共T2 T2｛get；set；｝}

代码：

IDictionary<TKey, DictionaryValue> t1Dict = t1Sequence.ToDictionary(
t1 -> t1KeySelector(t1),
t1 => new DictionaryValue {T1 = t1, T2 = (T2)null },
keyComparer);

t2Sequence:的枚举

foreach (T2 t2 in t2Sequence)
{
// check if the property is <= propertyLimit
TProperty property = propertySelector(t2);
if (propertyComparer.Compare(property, propertyLimit) < 0)
{
// find the T1 that belongs to this T2:
TKey key = keySelector(t2);
if (t1Dict.TryGetValue(key, out DictionaryValue largestValue))
{
// there is a DictionaryValue with the same key
// is it null? then t2 is the largest
// if not null: get the property of the largest value and use the
// propertyComparer to see which one of them is the largest
if (largestValue.T2 == null)
{
largestValue.T2 = t2;
}
else
{
TProperty largestProperty = propertySelector(largestValue.T2);
if (propertyComparer.Compare(property, largestProperty) > 0)
{
// t2 has a larger property than the largestValue: replace
largestValue.T2 = t2,
}
}
}
}
}

因此，对于每个t1，我们发现最大的t2具有性质<=propertyLimit。使用resultSelector创建结果。

IEnumerable<TResult> result = t1Dict.Values.Select(
t1WithLargestT2 => resultSelector(t1WithLargestT2.T1, t1WithLargestT2.T2));
return result;