慢sql查询C#

Question

我有一个sql查询，需要很长时间才能运行。我知道问题在哪里，但我不知道如何解决。

这里我的代码：

var result = context.Persons.OrderByDescending(x => x.PersonId);
var response = result.Where(x => x.IsProvider.Equals(false) && x.Obsolete.Equals(false) && x.Locked.Equals(false) && x.IsCustomer.Equals(true));

var list = new List<ICustomerHead>();

foreach (var customer in response)
{
    if (!customer.PersonType.Equals(2))
    {
        list.Add(customer.ToSingleCustomerHead());
    }
}

foreach (var customer in list) //This loop is the problem, because it will make a new db connection for every customer
{
    var companyResult = context.Companies.SingleOrDefault(x => x.CompanyId.Equals(customer.Id));

    if (companyResult != null)
        customer.Email = companyResult.StandardEMail;
}

return list;

第二个循环是延迟的位置。这是因为它将为每个客户建立一个新的db连接。那我怎么才能让这个更快呢？

(请帮助:)

Answer 1

简单--如果您有一个ID列表，并且您想要一个select查询来获取该列表中具有ID的所有公司，则需要使用.Contains (在SQL中转换为IN )。

以下是如何：

var idList = list.Select(x => x.Id);
// create a query that just gets the required emails and companyIds.
var emails = context.Companies.Where(x => idList.Contains(x.CompanyId))
                              .Select(x => new {x.CompanyId, x.StandardEmail});
// execute the query (with .ToList() ) 
// and then iterate through the results, setting e-mail address on the list items
emails.ToList().ForEach(
  x => list.First(y => y.Id == x.CompanyId).Email = x.StandardEmail
);

老实说，我可能只需进行一次查询，就可以将您的客户与他们的电子邮件地址联系在一起。

例：

var list = new List<ICustomerHead>();
var query = from p in context.Persons
            join c in context.Companies on p.CompanyId equals c.CompanyId
            where p.IsProvider.Equals(false) 
                  && p.Obsolete.Equals(false) 
                  && p.Locked.Equals(false) 
                  && p.IsCustomer.Equals(true)
                  && !p.PersonType.Equals(2) // not sure why this was separate before?
            select new {p, c.StandardEmail};

foreach(var item in query.ToList()) //ToList() causes the query to execute
{
  var customer = item.p.ToSingleCustomerHead(); //assume this is an extension method you've added?
  customer.Email = item.StandardEMail;
  list.Add(customer);
}

Answer 2

假设公司是一个相关实体，您可以在第一个查询中使用包含公司。

添加此引用以使用Include (使用System.Data.Entity;)

var response = result.Include(x=>x.Companies).Where(x => x.IsProvider.Equals(false) && x.Obsolete.Equals(false) && x.Locked.Equals(false) && x.IsCustomer.Equals(true));

添加

 foreach (var customer in response)
    {
        if (!customer.PersonType.Equals(2))
        {
            var c =customer.ToSingleCustomerHead();

            if (customer.Companies !=null && customer.Companies.FirstOrDefaultOrDefault() != null)
                c.Email = companyResult.StandardEMail;

            list.Add(c);
        }
    }

注意: FirstOrDefaultOrDefault比SingleOrDefault更快地检查不合格值的singleOrDefault。