如何将外部列表集成到原始SQL查询中作为LINQ原始SQL查询中的表联接 [英] How to integrate external list to raw sql query join as table in a linq raw sql query

问题描述

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

i have the following query running 10-20 times / page in my project. i have tried to run this query with linq to sql, linq to entities but this is far more faster then them.

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

The question is if i could pass in the external list(sContentIds) into query with a join statement, would it make the query faster then using SQL IN statement? If so how can i achieve this. sContentIds.Count may vary from 1-40 most of the times.

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倍.

We are working on ASP.NET MVC4 framework and using unit of work pattern for database interactions. Normally i had built this query like follows but it was 5 times slower then raw sql query.

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方法的实现

Implementation of GetFiltered Method

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;
            }
        }

推荐答案

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

If you're using SQL Server 2008 (or newer) and increasing performance is the main objective here (and you're maybe willing to abandon LINQ to SQL for this scenario), I would recommend writing this query as a stored procedure that takes a user-defined table type as a parameter. This will allow you to pass your entire sContentIds collection to the database and still benefit from advantages of a stored procedures over an ad-hoc query.

首先，将表类型定义为:

First, define the table type as something like:

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

然后按照以下方式创建过程:

Then create the procedure as something like:

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来实现，但是，正如我所说，提高性能是主要的目标，这可能是一个选择.

Then you should be able to call it from C# using the technique described in this answer (basically, create a DataTable from your list then add it like a regular parameter). Unfortunately it looks like this is tough to do with LINQ to SQL, but, as I said, if increasing performance is the main goal, this could be an option.

这篇关于如何将外部列表集成到原始SQL查询中作为LINQ原始SQL查询中的表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！