加速具有40,000行的linq查询 - c#

在我的服务中，首先，我会生成40,000个本国和所在国家/地区的组合，例如（clientLocations包含200条记录，因此200 x 200为40,000）：

foreach (var homeLocation in clientLocations)
{
    foreach (var hostLocation in clientLocations)
    {
        allLocationCombinations.Add(new AirShipmentRate
        {
            HomeCountryId = homeLocation.CountryId,
            HomeCountry = homeLocation.CountryName,
            HostCountryId = hostLocation.CountryId,
            HostCountry = hostLocation.CountryName,
            HomeLocationId = homeLocation.LocationId,
            HomeLocation = homeLocation.LocationName,
            HostLocationId = hostLocation.LocationId,
            HostLocation = hostLocation.LocationName,
        });
    }
}

然后，我运行以下查询以查找上述位置的现有费率，但还包括清空丢失的费率；产生了40,000行的完整记录集。

var allLocationRates = (from l in allLocationCombinations
                        join r in Db.PaymentRates_AirShipment
                            on new { home = l.HomeLocationId, host = l.HostLocationId }
                            equals new { home = r.HomeLocationId, host = (Guid?)r.HostLocationId }
                        into matches
                        from rate in matches.DefaultIfEmpty(new PaymentRates_AirShipment
                        {
                            Id = Guid.NewGuid()
                        })
                        select new AirShipmentRate
                        {
                            Id = rate.Id,
                            HomeCountry = l.HomeCountry,
                            HomeCountryId = l.HomeCountryId,
                            HomeLocation = l.HomeLocation,
                            HomeLocationId = l.HomeLocationId,
                            HostCountry = l.HostCountry,
                            HostCountryId = l.HostCountryId,
                            HostLocation = l.HostLocation,
                            HostLocationId = l.HostLocationId,
                            AssigneeAirShipmentPlusInsurance = rate.AssigneeAirShipmentPlusInsurance,
                            DependentAirShipmentPlusInsurance = rate.DependentAirShipmentPlusInsurance,
                            SmallContainerPlusInsurance = rate.SmallContainerPlusInsurance,
                            LargeContainerPlusInsurance = rate.LargeContainerPlusInsurance,
                            CurrencyId = rate.RateCurrencyId
                        });

我尝试使用.AsEnumerable()和.AsNoTracking()，这加快了很多事情。以下代码将查询时间缩短了几秒钟：

var allLocationRates = (from l in allLocationCombinations.AsEnumerable()
                        join r in Db.PaymentRates_AirShipment.AsNoTracking()

但是，我想知道：如何才能进一步提高速度？

编辑：无法复制linq中的foreach功能。

allLocationCombinations = (from homeLocation in clientLocations
                            from hostLocation in clientLocations
                            select new AirShipmentRate
                            {
                                HomeCountryId = homeLocation.CountryId,
                                HomeCountry = homeLocation.CountryName,
                                HostCountryId = hostLocation.CountryId,
                                HostCountry = hostLocation.CountryName,
                                HomeLocationId = homeLocation.LocationId,
                                HomeLocation = homeLocation.LocationName,
                                HostLocationId = hostLocation.LocationId,
                                HostLocation = hostLocation.LocationName
                            });

我在from hostLocation in clientLocations上收到错误消息，提示“无法将IEnumerable类型转换为Generic.List。”

参考方案

我如何才能进一步加快速度？

优化是一个bit子。

您的代码对我来说看起来不错。确保在适当的数据库模式上设置索引。就像已经提到的那样：针对SQL运行Linq可以更好地了解性能。

好吧，但是无论如何如何提高性能呢？

您可能希望浏览以下链接：
10 tips to improve LINQ to SQL Performance

对我来说，可能列出了最重要的几点（在上面的链接中）：

只检索您需要的记录数
如果不关闭数据上下文的ObjectTrackingEnabled属性
必要
使用DataLoadOptions.AssociateWith将数据过滤到所需的内容
在需要时使用编译查询（请谨慎使用该查询...）

Java中的OrderByDecending(LINQ)等效项 - java

嗨，我是一名使用Java的C＃开发人员。问题很简单：我如何才能将下面的c＃代码写入Java并仍能正常工作：myCoffeeList.OrderByDescending(x => x.Name?.ToLower()?.Trim() == sender.Text.ToLower()?.Trim())); 我的sender.Text基本上是一个文本框。文本的…

LINQ RemoveAll代替循环 - c#

我有两个for循环，用于从列表中删除项目。我正在为这些循环寻找等效的LINQ语句for (Int32 i = points.Count - 1; i >= 0; i--) { for (Int32 j = touchingRects.Count - 1; j >= 0; j--) { if (touchingRects[j].HitTest(po…

Linq Any()的哪种使用效率更高？ - c#

我有一个Linq查询，如下所示：return this._alarmObjectAlarmViolationList .Where(row => row.ObjectId == subId) .Where(row => row.AlarmInternalId == "WECO #1 (StdDev > UCL)") .W…

LINQ to SQL中的“无法识别的表达式节点数组索引linq”异常 - c#

这是我的LINQ，其中我将DateTime类型的字段与当前日期进行了比较- var srs = (from s in dcDistrict.ScheduledReportStatus where s.ReportConfigId.Equals(ConfigId) && s.Status.HasValue && s.Status…

LINQ to XML语法 - c#

我有一个简单的POCO类，用于保存从XML文件提取的数据，该XML文件定义如下：public class Demographics { public string FirstName { get; set; } public string LastName { get; set; } public string MiddleName { get; set; …