优化 ASP.NET MVC Telerik 网格的 EF Linq to Entities 查询

Question

我希望我能得到一些帮助来优化以下数据检索。这是用例。我想在 Telerik ASP.NET MVC 网格中显示翻译者（供应商）列表。这些翻译器按语言对提供费率（或定价方案）。该数据库中的译者数量不到 400 名。我想一开始就显示所有这些内容，但让用户按他们翻译的语言进行过滤。有供应商（翻译者）表、语言对表（源语言和目标语言为 FK）和语言表。

这是我所拥有的，但速度很慢。主要原因是对于每个供应商，我需要获取他们翻译的每种独特语言（源语言和目标语言）。如果没有 ForEach，我不知道如何做到这一点。我什至不确定如何在没有 while、临时表或游标的情况下在 SQL 中执行此操作。

public List<tblSupplier> GetApprovedSuppliers()
{
    var query = from s in db.tblSuppliers
                join c in db.tblCountryLists on s.SupplierCountry equals c.CountryID into g1
                from c in g1.DefaultIfEmpty()
                select new
                {
                    SupplierID = s.SupplierID,
                    SupplierName = s.CompanyName == null ? s.SupplierFirstName + " " + s.SupplierLastName : s.CompanyName,
                    SupplierEmails = s.SupplierEmails,
                    SupplierType = s.SupplierType,
                    Country = c.Countryname
                };

    List<tblSupplier> list = query.ToList().ConvertAll(s => new tblSupplier
    {
        SupplierID = s.SupplierID,
        SupplierName = s.SupplierName,
        SupplierEmails = s.SupplierEmails,
        SupplierType = s.SupplierType,
        Country = s.Country
    }).OrderBy(s => s.SupplierName).ToList();

    list.ForEach(s => s.Languages = this.GetLanguages(s.SupplierID));

    return list;
}

public string GetLanguages(int supplierID)
{
    var query = (from ps in db.tblSupplierPricingSchemes
                    join lp in db.tblLangPairs on ps.PSLangPairID equals lp.ProductID
                    join sl in db.tblLanguages on lp.SourceLanguageID equals sl.LanguageID
                    where ps.SupplierID == supplierID
                    select sl.LanguageDesc)
                .Union
                (from ps in db.tblSupplierPricingSchemes
                    join lp in db.tblLangPairs on ps.PSLangPairID equals lp.ProductID
                    join tl in db.tblLanguages on lp.TargetLanguageID equals tl.LanguageID
                    where ps.SupplierID == supplierID
                    select tl.LanguageDesc);        

    return string.Join(", ", query);
}

任何帮助表示赞赏。

谢谢， 史蒂夫

Answer 1

您必须减少查询数量。如果您已正确定义模型，则可以从使用 Include 开始。如果您没有正确定义具有导航属性的模型，您可以将 GetLanguages 的多个调用替换为单个调用并在应用程序中重建数据集。要替换 GetLanguages 的多个调用，请将消息签名更改为：

public Dictionary<id, string> GetLanguages(int[] suppplierIds)

并将 ps.SupplierID == sellerID 替换为 sellerIds.Contains(ps.SupplierID)` - 您至少需要 EFv4 才能完成此操作。所以结果应该是这样的：

public Dictionary<id, string> GetLanguages(int[] supplierIds)
{
    var query = (from ps in db.tblSupplierPricingSchemes
                    join lp in db.tblLangPairs on ps.PSLangPairID equals lp.ProductID
                    join sl in db.tblLanguages on lp.SourceLanguageID equals sl.LanguageID
                    where ps.SupplierID == supplierID
                    select new { ps.SupplierID, sl.LanguageDesc })
                .Union
                (from ps in db.tblSupplierPricingSchemes
                    join lp in db.tblLangPairs on ps.PSLangPairID equals lp.ProductID
                    join tl in db.tblLanguages on lp.TargetLanguageID equals tl.LanguageID
                    where ps.SupplierID == supplierID
                    select new { ps.SupplierID, tl.LanguageDesc }); 

    query = from x in query
            group x by x.SupplierID into g
            select g; 

    return query.ToDictionary(x => x.Key, x => String.Join(", ", x));
}

不，你只能使用这个方法，比如：

Dictionary<int, string> languages = GetLanguages(list.Select(s => s.SupplierID));
list.ForEach(s => s.Languages = languages[s.SupplierID]);