如何将外部列表集成到原始SQL查询中作为LINQ原始SQL查询中的表联接

admin

如何将外部列表集成到原始SQL查询中作为LINQ原始SQL查询中的表联接

sql

我在我的项目中有以下查询，每页运行10-20次。我试过使用linq到sql，linq到实体运行此查询，但这要比它们快得多。

问题是，如果我可以使用join语句将外部列表（sContentIds）传递到查询中，那么它会比使用SQL
IN语句使查询更快吗？如果是这样，我怎么能做到这一点。sContentIds.Count多数情况下可能在1到40之间变化。

List<filterContentsPCDTO> cContents = unitOfWork.ExecuteQuery<filterContentsPCDTO>(@"SELECT c.ContentId, c.ContentPageId, c.CreatedById, p.PCA, p.PCC, p.PCD, c.AlbumId, a.AlbumTypeId 
                                FROM Contents c
                                INNER JOIN Privatizations p ON c.ContentId = p.ContentId
                                LEFT JOIN Albums a ON c.AlbumId = a.AlbumId
                                WHERE c.ContentId IN (" + string.Join(",", sContentIds) + ")").ToList();

我们正在研究ASP.NET MVC4框架，并使用工作单元模式进行数据库交互。通常我已经建立了如下查询，但是它比原始sql查询慢5倍。

var cContents = unitOfWork.ContentRepository
                .GetFiltered(x => contentIds.Contains(x.ContentId)).Select(x => new filterContentsPCDTO()
                {
                    ContentId = x.ContentId,
                    ContentPageId = x.ContentPageId,
                    CreatedById = x.CreatedById,
                    PCA = x.Privatization.PCA,
                    PCC = x.Privatization.PCC,
                    PCD = x.Privatization.PCD,
                    PrivatizationModifiedById = x.Privatization.ModifiedById,
                    AlbumId = x.AlbumId,
                    albumTypeId = x.AlbumId == null ? -1 : x.Album.AlbumTypeId
                }).ToList();

GetFiltered方法的实现

public IEnumerable<T> GetFiltered(
            Expression<Func<T, bool>> filter = null,
            Func<IQueryable<T>, IOrderedQueryable<T>> orderBy = null,
            string includeProperties = "")
        {
            IQueryable<T> query = _dbSet;

            if (filter != null)
            {
                query = query.Where(filter);
            }

            foreach (var includeProperty in includeProperties.Split
                (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
            {
                query = query.Include(includeProperty);
            }

            if (orderBy != null)
            {
                return orderBy(query);
            }
            else
            {
                return query;
            }
        }

阅读 117

2021-06-07

共1个答案

admin

如果您使用的是SQL Server 2008（或更高版本），并且提高性能是此处的主要目标（并且在这种情况下，您可能愿意放弃LINQ to
SQL），建议您将此查询编写为一个存储过程，以用户定义的表类型作为参数。这将使您可以将整个sContentIds集合传递到数据库，并且仍然比临时查询还受益于存储过程的优势。

首先，将表类型定义为：

CREATE TYPE [dbo].[ContentList] AS TABLE(
    [ContentId] [int]
)

然后像下面这样创建过程：

CREATE PROCEDURE [dbo].[usp_GetContents]
    @contentIds ContentList READONLY
AS

SELECT c.ContentId
       ,c.ContentPageId
       ,c.CreatedById
       ,p.PCA
       ,p.PCC
       ,p.PCD
       ,c.AlbumId
       , a.AlbumTypeId 
FROM Contents c
    INNER JOIN Privatizations p
        ON c.ContentId = p.ContentId
    LEFT JOIN Albums a
        ON c.AlbumId = a.AlbumId
WHERE c.ContentId IN (SELECT ContentId FROM @contentIds)

然后，您应该能够使用此答案中描述的技术从C＃调用它（基本上是DataTable从列表中创建一个，然后像常规参数一样添加它）。不幸的是，使用LINQ
to
SQL似乎很难做到这一点，但是，正如我所说，如果提高性能是主要目标，那么这可能是一个选择。

2021-06-07