我有一个简单的匿名类型的foreach
循环,我想知道是否有办法使其更有效率。
如果它循环浏览 155 个项目,则需要大约 20 秒才能完成。我省略了它在boAsset
对象上设置的其他一些属性,但没有什么特别的 - 只是Strings
/Integers
。
有什么想法吗?
List<BoAsset> assetList = new List<BoAsset>();
foreach (var asset in result)
{
BoAsset boAsset = new BoAsset();
boAsset.Description = asset.Description;
boAsset.DetailedDescription = asset.DetailedDescription;
boAsset.AssetCustomerID = asset.AssetCustomerID;
boAsset.AssetId = asset.AssetId;
boAsset.Keywords = asset.Keywords;
boAsset.Notes = asset.Notes;
boAsset.Photographer = asset.Photographer;
boAsset.PhotographerEmail = asset.PhotographerEmail;
boAsset.Notes = asset.Notes;
boAsset.Author = asset.Author;
boAsset.FileName = asset.FileName;
boAsset.FileExtension = asset.FileExtension;
boAsset.AssetCreateDate = asset.AssetCreateDate;
boAsset.AssetExpireDate = asset.AssetExpireDate;
assetList.Add(boAsset);
}
var query = (from a in context.Assets
join subAf1 in context.AssetFiles on new { aid = a.AssetID, ftid = 1 } equals new { aid = subAf1.AssetID, ftid = subAf1.AssetTypeID } into theAf1
from Af1 in theAf1.DefaultIfEmpty()
join subAf2 in context.AssetFiles on new { aid = a.AssetID, ftid = 2 } equals new { aid = subAf2.AssetID, ftid = subAf2.AssetTypeID } into theAf2
from Af2 in theAf2.DefaultIfEmpty()
join subAf3 in context.AssetFiles on new { aid = a.AssetID, ftid = 3 } equals new { aid = subAf3.AssetID, ftid = subAf3.AssetTypeID } into theAf3
from Af3 in theAf3.DefaultIfEmpty()
join subAf4 in context.AssetFiles on new { aid = a.AssetID, ftid = 4 } equals new { aid = subAf4.AssetID, ftid = subAf4.AssetTypeID } into theAf4
from Af4 in theAf4.DefaultIfEmpty()
join subAf5 in context.AssetFiles on new { aid = a.AssetID, ftid = 5 } equals new { aid = subAf5.AssetID, ftid = subAf5.AssetTypeID } into theAf5
from Af5 in theAf5.DefaultIfEmpty()
join subFp in context.FilePaths on new { fpid = Af1.FilePathID } equals new { fpid = subFp.FilePathID } into theFp1
from fp1 in theFp1.DefaultIfEmpty()
//join fp in context.FilePaths on Af1.FilePathID equals fp.FilePathID
where a.AssetCustomerID == custId && a.AssetID == assetId
select new { a, Af1, Af2, Af3, Af4, Af5, fp1 }).Distinct();
var result = from q in query
select new
{
AssetId = q.a.AssetID,
AssetCustomerId = q.a.AssetCustomerID,
StockImage = q.a.StockImage,
Description = q.a.Description,
DetailedDescription = q.a.DetailedDescription,
Author = q.a.Author,
FileName = q.Af1.FileName, //was 1
FileExtension = q.Af1.FileExtension, //was 1
AssetCreateDate = q.a.AssetCreateDate,
AssetExpireDate = q.a.AssetExpireDate,
AssetActivateDate = q.a.AssetActivateDate,
Notes = q.a.Notes,
Keywords = q.a.Keywords,
Photographer = q.a.Photographer,
PhotographerEmail = q.a.PhotographerEmail
}
创建 155 个对象并将数据复制到其中的时间大约是一毫秒。您无法对该代码执行任何操作来进行任何重大(甚至显着)改进。
如果要节省任何时间,则应查看创建结果的查询。这就是需要时间的。
您的查询太复杂了(连接太多),特别是考虑到有一些甚至没有使用。
请注意,如果查询很复杂,某些 LINQ 提供程序(如 LINQ to Entities)在简单地处理查询树以将其转换为 SQL 时可能需要相当长的时间。(我已经有一些查询需要 10 或 15 秒才能让 EF 对其进行分析。
如果,正如我怀疑的那样,您正在使用的 LINQ 实现正在访问数据库,请使用 SQL 分析工具来检查传递给服务器的实际 SQL 以及该 SQL 的性能如何。
另外,请注意,给定您使用的字段,您实际上可以通过将结果直接具体化到BoAsset
对象中来取消foreach
循环。
那是:
var result = from q in query
select new BoAsset
{
AssetId = q.a.AssetID,
AssetCustomerId = q.a.AssetCustomerID,
StockImage = q.a.StockImage,
Description = q.a.Description,
DetailedDescription = q.a.DetailedDescription,
Author = q.a.Author,
FileName = q.Af1.FileName, //was 1
FileExtension = q.Af1.FileExtension, //was 1
AssetCreateDate = q.a.AssetCreateDate,
AssetExpireDate = q.a.AssetExpireDate,
AssetActivateDate = q.a.AssetActivateDate,
Notes = q.a.Notes,
Keywords = q.a.Keywords,
Photographer = q.a.Photographer,
PhotographerEmail = q.a.PhotographerEmail
};
List<BoAsset> assetList = result.ToList();