(EF 4.0与普通SQL相比)缩短了Linq的执行时间 [英] (EF 4.0 vs a normal SQL) Improve execution time in Linq

问题描述

当我执行此SQL语句时，绝对不需要时间来运行:

When I execute this SQL statement, it takes absolutely no time to run:

select * from [user]
left join licence on [user].userID = licence.UserID
left join licenceProducts on licence.licenceID = licenceProducts.licenceID
left join products on products.productID = licenceProducts.productID
left join contacts on contacts.UserID = [user].userID
left join usersupportedproducts on usersupportedproducts.UserID = [user].userID
left join [user] b on b.userID = [user].ParentUserID
where [user].Type <> 6
order by [user].name

但是，当我使用EF 4.0运行相同的查询时，执行该查询几乎需要20秒钟.

However, when I run the same query using EF 4.0 it takes almost 20 sec to execute.

我试过有没有办法改善这个Linq请求?

Is there a way to improve this Linq request, I tried ?

users = null;
using (var db = new DistributorEntities())
{
    try
    {
        users = db.Users
            .Include(u => u.Licences)
            .Include(u => u.Licences.Select(l => l.LicenceProducts.Select(lp => lp.Product)))
            .Include(u => u.UserAddress)
            .Include(u => u.Contact)
            .Include(u => u.User2)
            .Include(u => u.SupportProducts)
            .Where(u => u.Type != (int)UserType.Admin)
            .OrderBy(u => u.Name)
            .ToList();
    }
    catch (Exception ex)
    {
        if (ex is InvalidOperationException)
        {
            _EventLog.WriteEntry(ex.Message + "  WebService.GetAllUsersAndChildren");
        }
        exception = new ServiceError(ex);
    }
}

我试图通过放置SQl语句来执行ExecuteStoreQuery()，但是它没有加入users的关系.

I tried to execute ExecuteStoreQuery() by putting my SQl statement, but it does not join the relations of users.

推荐答案

直接在SQL中编写查询通常比在LINQ中编写表达式要高效得多，后者必须将其转换为次优" SQL.

Writing queries directly in SQL is almost always way more efficient than writing expressions in LINQ which must then be translated into "sub-optimal" SQL.

您可以做的就是简单地创建一个包含查询的VIEW，然后将该VIEW映射到Entity Framework中的Entity，然后查询该Entity-这将只执行VIEW在SQL Server中.

What you could do is simply create a VIEW that contains your query, then map that VIEW to an Entity in Entity Framework, then query that Entity -- which will just execute the VIEW in SQL Server.

另请参见 http://www.mssqltips.com/sqlservertip/1990/how-to-use-sql-server-views-with-the-entity-framework/.

这篇关于(EF 4.0与普通SQL相比)缩短了Linq的执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！