如何将外部列表集成到RAW SQL查询加入linq RAW SQL查询中的表格[英] How to integrate external list to raw sql query join as table in a linq raw sql query

本文是小编为大家收集整理的关于如何将外部列表集成到RAW SQL查询加入linq RAW SQL查询中的表格的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我的项目中有以下查询运行10-20次/页面.我试图将此查询运行使用Linq到SQL,Linq到实体,但比它们要快得多.

问题是,我是否可以使用JOIN语句中的外部列表(Scontentids)将其传递到查询中,是否可以使查询更快,然后在语句中使用SQL?如果是这样,我该如何实现. scontentids.count在大多数时间的大部分时间里可能有所不同.

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框架,并使用工作模式单位进行数据库交互.通常,我像以下那样构建了此查询,但速度慢了5倍,而RAW SQL查询.

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

推荐答案

如果您使用的是SQL Server 2008(或较新),并且性能不断提高是这里的主要目标(并且您愿意在此情况下将Linq放在SQL上),我建议将此查询写为一个存储过程,该过程采用用户定义的表类型作为参数.这将允许您将整个sContentIds集合传递到数据库,并且仍然从中存储的过程优势超过Ad-Hoc查询, .

首先,将表类型定义为:

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到SQL ,但是,正如我所说的,如果提高性能是主要的,则是主要的性能.目标,这可能是一个选择.

本文地址:https://www.itbaoku.cn/post/1557062.html

问题描述

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.

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();

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();

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

推荐答案

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)

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.